DCOUNT Problem

[RobMc]

I wonder if anyone can help...I'm using domain functions to get data for a pop-up detailing last record in a table & number of entries made that day...the relevant part of the code is:

Dim dtLast As Date
Dim intCount As Integer

dtLast = DMax("[Date Received]", "tblLoad")
intCount = DCount("[Date Received]", "tblLoad", "[Date Received] = dtLast")

However, I get an error "The object doesn't contain the automation object 'dtLast'

I've tried many different syntaxes but always get a 'type mismatch' error.

I'd appreciate any words of wisdom!

Thanks

 

[Mile-O]

Currently you are literally comparing the date field to the string "dtLast" - you need to break this out from the string and, since it's a date, use the hash delimiters.

intCount = DCount("[Date Received]", "tblLoad", "[Date Received] = #" & dtLast & "#")

 

[RobMc]

Thanks for the right syntax. Interestingly, although this runs error-free, it returns 0 against the dcount. In the relevant table, there are 4 records with the selected date....any ideas?

Thanks

 

[Mile-O]

Format the dtLast variable to an American date format.

"[Date Received] = #" & Format(dtLast, "mm/dd/yyyy") & "#")

 

[RobMc]

Thanks again, but the thing still counts 0. I've checked the table/field, which is formatted as short date, which I thought would be okay.

Funnily enough, the value it's trying to count is 12/12/2005, so by pure chance UK or US format should have worked...

Apologies for being a continuing pain in the rear, but...any more ideas?

Thanks again

 

[Mile-O]

Take the formatting off the table, out of interest. What happens?

 

[namliam]

Yep I am guessing the date in the table is containing the time ...

Try

"format([Date Received],"mm/dd/yyyy") = #" & Format(dtLast, "mm/dd/yyyy") & "#")

Or

"[Date Received] >= #" & Format(dtLast, "mm/dd/yyyy") & "#")