Queries that have specific time frames

[Guest]

My question is this. How do I query from a specific date? I want to run
reports of a suspense date that shows greater than 30 days, greater than 60
days, ect. I am not sure how to do the expressions. If I want a report that
shows suspense dates that are olderthan 30 days ect.

 

[Guest]

I do this by writing the data to a temp table, with an extra column called
Aging.

Then populate the Aging column with the value 30, 60, 90, etc. (Update
query, or do it during the append.)

For the report, base the data on the temp table and group the data by the
Aging column.

 

[Guest]

Would you mind giving me an example?
--
Susan

I do this by writing the data to a temp table, with an extra column called
Aging.

Then populate the Aging column with the value 30, 60, 90, etc. (Update
query, or do it during the append.)

For the report, base the data on the temp table and group the data by the
Aging column.


--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

 

[John Spencer]

You could use a calculated field.

Field: MonthsOld: (DateDiff("d",[YourDateField],Date())\30) * 30

That should
--return the number of days between today and your date field
--Integer divide the result by 30 giving you 0,1,2,etc
--Multiply the result by 30 giving you 0, 30, 60, 90, etc.

0-29 would return 0
30-59 would return 30
60-89 would return 60
etc.

If you want 0 to 30, 31 to 60, 61 to 90, etc subtract 1 from the datediff
result before you do the calculation
Field: MonthsOld: ((DateDiff("d",[YourDateField],Date())-1)\30) * 30


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

I still do not understand. Please be patient. I am new in Access and still
learning. Do I put (DateDiff("d",[YourDateField],Date())\30) * 30 in the
Criteria field on the queries page. I am not sure where I am supposed to put
this information.
--
Susan

You could use a calculated field.

Field: MonthsOld: (DateDiff("d",[YourDateField],Date())\30) * 30

That should
--return the number of days between today and your date field
--Integer divide the result by 30 giving you 0,1,2,etc
--Multiply the result by 30 giving you 0, 30, 60, 90, etc.

0-29 would return 0
30-59 would return 30
60-89 would return 60
etc.

If you want 0 to 30, 31 to 60, 61 to 90, etc subtract 1 from the datediff
result before you do the calculation
Field: MonthsOld: ((DateDiff("d",[YourDateField],Date())-1)\30) * 30


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

My question is this. How do I query from a specific date? I want to run
reports of a suspense date that shows greater than 30 days, greater than
60
days, ect. I am not sure how to do the expressions. If I want a report
that
shows suspense dates that are olderthan 30 days ect.

 

[John Spencer]

That is a calculated field in the query. So you put that in a field "Cell"
in the query. If you want the column that is created to have a name other
than Expr1 you would enter the column name you want followed by a colon
followed by the expression.

The expression will generate values 0, 30, 60, 90, 120, etc. The expression
is
(DateDiff("d",[YourDateField],Date())\30) * 30

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I still do not understand. Please be patient. I am new in Access and
still
learning. Do I put (DateDiff("d",[YourDateField],Date())\30) * 30 in the
Criteria field on the queries page. I am not sure where I am supposed to
put
this information.
--
Susan

You could use a calculated field.

Field: MonthsOld: (DateDiff("d",[YourDateField],Date())\30) * 30

That should
--return the number of days between today and your date field
--Integer divide the result by 30 giving you 0,1,2,etc
--Multiply the result by 30 giving you 0, 30, 60, 90, etc.

0-29 would return 0
30-59 would return 30
60-89 would return 60
etc.

If you want 0 to 30, 31 to 60, 61 to 90, etc subtract 1 from the datediff
result before you do the calculation
Field: MonthsOld: ((DateDiff("d",[YourDateField],Date())-1)\30) * 30


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

My question is this. How do I query from a specific date? I want to
run
reports of a suspense date that shows greater than 30 days, greater
than
60
days, ect. I am not sure how to do the expressions. If I want a
report
that
shows suspense dates that are olderthan 30 days ect.