Dsum criteria

[bdaniel]

Using DSum in a query field expression. I know the criteria in the DSum function can be tricky, having trouble getting this to work.



I have the following query expression:

OH Cost: DSum("[Total OH]","WeeklyCost","[Week]>="&Str([TrnsplWk])&" And [Week]<"&Str([RdyWk])"")



[Week] and [Total OH] are the columns in the WeeklyCost table (this table is not part of the query join).

[TrnsplWk] and [RdyWk] are fields in query

Trying to sum [Total OH] where [Week] is greater than or equal to [TrnsplWk] and less than [RdyWk].

[June7]

Why is it not working - error message, wrong results, nothing? The quote marks at the end should cause an error. Why use the Str function? Show sample data.

[bdaniel]

Database attached.

Here is the expression that works:

OH Cost: DSum("[Total OH]","WeeklyCost","[Week]>=#3/20/2011# And [Week]<#4/3/2011#")

Here I have inserted actual dates for the criteria limits and the result is 1.34 which is the sum of week 3/20/2011 and 3/27/2011. It is the OH Cost field expression in the OH Cost query in the attached database.

In the expression trying to substitute [TrnsplWk] and [RdyWk] fields in the query for the absolute dates in the expression. That way, each record will have OH Cost based on the [TrnsplWk] and [RdyWk] fields for the record.

thanks for taking a look

[June7]

This seems to work:
OH Cost: DSum("[Total OH]","WeeklyCost","[Week]>=#" & [TrnsplWk] & "# And [Week]<#" & [RdyWk] & "#")

Why does TotalOH not always equal the sum of the OH fields? Why are you entering TotalOH in table anyway? This should be a calculation of the OH fields.

[bdaniel]

Thank you for the proper syntax on that criteria with the date fields. Now I get it. Very helpful for this and future projects.

There is extra data in the OH Cost table, not the best practice, but just including data from a spreadsheet that can be easily cut and paste into the table.