We help IT Professionals succeed at work.

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

SteveL13
SteveL13 asked
on
86 Views
Last Modified: 2017-03-22
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?
Comment
Watch Question

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

Commented:
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

Author

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,

????
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Perfect.  Thanks.

Commented:
Your welcome.