what is wrong with dsum function

[water911]

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# ")

 

[Will_B]

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.

 

[water911]

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

 

[Will_B]

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.

 

[water911]

ADVERTISEMENT

M is date filed in table formated with mmm
M1 filed on form formated as mmm

 

[Will_B]

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.

 

[water911]

ADVERTISEMENT

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-comfficeffice" /><o> </o>
<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></o>​

</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></o>​

</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></o>​

</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></o>​

</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></o>​

</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></o>​

</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></o>
</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></o>​

</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></o>​

</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></o>
</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></o>​

</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></o>​

</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></o>
</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></o>​

</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></o>​

</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></o>
</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></o>​

</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></o>​

</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></o>
</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></o>​

</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></o>​

</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></o>
</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></o>​

</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></o>​

</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></o>
</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></o>​

</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></o>​

</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></o>
</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></o>​

</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></o>​

</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></o>
</TD></TR></TBODY></TABLE>

 

[Will_B]

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".

 

[water911]

yes the date is vary from 1st till the end of the month
is the code going to be changed

 

[Will_B]

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.