DSUM Multiple Criteria issue

[JHarmon]

Hello all,
I am new here and need some help with a DSUM issue. I don't use this function often and can't seem to make it work when using two sets of criteria. Here is what it looks like. The output is all the same value. It works until I add the AND section. Any help is greatly appreciated.

Expr1: DSum("[OpenPO]![Open Tons]","OPenPO","[Delivery Date]=#" & [Start Date] & "#" And "[Material] ='" & [Component] & "'")

 

[bob fitz]

It works until I add the AND section.

And what happens then

 

[JHarmon]

It returns the entire SUM of Open Tons

 

[Minty]

You have your quotes all muddled up

Expr1: DSum("[OpenPO]![Open Tons]","OPenPO","[Delivery Date]=#" & [Start Date] & "#" And "[Material] ='" & [Component] & "'")

Should be

 Expr1: DSum("[OpenPO]![Open Tons]","OPenPO","[Delivery Date]=#" & [Start Date] & "# And [Material] ='" & [Component] & "'")

 

[JHarmon]

Bob,
I figured it out. I removed the " after the second # and before [Material] and it worked. Thank you for checking. I greatly appreciate it.

 

[JHarmon]

Thank you so much. I have spent hours trying to get this to work

 

[JHarmon]

Minty,
Sorry to bother you again. What would it look like to make this work BETWEEN two dates. Ex: BETWEEN [Start Date] AND [End Date] AND [Material]...

 

[Minty]

Are your dates just dates or are they datetimes ?

 

[JHarmon]

Just Dates

 

[Minty]

If they are straight dates it's allegedly simple;

DSum("[OpenPO]![Open Tons]","OPenPO","[Delivery Date] Between #" & [Start Date] & "# And  #" & [EndDate] & # AND [Material] ='" & [Component] & "'")

 

[JHarmon]

I am getting an Invalid Date Value error from the following code

# AND [Material] ='" & [Component] & "'")

 

[Minty]

I am getting an Invalid Date Value error from the following code

# AND [Material] ='" & [Component] & "'")

I've missed a quote apologies !

However as a hint to finding problems with complicated criteria can I suggest you build the criteria as a string before the dlookup / dsum e.g.

dim strCriteria as string
strCriteria = "[Delivery Date] Between #" & [Start Date] & "# And  #" & [EndDate] & [COLOR="Red"][B]"[/B][/COLOR]# AND [Material] ='" & [Component] & "'")
msgbox strCriteria  'or debug.print strCriteria
DSum("[OpenPO]![Open Tons]","OPenPO", sCriteria)

This is handy as you can see what's in your criteria as it runs.