DSum function

[ckpoll2]

Hello,

I'm trying to write a function that will sum the Hours column in the Credit Hours query for a user defined date range. This is the function that I have so far.

=DSum("[Hours]","Credit Hours","Date>=[Enter Start Date (MM/DD/YY)] And <=[Enter End Date (MM/DD/YY)]")

It seems like it should work, but I keep getting a #Error. What is wrong with my function? I have been pulling my hair out trying to figure this out.

Thanks,

Charlie

[MMcCarthy]

Hello,

I'm trying to write a function that will sum the Hours column in the Credit Hours query for a user defined date range. This is the function that I have so far.

=DSum("[Hours]","Credit Hours","Date>=[Enter Start Date (MM/DD/YY)] And <=[Enter End Date (MM/DD/YY)]")

It seems like it should work, but I keep getting a #Error. What is wrong with my function? I have been pulling my hair out trying to figure this out.

Thanks,

Charlie

Either of the following will work?

 
=DSum("[Hours]","Credit Hours","[Date]>=#[Enter Start Date (MM/DD/YY)]# And [Date]<=#[Enter End Date (MM/DD/YY)]#")
 

OR

=DSum("[Hours]","Credit Hours","[Date] BETWEEN #[Enter Start Date (MM/DD/YY)]# And #[Enter End Date (MM/DD/YY)]#")
 

Mary

[ckpoll2]

Mary,

Thanks for the response and for looking into my problem. I tried both of those formulas and both gave me a #Error. Is there something I have to change in the text box characteristics? Also, I noticed the # signs in front and at the end of the [Enter] boxes. What they're entering isn't a number. Is there some kind of symbol that could represent a date rather than a number? Is there anything else I can do?

Thanks so much,

Charlie

[MMcCarthy]

Hi Charlie

I know it doesn't make sense but # is the date representation in Access.

Rules:

String/Text - surround with single quotes
Date - surround with #
Number - no symbol required

You are summing the number of hours so the text box needs to be a number rather than a date/time format as Access cannot calculate in time beyond 24 hours. Once you have the number we can provide the formula to show the number of hours.

Are you being asked for the date parameters and did you enclose the date field in square brackets as per my example. In access Date is a function that returns the current date and you need to designate it as a field by enclosing it in square brackets. Ideally it would be a good idea to change the name of this field.

Mary

Mary,

Thanks for the response and for looking into my problem. I tried both of those formulas and both gave me a #Error. Is there something I have to change in the text box characteristics? Also, I noticed the # signs in front and at the end of the [Enter] boxes. What they're entering isn't a number. Is there some kind of symbol that could represent a date rather than a number? Is there anything else I can do?

Thanks so much,

Charlie

[ckpoll2]

I tried changing Date to TDate and used the same formula without the [] around it and that didn't work. Also, I don't know what you mean when you say once I have the number we can provide the formula to show the number of hours.

I copied and pasted the formula exactly from your post to the cell, so it appears as you typed it.

Is there any other way that you know of that I can accomplish this instead of going this route as it seems that this isn't wanting to work?

Charlie

[MMcCarthy]

Did you change the data type of the textbox control to a number. Format as Single or double by the way.

Mary

I tried changing Date to TDate and used the same formula without the [] around it and that didn't work. Also, I don't know what you mean when you say once I have the number we can provide the formula to show the number of hours.

I copied and pasted the formula exactly from your post to the cell, so it appears as you typed it.

Is there any other way that you know of that I can accomplish this instead of going this route as it seems that this isn't wanting to work?

Charlie

[ckpoll2]

I set the format to "General Number". Is this what you mean? Also, I'm not sure I understand the single or double. I'm still a beginner at Access...

[NeoPa]

Coming in here late I know, but Date variables don't need #s around them and a value passed by the operator (as you use) works like a variable.
Give me a second & I will post a version of your code for you.

[NeoPa]

This is essentially Mary's version but without the delimiters.

=DSum("[Hours]","Credit Hours","[Date] Between [Enter Start Date (MM/DD/YY)] And [Enter End Date (MM/DD/YY)]")

[NeoPa]

I've just updated the thread explaining all this to include this situation more clearly - (Literal DateTimes and Their Delimiters (#).). Have a look if you're interested.

[ckpoll2]

I really don't mean to keep crushing your ideas, but that one didn't work either. When you all are posting these formulas, are they working for you? I'm just trying to figure out if there's something that I'm doing wrong.

This is in a report and is in the report footer. Is that what is causing the problem?

Thank you for your continued support.

[MMcCarthy]

Are you being asked for the date parameters ?


I have a feeling you can't enter parameters like this in a report footer.

Mary

[NeoPa]

I really don't mean to keep crushing your ideas, but that one didn't work either. When you all are posting these formulas, are they working for you? I'm just trying to figure out if there's something that I'm doing wrong.

This is in a report and is in the report footer. Is that what is causing the problem?

Thank you for your continued support.

Charlie,

In almost all situations on here we are unable to reproduce the question on our machines. Mainly because too little information is provided but also many questions are determined by data or database setup which we simply don't have available to us without high levels of work. Such levels that would make answering the number of questions here completely impractical.
In some circumstances I do test the code I provide but they are rare.
So, in answer, it generally comes from the tops of our heads.

In this case, it would be helpful to know exactly how it failed and to see the code you actually tried. Sometimes we can see that members have not quite followed the answer when they post their code. Other times we know it's more complicated. It's rarely the latter.

I'm trying to write a function that will sum the Hours column in the Credit Hours query for a user defined date range. This is the function that I have so far.

I understood from this that you were working within a query. If that is not true and you are putting this calculation in your report footer, then I'm afraid that won't work for you.
To get around this you can add these two fields to you query and ignore them everywhere except in the report footer. This should give you the result you're after.