DSUM Syntax with multiple Criteria (1 Viewer)

DataBass

Registered User.
Local time
Today, 14:21
Joined
Jun 6, 2018
Messages
68
I want to calculate a total in a report. It seems to me that a DSUM field would work perfectly, but I absolutely cannot figure out the syntax.

I have a table called tblLabor with the following fields
ProjectID, LaborID, LaborDate, Total


This is working. It gives me the labor total for a specific ProjectID:

Code:
=DSum("Total","tblLabor","ProjectID=" & "[Reports]![rptIncomeExpense]![ProjectID]")
But I need to add one more criteria specifying a date range. I want [LaborDate] to be between [txtStartDate] and [txtEndDate] on my form frmReportGenerator.

I'm guessing it would be something like:

Code:
=DSum("Total","tblLabor","ProjectID=" & "[Reports]![rptIncomeExpense]![ProjectID]" AND [LaborDate] between [Forms]![frmReportGenerator]![txtStartDate] and [Forms]![frmReportGenerator]![txtEndDate]
Obviously this isn't right, but this gives you the idea of what I'm trying to accomplish

I've searched and searched and tried a million different ways to write this and nothing works.

Could anyone tell me how to write this DSUM function?
 

DataBass

Registered User.
Local time
Today, 14:21
Joined
Jun 6, 2018
Messages
68
The trick the syntax for using "between".
I've seen a million contradictory examples and none are working
 

isladogs

MVP / VIP
Local time
Today, 21:21
Joined
Jan 14, 2017
Messages
18,186
Assuming ProjectID is a number field, your original DSum for ProjectID only should have been:
Code:
=DSum("Total","tblLabor","ProjectID=" & [Reports]![rptIncomeExpense]![ProjectID])

You need date delimiters for the second part
Hopefully no errors below

Code:
=DSum("Total","tblLabor","ProjectID=" & [Reports]![rptIncomeExpense]![ProjectID] & " AND [LaborDate] Between #" & [Forms]![frmReportGenerator]![txtStartDate] & "# And #" & [Forms]![frmReportGenerator]![txtEndDate] & "#")
 

DataBass

Registered User.
Local time
Today, 14:21
Joined
Jun 6, 2018
Messages
68
OK, that looks like what I need.

Plugging it in now...hold your breath...
 

DataBass

Registered User.
Local time
Today, 14:21
Joined
Jun 6, 2018
Messages
68
Oh man.

That worked. I'm stepping away from the ledge now....

Incredible.

A few thoughts:
1. I'm going to go broke buying all of you guys beer.
2. It's amazing how much incorrect info is out there about syntax. Correct me if I'm wrong, but even this "Art of Writing Criteria" page has errors. Is there a definitive Access VBA syntax manual anywhere?
3. I've looked at your code and seeing an example that actually works, it mostly makes sense, but since you clearly understand this very well, would you mind clarifying a couple things?

I understand that the 3 domain aggregate arguments need to be enclosed in "". The 1st 2 arguments are single objects, got it.

In the 3rd argument, since [Reports]![rptIncomeExpense]![ProjectID] is a number field, why does it need to be enclosed in ""?

Since [Forms]![frmReportGenerator]![txtStartDate] is a date field, why are there quotes inside the # signs?

Again, you have no idea how much this has helped me. I have spent just about the entire day working on this.

Sincere thanks
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 08:21
Joined
Jan 20, 2009
Messages
12,849
In the 3rd argument, since [Reports]![rptIncomeExpense]![ProjectID] is a number field, why does it need to be enclosed in ""?

All the parameters in domain functions are Strings even though the ProjectID is a number. If ProjectID were text then the expression would require delimiters.

Code:
=DSum("Total","tblLabor","ProjectID='" & [Reports]![rptIncomeExpense]![ProjectID] & "'")
Since [Forms]![frmReportGenerator]![txtStartDate] is a date field, why are there quotes inside the # signs?
The quotes are surrounding the bold parts.
Code:
& " [B]AND [LaborDate] Between #[/B]" & [Forms]![frmReportGenerator]![txtStartDate] & "[B]# And #[/B]" &
The # delimiters will end up around the dates returned from the textbox.


BTW There is a subtle but important difference between the two following expressions.

This one concatenates the value from the textbox into the third parameter string. The appropriate delimiters for the datatype of the field must be included in the expression. The Date values must also be reformatted if not already in US or ISO format.

Code:
=DSum("Total","tblLabor","ProjectID=" & [Reports]![rptIncomeExpense]![ProjectID])
This second one passes the reference to the texbox inside the third parameter. Access then interprets it before performing the DSum. Access inserts delimiters and handles the formatting of dates according to the regional settings so it is much simpler to write.

Code:
=DSum("Total","tblLabor","ProjectID=[Reports]![rptIncomeExpense]![ProjectID]")
There really isn't much point doing the concatenation if the values are being read directly from a textbox.

One advantage might be that the reference to the textbox can be tested during compilation in concatenated expressions. I don't think the strings in domain functions would be checked but I have never tested it.

Either way, it won't make a difference where a bang is used before the control. The bang is Late Bound so the reference to the control is not tested for validity during compilation. Consequently it is generally better to use the dot rather than the bang when referring to a control.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:21
Joined
Feb 19, 2002
Messages
42,976
Syntax can be tricky. You have to get your head around the difference between the string you are typing and the string you are building. My best advice for building strings using VBA is to build them into a variable. That way, you can print the variable to see if your syntax is correct. Because you are trying to do this in a control's ControlSource, you don't have any option so either don't put the expression in the ControlSource, use VBA in an event to populate the unbound control OR for testing, build the string in code and when you have it working, put it in the ControlSource.

So, for testing.

=DSum("Total","tblLabor","ProjectID=" & [Reports]![rptIncomeExpense]![ProjectID] & " AND [LaborDate] Between #" & [Forms]![frmReportGenerator]![txtStartDate] & "# And #" & [Forms]![frmReportGenerator]![txtEndDate] & "#")

Would be

Dim strWHERE as String
strWHERE = "[ProjectID] = " & [Reports]![rptIncomeExpense]![ProjectID] & " AND [LaborDate] Between #" & [Forms]![frmReportGenerator]![txtStartDate] & "# And #" & [Forms]![frmReportGenerator]![txtEndDate] & "#")

Me.somecontrol DSum("Total","tblLabor","ProjectID=" & strWHERE)

That gives you the ability to look at strWHERE which is frequently all you need to do to see the error.
 
Last edited:

DataBass

Registered User.
Local time
Today, 14:21
Joined
Jun 6, 2018
Messages
68
Ridders, Galaxiom and Pat, thanks for taking the time to shed light on this. It has been VERY helpful.

The quotes are surrounding the bold parts.
Code:
& " [B]AND [LaborDate] Between #[/B]" & [Forms]![frmReportGenerator]![txtStartDate] & [B]"# And #"[/B] &

This was actually a mind blower. I had been thinking of it like this:

Code:
" [B]& [Forms]![frmReportGenerator]![txtStartDate] &[/B] "

This actually makes sense now.

This second one passes the reference to the texbox inside the third parameter. Access then interprets it before performing the DSum. Access inserts delimiters and handles the formatting of dates according to the regional settings so it is much simpler to write.

Code:
=DSum("Total","tblLabor","ProjectID=[Reports]![rptIncomeExpense]![ProjectID]")
There really isn't much point doing the concatenation if the values are being read directly from a textbox.

One advantage might be that the reference to the textbox can be tested during compilation in concatenated expressions. I don't think the strings in domain functions would be checked but I have never tested it.

Either way, it won't make a difference where a bang is used before the control. The bang is Late Bound so the reference to the control is not tested for validity during compilation. Consequently it is generally better to use the dot rather than the bang when referring to a control.

I generally followed this, but honestly I lost you at "testing during compilation and Late Bound, etc. I'm sure I'll get there, but I'm not there yet.

Me.somecontrol DSum("Total","tblLabor","ProjectID=" & strWHERE)

That gives you the ability to look at strWHERE which is frequently all you need to do to see the error.

This is brilliant. Great idea. Yes, all the difficulty is in the 3rd argument or the criteria. A variable would make this much easier.

Thanks again
 

DataBass

Registered User.
Local time
Today, 14:21
Joined
Jun 6, 2018
Messages
68
Would any of you mind helping with one other thing with this exact same calculated control? I'm trying to make this whole thing shorter by using variables. My question is only about the last part:

Code:
[LaborDate] Between #" & [Forms]![frmReportGenerator]![txtStartDate] & "# And #" & [Forms]![frmReportGenerator]![txtEndDate] & "#"
I have created 2 global variables: rptStartDate and rptEndDate. In the After Update event of frmReportGenerator, I set

Code:
rptStartDate = [Forms]![frmReportGenerator]![txtStartDate]
rptEndDate = [Forms]![frmReportGenerator]![txtEndDate]
In my DSum function how would I refer to these global variables instead of using the form references?

I have tried several different approaches and nothing seems to work. It doesn't seem that I can simply replace [Forms]![frmReportGenerator]![txtStartDate] with rptStartDate.

Again, any input would be appreciated
 

isladogs

MVP / VIP
Local time
Today, 21:21
Joined
Jan 14, 2017
Messages
18,186
First of all, make sure your variables are defined as Date
I would prefix with dte to make that clear
I also wouldn't use global variables unless there is a reason to do so

Code:
Dim dteStartDate As Date
Dim dteEndDate As Date

dteStartDate = [Forms]![frmReportGenerator]![txtStartDate]
dteEndDate = [Forms]![frmReportGenerator]![txtEndDate]

[LaborDate] Between #" & dteStartDate & "# And #" & dteEndDate & "#"

If the code is called from your form, this can be simplified using Me. operator

Code:
dteStartDate = Me.txtStartDate
dteEndDate = Me.txtEndDate

Also a reminder that date format needs to be mm/dd/yyyy in VBA so if that's not your default format then you need to use

Code:
[LaborDate] Between #" & Format(dteStartDate, "mm/dd/yyyy") & "# And #" & Format(dteEndDate,"mm/dd/yyyy") & "#"
 

DataBass

Registered User.
Local time
Today, 14:21
Joined
Jun 6, 2018
Messages
68
Thanks, Ridders.

My rationale for using global variables was that I planned on closing frmReportGenerator, instead of simply making it invisible, when the report is opened and I needed somewhere to store the dates selected on that form.

Is that a bad idea?

Yes, rptStartDate and rptEndDate are date variables and I am using the default mm/dd/yyyy format. Just for testing, I left their names as is. I will add the dte prefix later. Thanks for the idea.

I setup the Dsum control with this as its control source:

Code:
=DSum("Total","tblLabor","ProjectID=" & [Reports]![rptIncomeExpense]![ProjectID] & " AND [LaborDate] Between #" & rptStartDate & "# And #" & rptEndDate & "#")
but I still get prompted to enter rptStartDate and rptEndDate when I run the report.

When I go back into design view and inspect the control source, Access has surrounded the variables with [ ]. Apparently it thinks these variables are fields.

I just learned that you can't use global variables in query criteria because VBA and the Access engine are separate. Could the same thing be happening here?
 

June7

AWF VIP
Local time
Today, 13:21
Joined
Mar 9, 2014
Messages
5,423
Queries cannot find global declared variables but they can find TempVars. There's something else for you to research and learn about. I've never used them.
 

isladogs

MVP / VIP
Local time
Today, 21:21
Joined
Jan 14, 2017
Messages
18,186
Queries can also use functions. For example

Code:
Function GetStartDate()

GetStartDate=dteStartDate

End Function

You can then use GetStartDate instead of dteStartDate in your query criteria
 

MarkK

bit cruncher
Local time
Today, 14:21
Joined
Mar 17, 2004
Messages
8,178
I don't recommend globals. Maybe a few global objects, but I would never globally expose criteria for one query or the recordsource of one form or the controlsource of one control.

In the scenario you describe, where the user provides data in a form and the form is used to run a query, I would hide the form and program the query to draw the data from the hidden form rather than create a bunch of globals or tempvars.
imo
mark
 

DataBass

Registered User.
Local time
Today, 14:21
Joined
Jun 6, 2018
Messages
68
Thanks for the input everyone.

In the scenario you describe, where the user provides data in a form and the form is used to run a query, I would hide the form and program the query to draw the data from the hidden form rather than create a bunch of globals or tempvars.
imo

I think I have to agree. This is how it worked originally, but I thought it might be advantageous/simpler to go the variable route. But now it's looking like I'm making things much more complicated to make them "simpler".

Just for my own understanding, does it make sense that this Dsum control wouldn't be able to find these global variables? It seems like this could be useful in the future and I'd just like to know if this actually is an option.

Thanks again
 

isladogs

MVP / VIP
Local time
Today, 21:21
Joined
Jan 14, 2017
Messages
18,186
Variables whether global or not, work fine in VBA code which is what I thought you were referring to BUT they do not work in queries

As already stated, you can use functions instead in query criteria or use the original [Forms]![frmReportGenerator]![txtStartDate]
 
Last edited:

DataBass

Registered User.
Local time
Today, 14:21
Joined
Jun 6, 2018
Messages
68
Sorry for the confusion. I understand that query criteria can't access vba variables. I thought that since this Dsum was in a report control it might be different, but I think it isn't.

Regardless, I have a much better grasp of domain aggregate syntax now.

Thanks for the help.
 

Users who are viewing this thread

Top Bottom