DSum not working properly

[heather001]

I can't figure out what I have wrong in my code. I've searched all over the forums and the help files in Access. I'm sure it's something simple. Could someone please take a look?

=DSum([DefectQty],"d_sub1_DefectReport","CodeID= "" & [tblDefectCodes].[CodeID] & "" AND ""[InspectDate] >= #" & [Forms]![frmDefectDetailReport]![txtStartDate] & "#" And "[InspectDate] <= #" & [Forms]![frmDefectDetailReport]![txtEndDate] & "#")


I'm trying to get a sum of all defect quantities between a date range from a user form. Rather than summing all the defect quantities for a defect code, it's summing up the total quantites for each quantity. I'm sure that doesn't make sense so here's an example:

InspectDate CodeID DefectQty DSum Should Be
6/1/06 100 1 3 5
6/2/06 100 2 4 5
6/3/06 105 1 3 1
6/4/06 103 1 3 1
6/5/06 100 2 4 5


The domain is a query because the dates come from a different table than the defect quantities, so the query is pulling all the relevant data to be summed.

 

[Dreamweaver]

Change [DefectQty] For "DefectQty" And not sure if that Where statement is correct at all Try using the builder which is the little button to the right where you add your DSUM.

Mick

 

[pbaldy]

You've got too many quotes in there. Here's a good reference:

http://www.mvps.org/access/general/gen0018.htm

 

[heather001]

Thanks for the help but I'm still having trouble.
The double set of quotes were from trying to call it in a sql statement in vba.
To narrow down the source of my problem I've since removed the date criteria so it looks like this:
DSum("DefectQty","d_sub1_DefectReport","CodeID='[CodeID]")
This returns no values

DSum("DefectQty","d_sub1_DefectReport","CodeID=[CodeID]")
This returns a sum of all DefectQty with no regard to CodeID

DSum([DefectQty],"d_sub1_DefectReport","CodeID=[CodeID]")
This returns a value that reflects my original post

If I remove [CodeID] in the criteria and place a Code from my db there (ie 205),
DSum("DefectQty","d_sub1_DefectReport","CodeID='205'")
This returns the right value for the code 205

I've tried different methods to call the CodeID but nothing is working properly. CodeID is a field in my query and is a text field, thus it requires a single asterisk but other than that it should seem that

DSum("DefectQty","d_sub1_DefectReport","CodeID='[CodeID]'")

Should work but it does not.

any thoughts?

 

[pbaldy]

None of those follow the format from the link:

DSum("DefectQty","d_sub1_DefectReport","CodeID='" & [CodeID] & "'")

 

[heather001]

I'm not referring to a form control, hence I didn't use this method. Although, I did however try this method before my first post and I still had the same outcome as I described previously.
I'll try this method later today when I'm at work and report back with my results.

 

[heather001]

I finally got it working with this code:
DSum("DefectQty","d_sub1_DefectReport","[tblDefectCodes]![CodeID]='" & [tblDefectCodes]![CodeID] & "'")

I think I was missing a punctuation mark or two the first time I tried this.

Thanks for the help!