Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-16-2018, 06:26 AM   #1
eacollie
Newly Registered User
 
Join Date: May 2011
Posts: 152
Thanks: 23
Thanked 0 Times in 0 Posts
eacollie is on a distinguished road
DSum() function in report

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.

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!

eacollie is offline   Reply With Quote
Old 08-16-2018, 06:35 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,696
Thanks: 138
Thanked 1,532 Times in 1,504 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: DSum() function in report

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")
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 08-16-2018, 06:37 AM   #3
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,934
Thanks: 10
Thanked 2,147 Times in 2,102 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: DSum() function in report

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.

plog is offline   Reply With Quote
Old 08-16-2018, 06:51 AM   #4
eacollie
Newly Registered User
 
Join Date: May 2011
Posts: 152
Thanks: 23
Thanked 0 Times in 0 Posts
eacollie is on a distinguished road
Re: DSum() function in report

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.
eacollie is offline   Reply With Quote
Old 08-16-2018, 08:35 AM   #5
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: DSum() function in report

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.
Mark_ is offline   Reply With Quote
Old 08-16-2018, 09:07 AM   #6
eacollie
Newly Registered User
 
Join Date: May 2011
Posts: 152
Thanks: 23
Thanked 0 Times in 0 Posts
eacollie is on a distinguished road
Re: DSum() function in report

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?
eacollie is offline   Reply With Quote
Old 08-16-2018, 09:18 AM   #7
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: DSum() function in report

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.

Mark_ is offline   Reply With Quote
Old 08-16-2018, 09:32 AM   #8
eacollie
Newly Registered User
 
Join Date: May 2011
Posts: 152
Thanks: 23
Thanked 0 Times in 0 Posts
eacollie is on a distinguished road
Re: DSum() function in report

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.
eacollie is offline   Reply With Quote
Old 08-16-2018, 09:51 AM   #9
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 806
Thanks: 0
Thanked 182 Times in 182 Posts
June7 will become famous soon enough
Re: DSum() function in report

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.
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 08-16-2018 at 10:07 AM.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
eacollie (08-16-2018)
Old 08-16-2018, 10:41 AM   #10
eacollie
Newly Registered User
 
Join Date: May 2011
Posts: 152
Thanks: 23
Thanked 0 Times in 0 Posts
eacollie is on a distinguished road
Re: DSum() function in report

June7 - you are amazing! Thank you so much. Doesn't need the format BTW.

eacollie is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
DSum Function use in Report. abzalali Reports 0 03-28-2014 10:59 PM
DSum function gloryboat General 1 12-22-2012 11:22 AM
DSum function gloryboat General 1 12-19-2012 10:37 AM
DSum Function from another table than the report's source jlathem Reports 5 11-02-2010 04:45 PM
Dsum Function dr223 Forms 1 01-03-2008 08:46 AM




All times are GMT -8. The time now is 04:15 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World