Dcount with Date criteria (1 Viewer)

MrHans

Registered User
Local time
Today, 20:13
Joined
Jul 27, 2015
Messages
147
Hi all,

I can't get this sorted.
I have created a report where I want to display the total number of records that were created today. So I have set the following control source for my textbox.

Code:
=DCount("[EditRecordID]";"tblActionLog";"Format([EditDate];'mm\/dd\/yyyy') = Format(Date();'mm\/dd\/yyyy'")
Or
=DCount("[EditRecordID]"; "tblActionLog"; "[EditDate]=#" & Date() &"#")

EditDate = Date/Time field
EditRecordID = PK

Why is this not working?
 

Minty

AWF VIP
Local time
Today, 19:13
Joined
Jul 26, 2013
Messages
10,355
I don't think you need to format EditDate in the criteria but do the date() so try mixing your two approaches;
Code:
=DCount("[EditRecordID]";"tblActionLog";"[EditDate] = #" & Format(Date();'mm\/dd\/yyyy') & "#" )
 

MrHans

Registered User
Local time
Today, 20:13
Joined
Jul 27, 2015
Messages
147
Thank you Minty, but no, it doesn't change anything, it returns 0.
So not an error, but 0.

When I look at the table directly, the date is visible like this:
12-10-2016 11:22:53
 

Minty

AWF VIP
Local time
Today, 19:13
Joined
Jul 26, 2013
Messages
10,355
Ah so you do have a time component.

Try >=Date() as the default for date() would be 12/10/2016 00:00:00 (UK Style) and you want anything for today.
 

MrHans

Registered User
Local time
Today, 20:13
Joined
Jul 27, 2015
Messages
147
Excellent, been struggling for hours on this...
Thank you very much for the explanation also, this makes sense indeed.

Code:
=DCount("[EditRecordID]";"tblActionLog";"[SourceTable]='tblCustomers' AND [EditDate]>=Date()")
 

Users who are viewing this thread

Top Bottom