DateSerial not returning proper prior month last day (1 Viewer)

ClydeL

New member
Local time
Today, 08:23
Joined
Dec 5, 2019
Messages
3
I've got a simple query against a single table and need to return records from the previous month. Searches have led me to believe that DateSerial is the way to go, but I've got an issue with it finding the last day of the prior month.

Running Access 2019 on Win10.

The following returns 11/1/2019 - 11/29/2019 but the table contains records for 11/30. The field IncidentDateTime is datetime.
WHERE IncidentDateTime Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)

This returns 11/30 - 12/4.
IncidentDateTime >= DateSerial(Year(Date()),Month(Date()),0)

This returns 11/29 and prior.
IncidentDateTime <= DateSerial(Year(Date()),Month(Date()),0)

And this returns nothing.
IncidentDateTime= DateSerial(Year(Date()),Month(Date()),0)

Obviously I'm missing something!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:23
Joined
Oct 29, 2018
Messages
21,358
I've got a simple query against a single table and need to return records from the previous month. Searches have led me to believe that DateSerial is the way to go, but I've got an issue with it finding the last day of the prior month.

Running Access 2019 on Win10.

The following returns 11/1/2019 - 11/29/2019 but the table contains records for 11/30. The field IncidentDateTime is datetime.
Hi Clyde. Does the datetime field contain any time components too? If so, you'll need to either add one day to your criteria or add the time component up to 11:59:59 PM. Hope that helps...
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:23
Joined
Sep 21, 2011
Messages
14,044
Could also use the DateVallue() function perhaps?
 

ClydeL

New member
Local time
Today, 08:23
Joined
Dec 5, 2019
Messages
3
Thanks guys! I didn't realize that the time value would influence DateSerial like that. DateValue did the trick.:)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:23
Joined
Oct 29, 2018
Messages
21,358
Thanks guys! I didn't realize that the time value would influence DateSerial like that. DateValue did the trick.:)
Hi. Congratulations! Glad to hear you got it sorted out. Gasman and I were happy to assist. Good luck with your project.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:23
Joined
Jan 20, 2009
Messages
12,849
Could also use the DateVallue() function perhaps?

Best not. DateValue() would have to be applied to the records. For best performance, always try to avoid applying any functions to the records in the Where clause.
 

Users who are viewing this thread

Top Bottom