You are here:

Visual Basic/Problem in executing access query using vb 6 code


Dear Sir,
I'm developing an application for Vehicle Log Book in vb6.0 and MS-access as back end. I'm facing a problem while generating report with data report. In data report I want to calculate automatically total consumed fuel/month, mileage/month and total KM/month from the daily consumed fuel, mileage and KM by the query. I have designed a query in access that executes normally in access but when I execute the query from the VB code it gives an error saying like this:

You tried to execute a query that does not include the specified expression 'Ending_KM-Starting_KM' as part of an aggregate function.
The Query that I have designed is:
"SELECT Choose(MONTH(`TDate`), 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) AS `Month`, tblLogSheet.Driver_Name, MIN(tblLogSheet.Starting_KM) AS Starting_KM, MAX(tblLogSheet.Ending_KM) AS Ending_KM,
   SUM(tblLogSheet.Fuel_Added) AS Fuel_Added,
   Ending_KM - Starting_KM AS Total_KM_Per_Month,
   Total_KM_Per_Month / Fuel_Added AS Mileage_Per_Month,
   (YEAR(`TDate`)) AS `Year`, tblLogSheet.Vehicle_No
FROM tblLogSheet
WHERE (((tblLogSheet.Vehicle_No) = '16 - 0 - 156') AND
   ((YEAR(`tblLogSheet`.`TDate`)) = 2012))
GROUP BY Choose(MONTH(`TDate`), 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12), tblLogSheet.Driver_Name, (YEAR(`TDate`)), tblLogSheet.Vehicle_No"

Please help me
Thanks in advance

you cannot use such sql in vb and data report.

to the command1 text

add this  line after pointing it  to connection
Select * from testable where Item=? And  rate=?
'where item and rate are 2 fields with parameters

drag the command1 on to datareport and point it command1 and dataenvironement

'have a commandbutton and 2 textboxes on the form
add this code:
Private Sub Command1_Click()

With DataEnvironment1
.Command1 Text1, Val(Text2)
End With

If Text1 = "" Or Text2 = "" Then Exit Sub
If DataEnvironment1.rsCommand1.RecordCount > 0 Then
End If
End Sub

this sort of code works

Visual Basic

All Answers

Answers by Expert:

Ask Experts




visual basic application programming from design to access information, sql, engineering and commercial applications. access databases, excel.(VB6) basic questions in vb 2008. Optional: 1)a large number of people want me to do work which takes some time and effort. can do projects in vb 2008. Pl.note that i would like to be paid for such work. 2) if you want me to spend quality time and do special work, i expect to be paid a reasonable price for my time. 3) if you are pleased with my reply you could consider a good amount. 4) you can visit my website


programming since the past 15 years. have completed a large number of projects in Industrial applications, finance,accounts, correspondence. excel macros etc

engineering, management degrees.

Past/Present Clients
project work for a Norway company,USA company and a Canadian company completed. Freelance Project work and Teaching. helping programmers with their work
teaching vb

©2016 All rights reserved.