Dateserial issue

[jamesdpmullan]

Hi,

After some help if possible.

I am trying to obtain data for the last two full years are current year.

So, i need the query to show data in this case for all of 2014, 2015 and current YTD.

The criteria below doesn't seem to work - any ideas?

Between DateAdd("yyyy",-2,DateSerial(Year(Date()),Month(Date()),0)) And DateSerial(Year(Date()),Month(Date())+1,0)

 

[plog]

Define "doesn't seem to work".

Error? No results? Unexpected results? Fabric of space/time continium being torn apart?

I think it would help if you found out what those exact dates the DateAdd and DateSerial were generating. You're close, but not exactly there.

 

[jamesdpmullan]

Apologies:

Lets say we are in December 2015; I would expect to see full 2013,full 2014 and 2015 YTD.

I get 2015 YTD, full 2014 but only December 2013.

Thanks,

 

[plog]

If you want to fix what you have, you need to tweak the actual dates you are producing with the DateAdd and DateSerial. The query works, so obviously its producing valid dates. Find out what those dates are.

With that said, I think this would be easier if you could just look at the year:

WHERE Year([YourDateField])>=Year(Date())-2

That will pull back anything greater than 1/1/2014. The problem is if you have things in the future--data dated tomorrow and beyond. If that's the case you just plop one more criteria on it:

AND [YourDateField]<=Date()

 

[jamesdpmullan]

Hi,

Thanks this works a treat.

The add on "AND [YourDateField]<=Date()" doesn't seem to work though - an error appears, however, i only need to first section - thanks for your help