start date for query

[Scott]

I have two tables that I want to use in a query, or combination of
queries. One is an attendance table, the other is a corrective action
table. The start date for the quesry will always be 6 months prior,
unless one of the representatives has an active write up in the
corrective action table. If they do have a corrective action write up,
then i want to use the date of the corrective action as the start date
for that rep, not the 6 months prior (default). How would this be
done?

Thanks-

Scott

 

[tina]

you might try using a DLookup() function that searches the corrective action
table, something along the lines of

=Nz(DLookup("DateFieldName", "CorrectiveActionTable", "ActiveField = Yes

And Rep = SomeIDField"), DateAdd("m", -6, Date())

suggest you read up on the DLookup() function, so you understand how to
write the criteria, and also read up on the NZ() function and the Date()
function if you're not already familiar with them.

hth

 

[Arvin Meyer [MVP]]

Queries will only return both criteria in an Or criteria situation. You can
do what you want, but you must write it in code, or create 2 queries and use
code to decide which one will be the recordsource for the form or report.
The conditions require that you look for the corrective action first and if
the resulting dataset is null, then look for the default criteria. I would
use code to look in a recordset for the corrective action. If the
recordset.recordcount was 0, then I'd build a second recordset and use it as
my source.

 

[John W. Vinson]

I have two tables that I want to use in a query, or combination of
queries. One is an attendance table, the other is a corrective action
table. The start date for the quesry will always be 6 months prior,
unless one of the representatives has an active write up in the
corrective action table. If they do have a corrective action write up,
then i want to use the date of the corrective action as the start date
for that rep, not the 6 months prior (default). How would this be
done?

Thanks-

Scott

Since we don't know anything about the structure of your tables, where the
corrective action writeup date might be found for instance; and since you
don't say six months prior to *what*; all I can suggest is that you use the
NZ() function somehow.

John W. Vinson [MVP]