Date Computation

[Guest]

I need to generate a report based on a query which displays a list where
Date1 exceeds Date2 by more than 10 days. My query looks something like this:

SELECT [field1], [field2], [date1], [date2] FROM table1 WHERE
[date1]-[date2]>10

Seems like it doesn't work the way I thought it would work. Any suggestions?
Thanks.

 

[Douglas J. Steele]

How did you think it would work, and what's happening instead?

That query should give you what you're looking for (although it'll have
problems with records where either date1 or date2 is null).

To be able to include Null dates, try:

SELECT [field1], [field2], [date1], [date2] FROM table1 WHERE (Nz([date1],
Date() + 11)) -Nz([date2], Date())) >10

or

SELECT [field1], [field2], [date1], [date2] FROM table1 WHERE DateDiff("d",
Nz([date1], Date() + 11), Nz([date2], Date())) >10

 

[Guest]

Try

SELECT [field1], [field2], [date1], [date2] FROM table1 WHERE
DateDiff("d",[date1],[date2]) < -10

 

[Guest]

WHERE DateDiff("d", [date1], [date2]) > 10

 

[Guest]

Thanks.
ck

Try

SELECT [field1], [field2], [date1], [date2] FROM table1 WHERE
DateDiff("d",[date1],[date2]) < -10

--
Good Luck
BS"D

I need to generate a report based on a query which displays a list where
Date1 exceeds Date2 by more than 10 days. My query looks something like this:

SELECT [field1], [field2], [date1], [date2] FROM table1 WHERE
[date1]-[date2]>10

Seems like it doesn't work the way I thought it would work. Any suggestions?
Thanks.

 

[Guest]

Douglas, you mean that my formual there should work? Mmm....you were right
about the null dates because no records were selected at first. Later I did
enter some dates for date1 & date2 but still no records were selected.
Anyway, I will try your suggestion and see what happens. Thanks.
ck

How did you think it would work, and what's happening instead?

That query should give you what you're looking for (although it'll have
problems with records where either date1 or date2 is null).

To be able to include Null dates, try:

SELECT [field1], [field2], [date1], [date2] FROM table1 WHERE (Nz([date1],
Date() + 11)) -Nz([date2], Date())) >10

or

SELECT [field1], [field2], [date1], [date2] FROM table1 WHERE DateDiff("d",
Nz([date1], Date() + 11), Nz([date2], Date())) >10



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I need to generate a report based on a query which displays a list where
Date1 exceeds Date2 by more than 10 days. My query looks something like
this:

SELECT [field1], [field2], [date1], [date2] FROM table1 WHERE
[date1]-[date2]>10

Seems like it doesn't work the way I thought it would work. Any
suggestions?
Thanks.

 

[Guest]

Thanks, Klatuu.
ck

WHERE DateDiff("d", [date1], [date2]) > 10

I need to generate a report based on a query which displays a list where
Date1 exceeds Date2 by more than 10 days. My query looks something like this:

SELECT [field1], [field2], [date1], [date2] FROM table1 WHERE
[date1]-[date2]>10

Seems like it doesn't work the way I thought it would work. Any suggestions?
Thanks.