Dsum error

[Jim Stanicki]

Would someone please tell me where I have gone astray?

= Dsum("[RMA Qty]","Inventory Transactions", "[Inventory Transactions]![created Date]< Cdate([forms]![askdate]![txtStartDate])")

Thanks
Jim

 

[KenHigg]

= Dsum("[RMA Qty]","Inventory Transactions", "[Inventory Transactions]![created Date]< Cdate(" & [forms]![askdate]![txtStartDate] & ")")

???

 

[boblarson]

If created Date is a date field then you will probably have to do this:

= Dsum("[RMA Qty]","Inventory Transactions", "[created Date]< #" & [forms]![askdate]![txtStartDate] & "#)")

Also, you don't need to add the table name in the criteria because you already told Access that it is looking in the Inventory Transactions table. So you just need to specify the field and its criteria. For dates you should use the date delimiter of #.

 

[Jim Stanicki]

I can't get either to work yet but whats the scoop on the ampersand? My book says they are used for concatenation and formating
Thanks
Jim

 

[boblarson]

The ampersands concatenate things together. What you are doing when you have something like:

"[created Date] < #" & [forms]![askdate]![txtStartDate] & "#"

Is that you are wanting the VALUE from [forms]![askdate]![txtStartDate] and not the actual text of "[forms]![askdate]![txtStartDate]." Access needs that outside of the quotes so that it knows you want the value from that text box.

 

[Jim Stanicki]

Man I hate to be stupid but I just can't get this to work. My report prints "#Error" where it should be printing the Dsum field.
This is what I am using..
= Dsum("[RMA Qty]","Inventory Transactions", "[created Date]< #" & [forms]![askdate]![txtStartDate] & "#")

 

[KenHigg]

Try this:

= Dsum("[RMA Qty]","Inventory Transactions", "[created Date] < " & CDate([forms]![askdate]![txtStartDate]))

???

 

[boblarson]

Hopefully Ken's suggestion will work for you and you might double-check to see if your created date field is truly a date/time field and not text. If it is text Ken's CDate code should work for you where the other wouldn't.

 

[Jim Stanicki]

A tip of the hat (and the little scale thing) to the both of you. I had a learning experience. The pound sign method worked (it was a date). Two more questions if I may.
Sometimes after keying an expression then exiting the field. I return to find it reverted to the way it was. Is this Access telling me I made a syntax error?

Is their anyway to use the debugger on an expression?

Thanks again for your help. Someday I hope to pass it along.
Jim

 

[Jim Stanicki]

One more question on DSum
I have a " =Sum()" in the item header and it works as expected, totaling for the item.
I replace it with a "=DSum()" and it seems to be a report total.
How do I get the DSum to act like the Sum and just total for that item?
Thanks
Jim