what is wrong with dsum function

water911

New Member
Joined
Mar 30, 2009
Messages
9
Dear
i am trying to make dsum function read from different filed which is (date) but it is not working ,
please advise


D2 = DSum("Qty", "AllD", " [M]=[M1] ")
where [M1] date can be enter by end user

but if i make date as # 01/01/2009# it does give value

D3 = DSum("Qty", "AllD", " [Grade] = [C1] and [M]= #01/01/2009# ")
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
As you have your DSUM written now, you are saying that there is actually a field called "M1" in the table or query named "AllD", and so the equation is trying to find it. How to fix this problem depends on whether you want:

(A) a pop-up box to ask the user for a date or
(B) to get the date from field "M1" on your form.

Both solutions are below. The concept in both is that we "pause" the criteria string to insert our date variable, regardless of where that variable comes from.

(A) If you want a pop-up box to ask the user to enter a date, you have to phrase your DSUM as follows:

D2 = DSum("Qty", "AllD", " [M]=#" & [M1] & "#")

If this pop-up box is what you want, you should use something more descriptive than "M1", such as "Enter a date:". That is the text that will appear in the pop-up.

(B) If M1 is a field on your form, the DSUM should look like this:
D2 = DSum("Qty", "AllD", " [M]=#" & Me![M1] & "#")

... and you might have to drop the # if the field is already formatted as a date. If the form is a subform or another form that does not have the focus, you'll have to make an explicit reference to that form.
 
Upvote 0
thanks Will_B<SCRIPT type=text/javascript> vbmenu_register("postmenu_1888951", true); </SCRIPT>
i apprchated your words & full explination
you are right in each single words , i test all of them , but they did not excute any thing

i wish i i could send you the access file so u can look it
 
Upvote 0
Is "M1" the name of a field in a table? If so, are both [M] and [M1] fields of data type "Date"? If one of them is text, that could be the problem.

Are M1 and M fields both in the same table or are they each in a different table? Let us know.

Look inside your table design and tell us what the data types are for [M] and [M1].

If [M1] is not the name of a field in a table, but is the name of a field on a form, what is the format for the field? Look in the fields properties under "Format" to find out and let us know.
 
Upvote 0
I need one more piece of information: what are the conditions when you want the DSUM to add up all the Qty values?

(A) Do you want to add up all the Qty values for a specific date (e.g., 3/31/2009) that the user types into M1 --- or

(B) Do you want to add up all the Qty values for the MONTH (e.g, "MAR") of the current year (2009) that the user specifies (with the result that all March 2009 records, regardless of day, will be added up.

Let me know if it is A or B that you are trying to achieve --- or if it is something else entirely.
 
Last edited:
Upvote 0
C1: There is combo box read from all grades that may available in Grade column
D1 where I will display output when end user select C1
D1 = DSum("Qty", "AllD", " [Grade] =[C1]") ----- It is working fine
I have another filed in same form which is called M1 , M2 , M3 … M12
M1 = DateAdd("m", 1, [M0])
Where M0 text box in date format which enter or selected from calendar by end user
My objective to see how much Qty for specific grade & month which will be selected by end user
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
<TABLE class=MsoNormalTable style="BACKGROUND: white; mso-cellspacing: 0in; mso-yfti-tbllook: 1184" cellSpacing=0 cellPadding=0 border=1><THEAD><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0.75pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 0.75pt; BACKGROUND: silver; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #ece9d8; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #ece9d8" colSpan=3>
AllD<o:p></o:p>
</TD></TR><TR style="mso-yfti-irow: 1"><TD style="BORDER-RIGHT: black 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: black 1pt inset; PADDING-LEFT: 0.75pt; BACKGROUND: silver; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: black 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: black 1pt inset; mso-border-alt: inset black .75pt">
M<o:p></o:p>
</TD><TD style="BORDER-RIGHT: black 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: black 1pt inset; PADDING-LEFT: 0.75pt; BACKGROUND: silver; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: black 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: black 1pt inset; mso-border-alt: inset black .75pt">
Qty<o:p></o:p>
</TD><TD style="BORDER-RIGHT: black 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: black 1pt inset; PADDING-LEFT: 0.75pt; BACKGROUND: silver; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: black 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: black 1pt inset; mso-border-alt: inset black .75pt">
Grade<o:p></o:p>
</TD></TR></THEAD><TBODY><TR style="mso-yfti-irow: 2"><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>
2/1/2009<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>
160<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>999W<o:p></o:p>
</TD></TR><TR style="mso-yfti-irow: 3"><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>
2/1/2009<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>
0<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>999W<o:p></o:p>
</TD></TR><TR style="mso-yfti-irow: 4"><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>
3/1/2009<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>
0<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>318X<o:p></o:p>
</TD></TR><TR style="mso-yfti-irow: 5"><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>
1/1/2009<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>
240<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>999W<o:p></o:p>
</TD></TR><TR style="mso-yfti-irow: 6"><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>
1/1/2009<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>
880<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>318X<o:p></o:p>
</TD></TR><TR style="mso-yfti-irow: 7"><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>
1/1/2009<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>
160<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>999W<o:p></o:p>
</TD></TR><TR style="mso-yfti-irow: 8"><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>
1/1/2009<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>
80<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>999W<o:p></o:p>
</TD></TR><TR style="mso-yfti-irow: 9"><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>
1/1/2009<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>
240<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>999W<o:p></o:p>
</TD></TR><TR style="mso-yfti-irow: 10; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>
1/1/2009<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>
320<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #d0d7e5 1pt inset; PADDING-RIGHT: 0.75pt; BORDER-TOP: #d0d7e5 1pt inset; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; BORDER-LEFT: #d0d7e5 1pt inset; PADDING-TOP: 0.75pt; BORDER-BOTTOM: #d0d7e5 1pt inset; BACKGROUND-COLOR: transparent; mso-border-alt: inset #D0D7E5 .75pt" vAlign=top>318X<o:p></o:p>
</TD></TR></TBODY></TABLE>
 
Upvote 0
Thanks for the details. Using that and the sample data you provide, I was able to create the form below. I've displayed the DSUM code next to each Qty total. If you compare it to your data, you'll see that it works.

This assumes that your "M" dates would always the the 1st of the month. If that is not true --- if you your "M" dates might be any day of the month, then let me know.

The M1...M3 dates are all formated as "mmm".

dsum.jpg
 
Last edited:
Upvote 0
I'm afraid I don't understand your last reply. Let me know if the DSUM code shown on the form works for you on your form. If you are also using the DSUM in a report, we would need to change the DSUM slightly. Let me know what you still need.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top