Dsum error (1 Viewer)

Jim Stanicki

Registered User.
Local time
Today, 18:39
Joined
Aug 7, 2007
Messages
36
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

Registered User
Local time
Today, 18:39
Joined
Jun 9, 2004
Messages
13,327
= Dsum("[RMA Qty]","Inventory Transactions", "[Inventory Transactions]![created Date]< Cdate(" & [forms]![askdate]![txtStartDate] & ")")

???
 

boblarson

Smeghead
Local time
Today, 15:39
Joined
Jan 12, 2001
Messages
32,059
If created Date is a date field then you will probably have to do this:
Code:
= 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

Registered User.
Local time
Today, 18:39
Joined
Aug 7, 2007
Messages
36
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

Smeghead
Local time
Today, 15:39
Joined
Jan 12, 2001
Messages
32,059
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

Registered User.
Local time
Today, 18:39
Joined
Aug 7, 2007
Messages
36
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

Registered User
Local time
Today, 18:39
Joined
Jun 9, 2004
Messages
13,327
Try this:

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

???
 

boblarson

Smeghead
Local time
Today, 15:39
Joined
Jan 12, 2001
Messages
32,059
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

Registered User.
Local time
Today, 18:39
Joined
Aug 7, 2007
Messages
36
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

Registered User.
Local time
Today, 18:39
Joined
Aug 7, 2007
Messages
36
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
 

Users who are viewing this thread

Top Bottom