Query Date Criteria to filter Current Year and next 2 years...???? :(

[farhanleos]

Gentlemen,

Greetings

I have a query having a date field
I want criteria to show complete current year records and future 2 years records

I am stuck in this please any one can help me out ?

I tried below criteria but it shows just next 2 years data

Between(DateAdd("yyyy",+2,date())) And Date()



Thanks

 

[Minty]

You can get the Start date for the beginning of the current year by using

 DateSerial(Year(Date()), 1, 1)

Other really useful functions here :https://msdn.microsoft.com/en-us/li...v=office.14).aspx?f=255&MSPPError=-2147217396

 

[arnelgp]

Year([dateField]) Between Year(Date) And DateAdd("yyyy", 2, Date)

 

[farhanleos]

Year([dateField]) Between Year(Date) And DateAdd("yyyy", 2, Date)
by thos criteria I got msg box
the expression is too complex?

sir any other suggestion ?

 

[farhanleos]

Year([dateField]) Between Year(Date) And DateAdd("yyyy", 2, Date)

sir by putting the above criteria I got msg you
The expression you entered is too complex ?

 

[arnelgp]

WHERE Year([yourDateField]) Between Year(Date()) And Year(Date())+2

 

[farhanleos]

WHERE Year([yourDateField]) Between Year(Date()) And Year(Date())+2

Dear SIr Arnel,

thanks for your support
by this way my actual field with full date removed , and new expression field appears with years only this is nice
but sir actually I want to keep my date field in query , I want to filter that date column with the dates come under current year from January up till next 2 years


hope you get my point ?

Thanks a lot appreciate your support
I feel happy by joining this forum for access

 

[farhanleos]

Thanks you sir it also ok for me thanks a lot
will disturb you for further query

 

[arnelgp]

hi, the SQL i gave you will do the
filter from jan 1, 2017 to dec 31, 2019 (2 years).

but if you want to be specific and clear you
can change it to:

WHERE [yourDateField] BETWEEN DateSerial(Year(Date()), 1, 1) AND DateSerial(Year(Date())+2, 12, 31)


****
DateSerial will return a True Date.
the syntax is:

DateSerial(YearNumber, MonthNumber, DayNumber)