By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,572 Members | 2,949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,572 IT Pros & Developers. It's quick & easy.

DSum function

P: 76
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
Dec 14 '06 #1
Share this Question
Share on Google+
12 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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?

Expand|Select|Wrap|Line Numbers
  1.  
  2. =DSum("[Hours]","Credit Hours","[Date]>=#[Enter Start Date (MM/DD/YY)]# And [Date]<=#[Enter End Date (MM/DD/YY)]#")
  3.  
OR

Expand|Select|Wrap|Line Numbers
  1. =DSum("[Hours]","Credit Hours","[Date] BETWEEN #[Enter Start Date (MM/DD/YY)]# And #[Enter End Date (MM/DD/YY)]#")
  2.  
Mary
Dec 14 '06 #2

P: 76
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
Dec 14 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 14 '06 #4

P: 76
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
Dec 14 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 14 '06 #6

P: 76
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...
Dec 14 '06 #7

NeoPa
Expert Mod 15k+
P: 31,494
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.
Dec 14 '06 #8

NeoPa
Expert Mod 15k+
P: 31,494
This is essentially Mary's version but without the delimiters.
Expand|Select|Wrap|Line Numbers
  1. =DSum("[Hours]","Credit Hours","[Date] Between [Enter Start Date (MM/DD/YY)] And [Enter End Date (MM/DD/YY)]")
Dec 14 '06 #9

NeoPa
Expert Mod 15k+
P: 31,494
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.
Dec 14 '06 #10

P: 76
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.
Dec 14 '06 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
Are you being asked for the date parameters ?


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

Mary
Dec 15 '06 #12

NeoPa
Expert Mod 15k+
P: 31,494
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.
Dec 15 '06 #13

Post your reply

Sign in to post your reply or Sign up for a free account.