Bill:
Both Dirk, in your other thread, and I, in this one, have given you the
standard method for doing this. Have you tried it?
For each parameter which you want to be optional you test for the value in
the column matching the parameter Or the parameter being Null. So if you
have a parameter of:
Forms![MyForm]![1st Number]
as the parameter in the criteria row of the 1st Number column in query design
view you change it to:
Forms![MyForm]![1st Number] Or Forms![MyForm]![1st Number] Is Null
Do the same for the other columns. The logic is easier to understand if the
query is written out as SQL:
PARAMETERS
Forms![MyForm]![Startdate] DATETIME,
Forms![MyForm]![Enddate] DATETIME;
SELECT *
FROM [MyTable]
WHERE
([Date] BETWEEN Forms![MyForm]![Startdate]
AND Forms![MyForm]![Enddate])
AND ([1st Number] = Forms![MyForm]![1st Number]
OR Forms![MyForm]![1st Number] IS NULL)
AND ([2nd Number] = Forms![MyForm]![2nd Number]
OR Forms![MyForm]![2nd Number] IS NULL)
AND ([3rd Number] = Forms![MyForm]![3rd Number]
OR Forms![MyForm]![3rd Number] IS NULL);
Remember that all a query's WHERE clause has to do is to evaluate to TRUE or
FALSE for each row in the table; if it evaluates to TRUE the row is returned,
if it evaluates to FALSE the row is not returned.
If you examine the above SQL statement you'll see that each part of the WHERE
clause is wrapped in parentheses, e.g.
([1st Number] = Forms![MyForm]![1st Number]
OR Forms![MyForm]![1st Number] IS NULL)
and that each of these parenthesised expressions is tacked together with an
AND. By wrapping these expressions in parentheses it forces each one to
evaluate independently of the complete expression for the WHERE clause as a
whole. So if in the form you enter a value in the 1st Number control the
parenthesised expression above will evaluate to TRUE for every row where the
value in the 1st Number column matches the value you entered in the form.
This is because the parenthesised expression is an OR operation. An OR
operation evaluates to TRUE if one or both of the expressions either side of
the OR evaluates to TRUE.
If on the other hand you leave the 1st Number control on the form blank (Null)
then the second part of the parenthesised expression will evaluate to TRUE
for every row in the table. This because all it asks is 'is the control Null?
', so whatever is in the 1st Number column in the row in the table is
immaterial. So the parenthesised expression evaluates to TRUE because one of
the expressions either side of the OR evaluates to TRUE.
An AND operation on the other hand requires that all of the expressions which
are tacked together with the ANDs must be TRUE for the expression as a whole
to evaluate to TRUE. So the whole of the WHERE clause will evaluate to TRUE
only where every one of the parenthesised expressions evaluates to TRUE.
Lets say that in the form you enter the dates for the date range, leave 1st
Number blank, and enter values in 2nd Number and 3rd Number. The WHERE
clause as a whole will evaluate TRUE for those rows where:
(a) the Date column's value is within the data range.
(b) the 1st Number column contains any value, or none.
(c) the 2nd Number column contains a value which matches the one you entered
in the form.
(d) the 3rd Number column contains a value which matches the one you entered
in the form.
Only the rows which satisfy every one of these will be returned.
You can build a query equivalent to the one shown above in SQL simply by
entering, as one line, Forms![MyForm]![1st Number] Or Forms![MyForm]![1st
Number] Is Null in the criteria row of the Ist Number column, and similarly
for the 2nd and 3rd Number columns. You don't have to parenthesise each of
these in design view. Access will build the SQL as necessary for you. In
fact Access will move things around quite a lot and throw in a lot of extra
parentheses. The query will work just the same as the underlying logic
remains unaltered, but it will be less obvious that if its written directly
in SQL as above. It would also be a lot more tricky to amend if you want to
add more criteria for instance. For this reason most of us would write
something like this in SQL and save it in SQL view so that Access doesn't
move things around as it would if we switched to design view. But if you are
not comfortable with writing SQL do it in design view as I described.
You'll have noticed that I've declared the date/time parameters in the query.
Its always a good idea to declare date/time parameters in particular as
otherwise a value entered in the form in short date format could be
misinterpreted as an arithmetical expression and give the wrong results.
BTW, not directly connected with this issue, but I see you've used Date as a
column name. This is best avoided as Date is the name of a built in function
so could in some circumstances cause confusion and give the wrong results.
Always use a specifically descriptive column name such as GameDate.
Ken Sheridan
Stafford, England
Daryl,
Thanks for the help. This is what I have.
Table with 5 columns: Date-Game-1st Number, 2nd Number and 3rd Number.
My Query form has Text Boxes for: Start Date, End Date, 1st Number, 2nd
Number and 3rd Number. Plus a Command button to run the Query.
What I want is if I leave say 1st Number Blank and fill in the 2nd & 3rd
Number. It should show all results for my date range where the 2nd Number and
3rd Number equal the inputted text box. Most everything I tried came back
with no results or a message saying I cancelled the operation.
Thanks,
Bill
[quoted text clipped - 39 lines]
