Re: find orders 90 days past due
- From: John Spencer <spencer@xxxxxxxxx>
- Date: Tue, 22 Sep 2009 15:10:38 -0400
Did you add your date field into the design grid a second time?
Did you put the criteria into a criteria "cell" under the second instance of the date field.
Field: [Your Date Field]
Table: [Your table Name]
Criteria: < DateAdd("d",-89,Date())
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
eddieK wrote:
the first methow worked great!. however, i messed up the second instruction. When entered with the <dateadd, i received an error message 'expression contains invalid data". i am using DueDate field to store dates and used 9/1/2009..
If i remove the < then the query runs, but shows a date of of 9/26/2009 in the calculation field.
Thank you for your help.
"John Spencer" wrote:
Use an expression like:
IIF(Date()-[SomeDateField]>=90,"Yes",Null)
And if you are talking about limiting the records to just those over or = to 90 day. Add the datefield into your query (again if needed) and set the criteria to
<DateAdd("d",-89,Date())
That will only return records where your field is over 90 days from today.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
eddieK wrote:John:
tku for the quick reply. I was able to run the quert successfully. However is there a way to only show the records that are >90 days, rather that displaying all records90)records. Also, instead of showing -1 in the calculated field, can yes be displayed.
"John Spencer" wrote:
You can use an expression like the following to calculate in a query the number of days overdue.
Date()-[SomeDateField]>=90
Date()-[SomeDateField]>=60 AND Date()-[SomeDateField]<90
Date()-[SomeDateField]>=30 AND Date()-[SomeDateField]<60
That will return true or false. If you want the True/False to appear in as a value in a query and you are using the design view to build the query, you would enter something like the following to return -1 (90 Days overdue), Not 90 days overdue.
Field: Over90: Date()-[SomeDateField]>=90
If you want to do that in one field, then you would need and expression like the following. All on one line, but entered here with line feeds to make it easier to read.
IIF(Date()-[SomeDateField]>=90, "OverDue 90",
IIF(Date()-[SomeDateField]>=60 AND Date()-[SomeDateField]<90,"Overdue 60",
IIF(Date()-[SomeDateField]>=30 AND Date()-[SomeDateField]<60,"OverDue 30",
"Current")))
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
eddieK wrote:Want to create query showing orders 30 60 90 days overdue. would like to calculate these dates without vba....tku 2003 or 2007
- References:
- find orders 90 days past due
- From: eddieK
- Re: find orders 90 days past due
- From: John Spencer
- Re: find orders 90 days past due
- From: eddieK
- Re: find orders 90 days past due
- From: John Spencer
- Re: find orders 90 days past due
- From: eddieK
- find orders 90 days past due
- Prev by Date: Union query producing duplicate records
- Next by Date: Update Query
- Previous by thread: Re: find orders 90 days past due
- Next by thread: RE: find orders 90 days past due
- Index(es):
Relevant Pages
|
