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?
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

parts8Commented:
Remove the " ".
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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

0
SteveL13Author Commented:
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,

????
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Gustav BrockCIOCommented:
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
0
parts8Commented:
Use this as the query criteria for the date field you want to use.
Between DateSerial(Year(Date()),1,1) And Date()
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
Perfect.  Thanks.
0
parts8Commented:
Your welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.