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

SteveL13 used Ask the Experts™
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?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Remove the " ".
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

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


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,

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018

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)

Use this as the query criteria for the date field you want to use.
Between DateSerial(Year(Date()),1,1) And Date()


Perfect.  Thanks.

Your welcome.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial