How to calculate if past due date with criteria?

[Guest]

I have a date field (called "LastUpdated") and a number field (called
"MonthlyUpdateInterval")

The date field holds the date a document was updated, and the number field
holds a number to represent a period in months before the document needs to
be reviewed. So if the last review date was "06/01/06" and the months was "3"
the query should show that a document needs to be reviewed when the today's
date is 09/01/06 or greater.

The results don't need to be exact - the number 30 could be used in a
calculation against the monthly interval field to show that 90 days later the
document needs to be updated - that would be good.
This would help me to generate a monthly report on the documents I have to
manage and keep updated

Thanks

 

[MGFoster]

I have a date field (called "LastUpdated") and a number field (called
"MonthlyUpdateInterval")

The date field holds the date a document was updated, and the number field
holds a number to represent a period in months before the document needs to
be reviewed. So if the last review date was "06/01/06" and the months was "3"
the query should show that a document needs to be reviewed when the today's
date is 09/01/06 or greater.

The results don't need to be exact - the number 30 could be used in a
calculation against the monthly interval field to show that 90 days later the
document needs to be updated - that would be good.
This would help me to generate a monthly report on the documents I have to
manage and keep updated

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SELECT *
FROM Documents
WHERE LastUpdated <= DateAdd("m", -MonthlyUpdateInterval, Date())

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRI7nOYechKqOuFEgEQLZMACfUAGJb1B6LqdAwN82eFh5yXPQs5IAoIkh
xzonGSvS9TqIxK9YQfFkjIcD
=PNHP
-----END PGP SIGNATURE-----

 

[John Spencer]

You can calculate the next review date with DateAdd function.

Field: ReviewDue: DateAdd("m",MonthlyUpdateInterval, LastUpdated)
Criteria: Between #6/1/06# and #6/30/06#

 

[Guest]

Thanks for replying - I pasted that in and changed Documents to the table
name (is that right?) and it generated a syntax error. It looks like this in
my criteria field:

SELECT * FROM DocumentMain WHERE LastUpdated <= DateAdd("m",
-MonthlyUpdateInterval, Date())

So I put it in brackets () and it gave me this message:

"You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field."

Huh? I'm double confused =#

 

[Guest]

Thanks for replying John. I've created this Expression and it prompts me for
the criteria when I run the query (It put both the field names in []
brackets).

What I have is this:

LastUpdated MonthlyUpdateInterval
06/01/06 1
06/01/06 2
06/01/06 3

If I run the query on 07/01/06 only the first document should report, on
08/01/06 only document 1 and 2 should report. On 09/01/06 all three documents
should report. In theory it does not need to be any more complicated than
that because whenever I update a document I will change the LastUpdated value
the current date and in time when I check the query it will come around
again.

Thank you

 

[MGFoster]

That SELECT statement shouldn't be in the Criteria field. Switch to SQL
view & past my original SELECT statement (change the table name as you
did). It should be the only statement in the SQL view. Then try
running it. There should be no error.

 

[Guest]

Hmm, showing my ignorance here. Thanks for my first lesson in SQL!

I began a new query and pasted the statement over the top of the
auto-generated code in SQL view and ran it but it returned no results! Then I
realised that all the dates in my table were set to June, so I changed a
couple at random back to the start of the year and ran the query again and up
they popped! Brilliant! Thanks so much, you've been an enormous help =)

Adrian

 

[John Spencer]

I see you have a solution from MgFoster, so I won't pursue a solution. If I
were, I would suggest you post the SQL text of your query.

View: Sql from the menu.

Thanks for replying John. I've created this Expression and it prompts me
for
the criteria when I run the query (It put both the field names in []
brackets).

What I have is this:

LastUpdated MonthlyUpdateInterval
06/01/06 1
06/01/06 2
06/01/06 3

If I run the query on 07/01/06 only the first document should report, on
08/01/06 only document 1 and 2 should report. On 09/01/06 all three
documents
should report. In theory it does not need to be any more complicated than
that because whenever I update a document I will change the LastUpdated
value
the current date and in time when I check the query it will come around
again.

Thank you

You can calculate the next review date with DateAdd function.

Field: ReviewDue: DateAdd("m",MonthlyUpdateInterval, LastUpdated)
Criteria: Between #6/1/06# and #6/30/06#