Setting up a date query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that has a field called "Type". It is a combo field that has
values: Revision and Review. Based on the selection, there is another field
in the table called DATE. So, there can be a selection called Review with an
associated DATE and a selection Revision with an associated DATE. What I
would like to do is setup a report based on a query that calculates the NEXT
DUE DATE. To determine the NEXT DUE DATE I need to take the LATEST date from
either REVISION or REVIEW and then calculate 12 months later for the NEXT DUE
DATE. Can anyone advise me on how best to do this? I've never dabbled in
VBS or VBA so I'd like to see if I can get this done with just the tools in
MS Access 2000. Thanks.
 
I have a table that has a field called "Type".
Click to expand...

That's a reserved word and a bad choice of fieldname.
It is a combo field that has
values: Revision and Review. Based on the selection, there is another field
in the table called DATE.
Click to expand...

That's another reserved word and also a bad choice of fieldname.
You'll need to ALWAYS enclose both these names in [square brackets] or
Access *will* get confused!
So, there can be a selection called Review with an
associated DATE and a selection Revision with an associated DATE. What I
would like to do is setup a report based on a query that calculates the NEXT
DUE DATE. To determine the NEXT DUE DATE I need to take the LATEST date from
either REVISION or REVIEW and then calculate 12 months later for the NEXT DUE
DATE. Can anyone advise me on how best to do this? I've never dabbled in
VBS or VBA so I'd like to see if I can get this done with just the tools in
MS Access 2000. Thanks.
Click to expand...

Neither VBA nor VBS (??) is needed: you can put a calculated
expression in a query. Note that a "combo field" is apparently an
instance of the dreaded and despised "Lookup Field" - the table does
NOT containwhat it appears to contain ("Revision" or "Review"), it
contains a concealed numeric link to the lookup table. I don't know
what that table might be named so I can't include it in a sample
query. Mixing in the lookup table makes my idea of a simple DMax()
function call problematic!

Instead, create a Query joining your main table to the lookup table.
Select the field that contains "Review"/"Revision" and put a criterion
on it of

IN("Review", "Revision")

to select just those two options. Select the [DATE] field and whatever
field you want to group by (I presume that this table contains
information about Projects or something similar and you want the
latest date for each project?)

Change the query to a Totals query by clicking the Greek Sigma icon
(looks like a sideways M). Group By this field; use WHERE as the
totals operator on the Type field; Group By the Project ID (whatever
it is); and select Max on the Date field. This will give the latest
review or revision date (whichever is later) for each project. Include
this Totals query in your report's recordsource query, joining by
ProjectID.

You can then use a textbox on your report with

=DateAdd("yyyy", 1, [MaxOfDate])

to calculate the next due date.

John W. Vinson[MVP]
 
many thanks for the advice. I'll change the field names so that they don't
confuse Access.
 
Back
Top