E
eddieK
Want to create query showing orders 30 60 90 days overdue. would like to
calculate these dates without vba....tku 2003 or 2007
calculate these dates without vba....tku 2003 or 2007
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
John Spencer
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
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
E
eddieK
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.
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 said: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
Want to create query showing orders 30 60 90 days overdue. would like to
calculate these dates without vba....tku 2003 or 2007
J
John Spencer
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
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
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 said: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
Want to create query showing orders 30 60 90 days overdue. would like to
calculate these dates without vba....tku 2003 or 2007
E
eddieK
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.
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 said: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
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 said: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
J
John Spencer
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
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
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 said: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
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.
:
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
E
eddieK
sorry to be so dense... aded DueDate field a second time and placed
expression in criteria field. However, the output showed the same date that
was in the DueDate field???
expression in criteria field. However, the output showed the same date that
was in the DueDate field???
J
John Spencer
Please copy and post the SQL of your query.
(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
E
eddieK
SELECT pastdue.CustomerID, pastdue.OrderDate, pastdue.DueDate, pastdue.DueDate
FROM pastdue
WHERE (((pastdue.DueDate)<DateAdd("d",-89,Date())));
FROM pastdue
WHERE (((pastdue.DueDate)<DateAdd("d",-89,Date())));
E
eddieK
After my lights came back on, I realized that the "<" sign did not have
anything to do with your solution. However removing the symbol still doesn'd
solve the problem. Did i miss something else?
tky for your continued patience
anything to do with your solution. However removing the symbol still doesn'd
solve the problem. Did i miss something else?
tky for your continued patience
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.