Dsum can not see dates with 0

[Baybars]

After so much brainstorming, I finally got a control source for my
textbox. It is looking for summary of current date of active form.

=DSum("[VALUE]";"TableOfValue";"[Date]=# " & Forms![CurrentForm]!Date&
"#")

This works fine for a date like 12/11/03, but if there is a date with
0 like 04/12/03, it doesn't see it. The formats for table and forms
date textbox are "dd/mm/yy".

What am I missing?

Regards,
Baybars

 

[Duane Hookom]

Only thing I notice is the extra space following "[Date]=#".
BTW: Date isn't a good name for a field since it is also the name of a
function.

 

[Douglas J. Steele]

Dates must be in mm/dd/yyyy format when you're using them like that.

What's happening is that 04/12/03 is being treated as 12 Apr, 2003, not as 4
Dec, 2003.

The normal approach is to use the Format function to represent your date the
way it's required:

=DSum("[VALUE]";"TableOfValue";"[Date]=# " &
Format(Forms![CurrentForm]!Date, "mm/dd/yyyy") & "#")

Assuming that the regional settings are set correctly, you could also try:

=DSum("[VALUE]";"TableOfValue";"[Date]=CDate('" & Forms![CurrentForm]!Date&
"')")

Note the single quotes in the CDate function: it's

CDate( ' " & Forms![CurrentForm]!Date& " ' ) "

You might find Allen Browne's "International Dates in Access" at
http://members.iinet.net.au/~allenbrowne/ser-36.html a worthwhile read.