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?

parts8

Remove the " ".

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

SteveL13

ASKER

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,

????

Gustav Brock

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)

/gustav

ASKER CERTIFIED SOLUTION

parts8

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SteveL13

ASKER

Perfect. Thanks.

parts8

Your welcome.