SQL Query works -- just not in VBA

P

p

Hi,

I have this query:

SELECT DISTINCT tbl_Progress.Progress, tbl_Progress.Date,
tbl_Employee.EmployeeID, tbl_Activity.ActivityDate, tbl_Activity.Units,
tbl_Activity.Rate, tbl_Progress.Job_Number, tbl_Jobs.Job_ID,
tbl_Employee.Name, tbl_Employee.Initials, tbl_Jobs.Job_Duration,
tbl_Jobs.Job_Name, tbl_Jobs.date_Value, SUM([tbl_Activity.Units]) AS
TotalTimeSpent, SUM([tbl_Activity.Units]*[tbl_Activity.Rate]) AS
TotalSpent, [tbl_Jobs.Job_Budget]/[tbl_Jobs.Job_Duration] AS
ProjectedSpent, tbl_Jobs.Job_Budget
FROM (tbl_Employee INNER JOIN (tbl_Jobs INNER JOIN tbl_Activity ON
tbl_Jobs.Job_ID = tbl_Activity.JobID) ON (tbl_Employee.EmployeeID =
tbl_Jobs.Employee_ID) AND (tbl_Employee.EmployeeID =
tbl_Activity.EmployeeID)) INNER JOIN tbl_Progress ON
tbl_Jobs.Job_Number = tbl_Progress.Job_Number
GROUP BY tbl_Progress.Progress, tbl_Progress.Date,
tbl_Employee.EmployeeID, tbl_Activity.ActivityDate, tbl_Activity.Units,
tbl_Activity.Rate, tbl_Progress.Job_Number, tbl_Jobs.Job_ID,
tbl_Employee.Name, tbl_Employee.Initials, tbl_Jobs.Job_Duration,
tbl_Jobs.Job_Name, tbl_Jobs.date_Value, tbl_Jobs.Job_Budget
HAVING (((tbl_Progress.Date) Between Now() And DateAdd("m",-4,Now()))
AND ((tbl_Employee.EmployeeID)=[Forms]![Eva]![EmployeeID]));

However, I need to run multiple calculations which are above and beyond
the ability of SQL only so I am incorporating it into VBA.

This is the VBA code i have at the moment:

ReportSQL = "SELECT DISTINCT tbl_Progress.Progress, tbl_Progress.Date,
tbl_Employee.EmployeeID, tbl_Activity.ActivityDate, tbl_Activity.Units,
tbl_Activity.Rate, tbl_Progress.Job_Number, tbl_Jobs.Job_ID,
tbl_Employee.Name, tbl_Employee.Initials, tbl_Jobs.Job_Duration,
tbl_Jobs.Job_Name, tbl_Jobs.date_Value, SUM([tbl_Activity.Units]) AS
TotalTimeSpent, SUM([tbl_Activity.Units]*[tbl_Activity.Rate]) AS
TotalSpent, [tbl_Jobs.Job_Budget]/[tbl_Jobs.Job_Duration] AS
ProjectedSpent, tbl_Jobs.Job_Budget " & _
" FROM (tbl_Employee INNER JOIN (tbl_Jobs INNER JOIN
tbl_Activity ON tbl_Jobs.Job_ID = tbl_Activity.JobID) ON
(tbl_Employee.EmployeeID = tbl_Jobs.Employee_ID) AND
(tbl_Employee.EmployeeID = tbl_Activity.EmployeeID)) INNER JOIN
tbl_Progress ON tbl_Jobs.Job_Number = tbl_Progress.Job_Number " & _
" GROUP BY tbl_Progress.Progress, tbl_Progress.Date,
tbl_Employee.EmployeeID, tbl_Activity.ActivityDate, tbl_Activity.Units,
tbl_Activity.Rate, tbl_Progress.Job_Number, tbl_Jobs.Job_ID,
tbl_Employee.Name, tbl_Employee.Initials, tbl_Jobs.Job_Duration,
tbl_Jobs.Job_Name, tbl_Jobs.date_Value, tbl_Jobs.Job_Budget " & _
" HAVING (((tbl_Progress.Date) Between Now() And
DateAdd("m",-4,Now())) AND
((tbl_Employee.EmployeeID)=[Forms]![Eva]![EmployeeID]))"

but it says there are two few parameters expected. I know because its
DAO that I have to change the values for the form reference (and maybe
the date) but after hours of fiddling around I cannot get it to work.
Could someone changeout the above VBA code with an sql string that
would work?

thanks alot.
 
R

Rick Brandt

p said:
Hi,

I have this query:

SELECT DISTINCT tbl_Progress.Progress, tbl_Progress.Date,
tbl_Employee.EmployeeID, tbl_Activity.ActivityDate,
tbl_Activity.Units, tbl_Activity.Rate, tbl_Progress.Job_Number,
tbl_Jobs.Job_ID, tbl_Employee.Name, tbl_Employee.Initials,
tbl_Jobs.Job_Duration, tbl_Jobs.Job_Name, tbl_Jobs.date_Value,
SUM([tbl_Activity.Units]) AS TotalTimeSpent,
SUM([tbl_Activity.Units]*[tbl_Activity.Rate]) AS TotalSpent,
[tbl_Jobs.Job_Budget]/[tbl_Jobs.Job_Duration] AS ProjectedSpent,
tbl_Jobs.Job_Budget
FROM (tbl_Employee INNER JOIN (tbl_Jobs INNER JOIN tbl_Activity ON
tbl_Jobs.Job_ID = tbl_Activity.JobID) ON (tbl_Employee.EmployeeID =
tbl_Jobs.Employee_ID) AND (tbl_Employee.EmployeeID =
tbl_Activity.EmployeeID)) INNER JOIN tbl_Progress ON
tbl_Jobs.Job_Number = tbl_Progress.Job_Number
GROUP BY tbl_Progress.Progress, tbl_Progress.Date,
tbl_Employee.EmployeeID, tbl_Activity.ActivityDate,
tbl_Activity.Units, tbl_Activity.Rate, tbl_Progress.Job_Number,
tbl_Jobs.Job_ID, tbl_Employee.Name, tbl_Employee.Initials,
tbl_Jobs.Job_Duration, tbl_Jobs.Job_Name, tbl_Jobs.date_Value,
tbl_Jobs.Job_Budget
HAVING (((tbl_Progress.Date) Between Now() And DateAdd("m",-4,Now()))
AND ((tbl_Employee.EmployeeID)=[Forms]![Eva]![EmployeeID]));

However, I need to run multiple calculations which are above and
beyond the ability of SQL only so I am incorporating it into VBA.

This is the VBA code i have at the moment:

ReportSQL = "SELECT DISTINCT tbl_Progress.Progress, tbl_Progress.Date,
tbl_Employee.EmployeeID, tbl_Activity.ActivityDate,
tbl_Activity.Units, tbl_Activity.Rate, tbl_Progress.Job_Number,
tbl_Jobs.Job_ID, tbl_Employee.Name, tbl_Employee.Initials,
tbl_Jobs.Job_Duration, tbl_Jobs.Job_Name, tbl_Jobs.date_Value,
SUM([tbl_Activity.Units]) AS TotalTimeSpent,
SUM([tbl_Activity.Units]*[tbl_Activity.Rate]) AS TotalSpent,
[tbl_Jobs.Job_Budget]/[tbl_Jobs.Job_Duration] AS ProjectedSpent,
tbl_Jobs.Job_Budget " & _ " FROM (tbl_Employee INNER JOIN
(tbl_Jobs INNER JOIN
tbl_Activity ON tbl_Jobs.Job_ID = tbl_Activity.JobID) ON
(tbl_Employee.EmployeeID = tbl_Jobs.Employee_ID) AND
(tbl_Employee.EmployeeID = tbl_Activity.EmployeeID)) INNER JOIN
tbl_Progress ON tbl_Jobs.Job_Number = tbl_Progress.Job_Number " & _
" GROUP BY tbl_Progress.Progress, tbl_Progress.Date,
tbl_Employee.EmployeeID, tbl_Activity.ActivityDate,
tbl_Activity.Units, tbl_Activity.Rate, tbl_Progress.Job_Number,
tbl_Jobs.Job_ID, tbl_Employee.Name, tbl_Employee.Initials,
tbl_Jobs.Job_Duration, tbl_Jobs.Job_Name, tbl_Jobs.date_Value,
tbl_Jobs.Job_Budget " & _ " HAVING (((tbl_Progress.Date)
Between Now() And DateAdd("m",-4,Now())) AND
((tbl_Employee.EmployeeID)=[Forms]![Eva]![EmployeeID]))"

but it says there are two few parameters expected. I know because its
DAO that I have to change the values for the form reference (and maybe
the date) but after hours of fiddling around I cannot get it to work.
Could someone changeout the above VBA code with an sql string that
would work?

thanks alot.
Click to expand...

The simplest method is to simply take the form reference out of the quoted
string...

....Between Now() And DateAdd("m",-4,Now()))
AND ((tbl_Employee.EmployeeID)=" & [Forms]![Eva]![EmployeeID] & "))"
 
P

p

Ok I did that - and i made the date values a variable in the VBA code
and took the form reference out of the ""'s. This is the error I get
now:

Run-time error '3075'

Syntax error (missing operator) in query expression
'(((tbl_Progress.Date) Between 19/01/2007 1:49:38 PM And 19/09/2006
1:49:38 PM ) AND ((tbl_Employee.EmployeeID) = 18))'

Any idea what im missing? Do i have to snip the date values to not
include mins/second?


Rick said:
p wrote:
Could someone changeout the above VBA code with an sql string that
would work?

thanks alot.
Click to expand...

The simplest method is to simply take the form reference out of the quoted
string...

...Between Now() And DateAdd("m",-4,Now()))
AND ((tbl_Employee.EmployeeID)=" & [Forms]![Eva]![EmployeeID] & "))"
Click to expand...
 
J

John Vinson

Ok I did that - and i made the date values a variable in the VBA code
and took the form reference out of the ""'s. This is the error I get
now:

Run-time error '3075'

Syntax error (missing operator) in query expression
'(((tbl_Progress.Date) Between 19/01/2007 1:49:38 PM And 19/09/2006
1:49:38 PM ) AND ((tbl_Employee.EmployeeID) = 18))'

Any idea what im missing? Do i have to snip the date values to not
include mins/second?
Click to expand...

All that's missing is the required # delimiters, and you need to use
American mm/dd/yyyy format for literal dates:

((tbl_Progress.Date) Between #01/19/2007 1:49:38 PM# And #09/19/2006
1:49:38 PM# ) AND ((tbl_Employee.EmployeeID) = 18))'

You should probably use Date() rather than Now() if you don't want the
last four months to the nearest second; and wrap the Date() in a
Format expression such as

Format(Date(), "\#mm\/dd\/yyyy\#")

John W. Vinson[MVP]
 
Top