Parameter query - use two dates for multiple fields?

[Guest]

I have a query that looks for 4 different dates (sort start, sort completion,
soft open, last day business). Rather than using between in the criteria,
I'd like to use parameters to have the user enter the general start and end
dates of the search range, then bring me every instance of the dates that
fall in the user-entered range for all four fields. What I DON'T want is for
the user to have to individually enter the parameters for each of the four
different dates I want to search. (I.e. put the same start and end dates 4
times) Also, not every field will contain a date for every record. Thanks
in advance!

 

[Allen Browne]

You can use the same parameter name under multiple date fields. The user
will be prompted once for each parameter.

It gets more messy to return rows where the dates are null. Basically, you
need to:

a) Declare the parameter (Parameter on Query menu), so Access knows its data
type (thus avoiding a bug where JET mishandles nulls with parameters of type
Text.)

b) Construct the WHERE clause so that it returns the records if the
parameter is null, e.g.:
WHERE (([WotDate] Is Null) OR ([MyField] >= [WotDate]))

This quickly gets very messy where you are trying to handle combinations of
AND and OR and Nulls. An alternative (and more efficient) approach is to
provide a form where the user can enter the limiting values, and build the
WHERE clause from only those boxes where the user entered something. You can
then use the result as the Filter of a form, or the WhereCondition of
OpenReport, or in the RecordSource of a form/report etc. For an example,
see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[Michel Walsh]

Not sure I really understand... but it may be that you can check the
parameter about being null (not entered by the user) and then, change it for
a default value:


.... WHERE ... StartingDate >= Nz(StartingDateParam, #01-01-1901#) AND
EndingDate <= Nz( EndingDateParam, Now() ) AND ....


Here, if nothing is entered in startingDate parameter, January First,1901,
will be taken, etc.


Vanderghast, Access MVP

 

[John Spencer]

A little more infomation requested.

Do you want a record returned if any of the four date fields matches the
criteria or do you want all four to match? From the comment about fields
having null values I would guess the former. The SQL statement would look
something like the following.

Parameters [Enter Period Start] DateTime, [Enter Period End] DateTime;
SELECT *
FROM [Your Table]
WHERE [Sort Start] Between [Enter Period Start] and [Enter Period End]
Or [Sort Completion] Between [Enter Period Start] and [Enter Period End]
Or [Soft Open] Between [Enter Period Start] and [Enter Period End]
Or [Last Day Business] Between [Enter Period Start] and [Enter Period End]

In the design view (query grid), you would enter the criteria on four
different rows

Field: Sort Start
Criteria(1): Between [Enter Period Start] and [Enter Period End]
Criteria(2):

Field: Sort Start
Criteria(1):
Criteria(2): Between [Enter Period Start] and [Enter Period End]

etcetera

As long as the parameter is EXACTLY the same you will only get asked one
time for each parameter. (I usually cut and paste to ensure that I don't
mistype.)

Also, to make sure that Access understands the data type I would declare the
parameters (Menu bar Query : Parameters ...), but that is optional in
Access for most queries.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

To attempt to clarify, I have 4 fields in a form: SortStart, SoftOpen,
LastDayBusiness and SortCompletion. These dates are used to generate a staff
schedule for a store opening, closure, remodel or relocation. I need to
create a report under each of these four headings showing the location of the
work, staff member assigned to it and the relevant dates and I want to enter
one range of dates to pull all work of all types within that range. (I.e.
all new stores, relocations, closures and remodels taking place between X and
Y) The null part is that each of the four types of dates is not necessarily
relevant to each scope of work. Openings don't use LastDayBusiness, Closures
don't use SoftOpen, etc...

Maybe I'm either just lucky or missing something, but using the advice of
putting in the same parameter for each date type in the query brought
everything up, regardless of nulls. Is it perhaps because all scopes of work
use SortStart (so it's never null) and that was the first date type listed in
the query?

 

[Michel Walsh]

Note that you should test the parameter, not the field value itself.

You may have an 'ugly' OR that kill your filtering condition, through
improper ( )-s imbrications. Can you post the SQL view of your query?



Vanderghast, Access MVP