DSum() function in report (1 Viewer)

eacollie

Registered User.
Local time
Yesterday, 19:22
Joined
May 14, 2011
Messages
159
I have a report where I am trying to use the DSum() function as the control source for a text box and am getting the error #Name? I can't figure out where the problem is and need some help.:banghead:

The table I'm querying is tblTEMPMealCount:
MealDate (date)
BreakfastRM (integer)
MealLocation(text)
(the relevant fields)

I open the report with a form "frmSelectDate" and get [StartDate] from it. (I keep this form open until the report is closed so it is accessible to the report.) I need to display in the report from Sunday to Saturday of the week of the selected date from the form.

My control source (for BreakfastRM on Sunday of the week) is:
Code:
=DSum("[BreakfastRM]","tblTEMPMealCount",[MealLocation]="Location1" And [MealDate]=[Forms]![frmSelectDate].[StartDate]-Weekday([Forms]![frmSelectDate].[StartDate])+1)
)

Thank you!
 

Minty

AWF VIP
Local time
Today, 02:22
Joined
Jul 26, 2013
Messages
10,355
You have messed up the quotes a bit, try

Code:
=DSum("BreakfastRM","tblTEMPMealCount","[MealLocation]='Location1' And [MealDate]=[Forms]![frmSelectDate].[StartDate]-Weekday([Forms]![frmSelectDate].[StartDate])+1")
 

plog

Banishment Pending
Local time
Yesterday, 21:22
Joined
May 11, 2011
Messages
11,613
Your criteria argument is incorrect. It must end up as a string. A string can be made up of 2 things:

Code - which exists outside of quote marks
Literals - which exist inside quote marks

When you want to add parts together you must use ampersands (&). You have not done that. Here's what you have:

Code Literal Code

1. No ampersands seperating the code from the literals

2. Some of your code portions should be literals.

Using those rules, give it another shot and post back here.
 

eacollie

Registered User.
Local time
Yesterday, 19:22
Joined
May 14, 2011
Messages
159
Thanks. I think I understand what you're saying and tried the following:
Code:
=DSum("[BreakfastRM]","tblTEMPMealCount",[MealLocation]='Location1' And [MealDate]= # " & Format([Forms]![frmSelectDate].[StartDate]-Weekday([Forms]![frmSelectDate].[StartDate])+1), 'mm/dd/yyyy') & "#"
but get "the expression you entered has an invalid date value.
 

Mark_

Longboard on the internet
Local time
Yesterday, 19:22
Joined
Sep 12, 2017
Messages
2,111
Two things,
First, you may be better off using either an actual subquery to return your sum that is linked in to each record OR see if you can have the reports totaling functions do this for you. You will notice faster response from either.

Second, DSum, like all of the other domain functions, allows you to pass it a string variable for the "Where" clause. I'd highly recommend DIMing a string, fill it with the text you need for your WHERE, and use one of the methods to show you what its contents are prior to using DSum.

Code:
DIM asWhere as STRING
asWhere = "[MealLocation]='Location1' And [MealDate]= #" & Format([Forms]![frmSelectDate].[StartDate]-Weekday([Forms]![frmSelectDate].[StartDate])+1), 'mm/dd/yyyy') & "#"
MsgBox "Where is " & asWhere
'
'
'
'
'
=DSum("[BreakfastRM]","tblTEMPMealCount", asWhere)

This way you can verify if there are any data or spelling issues prior to using DSum.
 

eacollie

Registered User.
Local time
Yesterday, 19:22
Joined
May 14, 2011
Messages
159
Thank you Mark.
I'm not sure I understand what you are suggesting.
Is it that I put these calculated values in a query as the record source for the report?
 

Mark_

Longboard on the internet
Local time
Yesterday, 19:22
Joined
Sep 12, 2017
Messages
2,111
You would make a query that does your totaling.

Create a query on TblTempMealCount. Look at the upper right on the ribbon for "Totals".
Your totals query would be using your criteria to return a value for each "MealLocation" You would then join this new query that holds your total to your existing query so you get the total value.

Let us know if you've never joined queries before so we can provide a better answer. If you have, this should be the "Ahh" moment that has it make sense.
 

eacollie

Registered User.
Local time
Yesterday, 19:22
Joined
May 14, 2011
Messages
159
I need to display all the records (from Sunday-Saturday) on one page. I don't think a totals query is going to allow me to do that.

Here's the format for the report:
Header (Week of...)
Sun Mon Tue...
Breakfast
Location (RM SM) (RM SM)....(for each day of the week)
Lunch
Location (RM SM) (RM SM) ...(for each day of the week)
Dinner
Location (RM SM) (RM SM) ... (for each day of the week)

These values are all in the table, I just need to put them in the correct spot; hence the DSum() function.
 

June7

AWF VIP
Local time
Yesterday, 18:22
Joined
Mar 9, 2014
Messages
5,423
You are missing quote marks, also remove space follow the first #, and there is a misplaced paren (remove the one following +1 and add one after the second "#").

=DSum("[BreakfastRM]", "tblTEMPMealCount", "[MealLocation]='Location1' And [MealDate]= #" & Format([Forms]![frmSelectDate].[StartDate]-Weekday([Forms]![frmSelectDate].[StartDate])+1, "mm/dd/yyyy") & "#")

Not sure Format function is needed.
 
Last edited:

eacollie

Registered User.
Local time
Yesterday, 19:22
Joined
May 14, 2011
Messages
159
June7 - you are amazing! Thank you so much. Doesn't need the format BTW.
 

Users who are viewing this thread

Top Bottom