Help with Query syntax

[Rich]

Hello All,

I need help with the syntax of a query used for a report. This report
should detail each record where the expiration date comes in less than 30
days. This is what I am trying to accomplish:

Select field1, field2, field3, EOLdate, days_remaining
from table
where days_remaining < 30

Days remaining is a computed value, not a column. I believe I need to use
the DateDiff function like this: DateDiff("d","Date","EOLDate") < 30

Just to be sure I'm stating this clearly....

days_remaining = EOLDate - today (in days).
So, any record where the expiration date occurs in 30 days or less should be
returned.

Finally, is it a best practice to build a query, then base the report off of
the query, or simply build the query right in the report.

Thanks in advance,
Rich

 

[John Spencer]

SELECT Field1, Field2, Field3, EOLDate,
DateDiff("d",Date(),[EOLDate]) as DaysRemaining
FROM Table
WHERE DateDiff("d",Date(),[EOLDate]) < 30

Or perhaps more efficient if EOLDate is indexed,

SELECT Field1, Field2, Field3, EOLDate,
DateDiff("d",Date(),[EOLDate]) as DaysRemaining
FROM Table
WHERE EolDate >= DateAdd("d",30,Date())

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Rich]

That's it!
Thanks very much John!!!

SELECT Field1, Field2, Field3, EOLDate,
DateDiff("d",Date(),[EOLDate]) as DaysRemaining
FROM Table
WHERE DateDiff("d",Date(),[EOLDate]) < 30

Or perhaps more efficient if EOLDate is indexed,

SELECT Field1, Field2, Field3, EOLDate,
DateDiff("d",Date(),[EOLDate]) as DaysRemaining
FROM Table
WHERE EolDate >= DateAdd("d",30,Date())

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello All,

I need help with the syntax of a query used for a report. This report
should detail each record where the expiration date comes in less than 30
days. This is what I am trying to accomplish:

Select field1, field2, field3, EOLdate, days_remaining
from table
where days_remaining < 30

Days remaining is a computed value, not a column. I believe I need to use
the DateDiff function like this: DateDiff("d","Date","EOLDate") < 30

Just to be sure I'm stating this clearly....

days_remaining = EOLDate - today (in days).
So, any record where the expiration date occurs in 30 days or less should be
returned.

Finally, is it a best practice to build a query, then base the report off of
the query, or simply build the query right in the report.

Thanks in advance,
Rich