VBA with a variable in a query using a dsum funtion

M

Mona-ABE

Hi,
When I use an Access query (SQL copied below) as the recordsource for a
report with specific dates, it works perfectly. I'd like to use the same SQL
in VBA in the report's OnOpen event to set the RecordSource property and use
variables in place of the dates 5/1/2006, 6/1/2006 and 7/1/2006 respectively.
I've been able to use the SELECT, FROM AND ORDER BY portions of the SQL with
no problem, but am receiving errors on the portions with the nz(dsum(....)
statements. I'm sure it's the syntax, and I need help!! :)

Thanks in advance for the advice!
Mona
-------------------------------------

SELECT tblPrjTrkConsultantLeaderList.projEmployeeID, [projFirstName] & " " &
[projLastName] AS Resource,
nz(DSum("[tblPrjTrkForecastedDays]![ProjForecastDays]","tblPrjTrkForecastedDays","[tblPrjTrkForecastedDays]![ProjEmplID]="
& [projEmployeeID] & " And
[tblPrjTrkForecastedDays]![ProjForecastMonth]=#5/1/2006#"),0) AS
[MO1-1Forecast],
nz(DSum("[tblPrjTrkActualDays]![ProjActualDays]","tblPrjTrkActualDays","[tblPrjTrkActualDays]![ProjEmplID]="
& [projEmployeeID] & " And
[tblPrjTrkActualDays]![ProjActualMonth]=#5/1/2006#"),0) AS [MO1-2Actual],
[MO1-1Forecast]-[MO1-2Actual] AS [MO1-3Difference],
nz(DSum("[tblPrjTrkForecastedDays]![ProjForecastDays]","tblPrjTrkForecastedDays","[tblPrjTrkForecastedDays]![ProjEmplID]="
& [projEmployeeID] & " And
[tblPrjTrkForecastedDays]![ProjForecastMonth]=#6/1/2006#"),0) AS
[MO2-1Forecast],
nz(DSum("[tblPrjTrkActualDays]![ProjActualDays]","tblPrjTrkActualDays","[tblPrjTrkActualDays]![ProjEmplID]="
& [projEmployeeID] & " And
[tblPrjTrkActualDays]![ProjActualMonth]=#6/1/2006#"),0)
AS [MO2-2Actual], [MO2-1Forecast]-[MO2-2Actual] AS [MO2-3Difference],
nz(DSum("[tblPrjTrkForecastedDays]![ProjForecastDays]","tblPrjTrkForecastedDays","[tblPrjTrkForecastedDays]![ProjEmplID]="
& [projEmployeeID] & " And
[tblPrjTrkForecastedDays]![ProjForecastMonth]=#7/1/2006#"),0) AS
[MO3-1Forecast],
nz(DSum("[tblPrjTrkActualDays]![ProjActualDays]","tblPrjTrkActualDays","[tblPrjTrkActualDays]![ProjEmplID]="
& [projEmployeeID] & " And
[tblPrjTrkActualDays]![ProjActualMonth]=#7/1/2006#"),0) AS [MO3-2Actual],
[MO3-1Forecast]-[MO3-2Actual] AS [MO3-3Difference]
FROM tblPrjTrkConsultantLeaderList
ORDER BY [projFirstName] & " " & [projLastName];
 
D

Dale Fye

Mona,

When I use DSUM, DLOOKUP, DMIN, ..., I like to create a string that defines
the criteria and use that variable inside the DSUM function, makes it easier
to read.

In your case, you will need a couple of these criteria. I have also created
variables for you 5/1/2006 (dtDate1), 6/1/2006 (dtDate2), and 7/1/2006
(dtDate3)

Dim strCriteria1 as string
strCriteria1 = "[tblPrjTrkForecastedDays]![ProjEmplID]=" & [projEmployeeID]
& " And [tblPrjTrkForecastedDays]![ProjForecastMonth]=# " & dtDate1 & "#"

Dim strCriteria2 as string
strCriteria2 = "[tblPrjTrkForecastedDays]![ProjEmplID]=" & [projEmployeeID]
& " And [tblPrjTrkForecastedDays]![ProjForecastMonth]=#" & dtDate2 & "#"

I think you get the idea here. Then just place these criteria variables in
your DSUM functions to make it easier to read.

On the other hand, you might want to consider using a crosstab query for
this. I think it is likely to run quicker than doing all of these DSUM
operations. If your data set is not very large you might not be able to see
much difference, but with a large data set, I think the crosstab would be
much quicker. If you want some help with the crosstab, reply back to the
newsgroup and I'll see if I cannot help you format that query correctly.

HTH
Dale

Mona-ABE said:
Hi,
When I use an Access query (SQL copied below) as the recordsource for a
report with specific dates, it works perfectly. I'd like to use the same
SQL
in VBA in the report's OnOpen event to set the RecordSource property and
use
variables in place of the dates 5/1/2006, 6/1/2006 and 7/1/2006
respectively.
I've been able to use the SELECT, FROM AND ORDER BY portions of the SQL
with
no problem, but am receiving errors on the portions with the nz(dsum(....)
statements. I'm sure it's the syntax, and I need help!! :)

Thanks in advance for the advice!
Mona
-------------------------------------

SELECT tblPrjTrkConsultantLeaderList.projEmployeeID, [projFirstName] & " "
&
[projLastName] AS Resource,
nz(DSum("[tblPrjTrkForecastedDays]![ProjForecastDays]","tblPrjTrkForecastedDays","[tblPrjTrkForecastedDays]![ProjEmplID]="
& [projEmployeeID] & " And
[tblPrjTrkForecastedDays]![ProjForecastMonth]=#5/1/2006#"),0) AS
[MO1-1Forecast],
nz(DSum("[tblPrjTrkActualDays]![ProjActualDays]","tblPrjTrkActualDays","[tblPrjTrkActualDays]![ProjEmplID]="
& [projEmployeeID] & " And
[tblPrjTrkActualDays]![ProjActualMonth]=#5/1/2006#"),0) AS [MO1-2Actual],
[MO1-1Forecast]-[MO1-2Actual] AS [MO1-3Difference],
nz(DSum("[tblPrjTrkForecastedDays]![ProjForecastDays]","tblPrjTrkForecastedDays","[tblPrjTrkForecastedDays]![ProjEmplID]="
& [projEmployeeID] & " And
[tblPrjTrkForecastedDays]![ProjForecastMonth]=#6/1/2006#"),0) AS
[MO2-1Forecast],
nz(DSum("[tblPrjTrkActualDays]![ProjActualDays]","tblPrjTrkActualDays","[tblPrjTrkActualDays]![ProjEmplID]="
& [projEmployeeID] & " And
[tblPrjTrkActualDays]![ProjActualMonth]=#6/1/2006#"),0)
AS [MO2-2Actual], [MO2-1Forecast]-[MO2-2Actual] AS [MO2-3Difference],
nz(DSum("[tblPrjTrkForecastedDays]![ProjForecastDays]","tblPrjTrkForecastedDays","[tblPrjTrkForecastedDays]![ProjEmplID]="
& [projEmployeeID] & " And
[tblPrjTrkForecastedDays]![ProjForecastMonth]=#7/1/2006#"),0) AS
[MO3-1Forecast],
nz(DSum("[tblPrjTrkActualDays]![ProjActualDays]","tblPrjTrkActualDays","[tblPrjTrkActualDays]![ProjEmplID]="
& [projEmployeeID] & " And
[tblPrjTrkActualDays]![ProjActualMonth]=#7/1/2006#"),0) AS [MO3-2Actual],
[MO3-1Forecast]-[MO3-2Actual] AS [MO3-3Difference]
FROM tblPrjTrkConsultantLeaderList
ORDER BY [projFirstName] & " " & [projLastName];
Click to expand...
 
M

Mona-ABE

You know, I thought a crosstab was the way to go too until I tried to use
one. Since I'm dealing with 3 different tables and need several columns with
calculations, I kept running into a brick wall. I have even set up an Excel
spreadsheet mocking the tables I have in Access so I can send it to someone
who can guide me...would that be you? And how do I get the spreasheet to
you? :)
--
Thanks!
Mona-ABE


Dale Fye said:
Mona,

When I use DSUM, DLOOKUP, DMIN, ..., I like to create a string that defines
the criteria and use that variable inside the DSUM function, makes it easier
to read.

In your case, you will need a couple of these criteria. I have also created
variables for you 5/1/2006 (dtDate1), 6/1/2006 (dtDate2), and 7/1/2006
(dtDate3)

Dim strCriteria1 as string
strCriteria1 = "[tblPrjTrkForecastedDays]![ProjEmplID]=" & [projEmployeeID]
& " And [tblPrjTrkForecastedDays]![ProjForecastMonth]=# " & dtDate1 & "#"

Dim strCriteria2 as string
strCriteria2 = "[tblPrjTrkForecastedDays]![ProjEmplID]=" & [projEmployeeID]
& " And [tblPrjTrkForecastedDays]![ProjForecastMonth]=#" & dtDate2 & "#"

I think you get the idea here. Then just place these criteria variables in
your DSUM functions to make it easier to read.

On the other hand, you might want to consider using a crosstab query for
this. I think it is likely to run quicker than doing all of these DSUM
operations. If your data set is not very large you might not be able to see
much difference, but with a large data set, I think the crosstab would be
much quicker. If you want some help with the crosstab, reply back to the
newsgroup and I'll see if I cannot help you format that query correctly.

HTH
Dale

Mona-ABE said:
Hi,
When I use an Access query (SQL copied below) as the recordsource for a
report with specific dates, it works perfectly. I'd like to use the same
SQL
in VBA in the report's OnOpen event to set the RecordSource property and
use
variables in place of the dates 5/1/2006, 6/1/2006 and 7/1/2006
respectively.
I've been able to use the SELECT, FROM AND ORDER BY portions of the SQL
with
no problem, but am receiving errors on the portions with the nz(dsum(....)
statements. I'm sure it's the syntax, and I need help!! :)

Thanks in advance for the advice!
Mona
-------------------------------------

SELECT tblPrjTrkConsultantLeaderList.projEmployeeID, [projFirstName] & " "
&
[projLastName] AS Resource,
nz(DSum("[tblPrjTrkForecastedDays]![ProjForecastDays]","tblPrjTrkForecastedDays","[tblPrjTrkForecastedDays]![ProjEmplID]="
& [projEmployeeID] & " And
[tblPrjTrkForecastedDays]![ProjForecastMonth]=#5/1/2006#"),0) AS
[MO1-1Forecast],
nz(DSum("[tblPrjTrkActualDays]![ProjActualDays]","tblPrjTrkActualDays","[tblPrjTrkActualDays]![ProjEmplID]="
& [projEmployeeID] & " And
[tblPrjTrkActualDays]![ProjActualMonth]=#5/1/2006#"),0) AS [MO1-2Actual],
[MO1-1Forecast]-[MO1-2Actual] AS [MO1-3Difference],
nz(DSum("[tblPrjTrkForecastedDays]![ProjForecastDays]","tblPrjTrkForecastedDays","[tblPrjTrkForecastedDays]![ProjEmplID]="
& [projEmployeeID] & " And
[tblPrjTrkForecastedDays]![ProjForecastMonth]=#6/1/2006#"),0) AS
[MO2-1Forecast],
nz(DSum("[tblPrjTrkActualDays]![ProjActualDays]","tblPrjTrkActualDays","[tblPrjTrkActualDays]![ProjEmplID]="
& [projEmployeeID] & " And
[tblPrjTrkActualDays]![ProjActualMonth]=#6/1/2006#"),0)
AS [MO2-2Actual], [MO2-1Forecast]-[MO2-2Actual] AS [MO2-3Difference],
nz(DSum("[tblPrjTrkForecastedDays]![ProjForecastDays]","tblPrjTrkForecastedDays","[tblPrjTrkForecastedDays]![ProjEmplID]="
& [projEmployeeID] & " And
[tblPrjTrkForecastedDays]![ProjForecastMonth]=#7/1/2006#"),0) AS
[MO3-1Forecast],
nz(DSum("[tblPrjTrkActualDays]![ProjActualDays]","tblPrjTrkActualDays","[tblPrjTrkActualDays]![ProjEmplID]="
& [projEmployeeID] & " And
[tblPrjTrkActualDays]![ProjActualMonth]=#7/1/2006#"),0) AS [MO3-2Actual],
[MO3-1Forecast]-[MO3-2Actual] AS [MO3-3Difference]
FROM tblPrjTrkConsultantLeaderList
ORDER BY [projFirstName] & " " & [projLastName];
Click to expand...
Click to expand...
 
Top