multifield perameter search

[freelancer]

I have a table in which there is a field which shows the date that specific
jobs were completed, (let’s call that the Primary date field). I also have
three fields in which we are able to insert an up-dated date (1st, 2nd, and
3rd updates), when the job is raised again months or years down the line.
I would like to search all jobs opened after say 1 Nov 2008, using a
parameter field search, (i.e. click search , parameter field opens asking
‘please insert start date’) searching all date fields, (Primary, 1st, 2nd,
and 3rd updates) but can’t figure out how to search all date fields from one
parameter search. Any ideas? All responses will be greatly appreciated.

 

[Brian]

It is not clear from your question how you are doing the search. Presumably,
though, you are using a query with the parameter.

All you need is multiple OR operators. In SQL it would look something like
this:

SELECT * From Jobs WHERE PrimaryDate >= [please insert start date] OR
FirstUpdate >= [please insert start date] OR SecondUpdate >= [please insert
start date] OR ThirdUpdate >= [please insert start date]

You can Use the query builder to do this. Create a query that selects * (or
appropriate fields) from your Jobs table. Add all four date fields to the
query. Uncheck the Show box for each one if you do not need to see them in
the results. In the uppermost criteria box for PrimaryDate, enter this:

=[please insert start date]. Under FirstUpdate, place the criteria one line

lower, in the "or:" row. Under SecondUpdate, place it one line lower, and for
ThirdUpdate, yet one line lower.

This will return all records where any of those dates is on or after the
parameter date.

 

[John Spencer]

You would need to use the exact same parameter and stairstep it in the
criteria so that it was on a different criteria line (in the design view)
under each of your date fields.

Field: PrimaryDate
Criteria (1): > [Enter Start Date]
Criteria (2):
Criteria (3):
Criteria (4):

Field: Update1
Criteria (1):
Criteria (2): > [Enter Start Date]
Criteria (3):
Criteria (4):

Field: Update2
Criteria (1):
Criteria (2):
Criteria (3): > [Enter Start Date]
Criteria (4):

Field: Update3
Criteria (1):
Criteria (2):
Criteria (3):
Criteria (4): > [Enter Start Date]

As long as the prompt is exactly the same, you will only be asked for its
value one time.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County