Setting a date Crireria

[Bob V]

In my query I am trying to find records between 365 days ago and 730 days
ago!
Is this correct?

Date()-365-730

Thanks for any help............Bob

 

[tina]

if you're setting criteria on a field with a Date/Time data type, try

Between Date()-365 And Date()-730

but if what you actually want is all dates between 1 and 2 years prior to
the current date, you might find this more accurate, as

Between DateAdd("yyyy", -1, Date()) And DateAdd("yyyy", -2, Date())

keeping in mind that leap year is going to roll around every four, and mess
up a "365, 730" hard-coded number.

hth

 

[Bob V]

Brilliant Tina, Thanks very much for your help..... Bob

 

[John W. Vinson]

In my query I am trying to find records between 365 days ago and 730 days
ago!
Is this correct?


Thanks for any help............Bob

No, it isn't.

Date() is 39314 (it's been that many days since December 30, 1899, the anchor
of the date value).

39314 - 364 -370 is just 38219, or displayed another way, 8/20/2004.

Use a criterion on your date field of

BETWEEN DateAdd("yyyy", -2, Date()) AND DateAdd("yyyy", -1, Date())

This will handle leap years correctly (which your guess wouldn't have even if
it had worked!)

John W. Vinson [MVP]

 

[Bob V]

Thanks John, I did try regards Bob

No, it isn't.

Date() is 39314 (it's been that many days since December 30, 1899, the
anchor
of the date value).

39314 - 364 -370 is just 38219, or displayed another way, 8/20/2004.

Use a criterion on your date field of

BETWEEN DateAdd("yyyy", -2, Date()) AND DateAdd("yyyy", -1, Date())

This will handle leap years correctly (which your guess wouldn't have even
if
it had worked!)

John W. Vinson [MVP]

 

[tina]

you're welcome

Brilliant Tina, Thanks very much for your help..... Bob

 

[Jamie Collins]

if you're setting criteria on a field with aDate/Timedata type, try

BetweenDate()-365 AndDate()-730

If you're setting criteria on a field with a Date/Time data type, why
not use temporal expressions (rather than operate on them as if they
were numeric values)? e.g.

DATEDIFF('D', test_date, DATE()) BETWEEN 365 AND 720

but if what you actually want is all dates between 1 and 2 years prior to
the currentdate, you might find this more accurate, as

Between DateAdd("yyyy", -1,Date()) And DateAdd("yyyy", -2,Date())

....oh, so you *are* aware of temporal expressions <g>! Why the
inconsistent approach?!

Here's another version which IMO is easier for a human reader:

DATEDIFF('YYYY', test_value, DATE()) BETWEEN 1 AND 2

Jamie.

--