Link to home
Start Free TrialLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

Trying to query data from the 1st of the current year through today

I'm trying to query data from the 1st of the current year through today with this syntax in query designer but it seems I have a syntax error.

Between DateSerial(Year("[Closing Date]"),1,1) And Now()

I've also tried

Between DateSerial(Year("[Closing Date]"),1,1) And Date()

But neither one works.  Can someone help?
Avatar of parts8

Remove the " ".
Avatar of crystal (strive4peace) - Microsoft MVP, Access
Between DateSerial(Year([Closing Date]),1,1) And Now()

Open in new window

Quote marks turn the date into a string -- and of course, the fieldname is not a valid date.

If you don't have records AFTER the current date, you can also do this:
>= DateSerial(Year([Closing Date]),1,1) 

Open in new window

You should also add criteria for [Closing Date]:
Is Not Null

Open in new window

Avatar of SteveL13


I've tried the suggestions but am getting all the records instead of just those from 1/1/2017 through Now().  If I replace the suggestions with this:

Between #1/1/2017# And Now()

It works fine.  But I don't want to hard code the 1st date of the current year,

DateSerial(Year([Closing Date]),1,1)

will not return dates prior to the year of Closing Date except if Closing Date is not of data type Date. If Text, use:

    DateSerial(Year(DateValue([Closing Date])),1,1)

Avatar of parts8

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfect.  Thanks.
Your welcome.