Using a Form to Feed the Criteria in a Query

[David]

I am trying to create a form that will serve to populate
the criteria in a query. I have about 10 fields listed on
the form. When I enter a value in the field and have the
following syntax in the query criteria (=forms!formname!
controlname) the query may or may not return a result
depending on the criteria.

Here is my problem. When I do not enter a value in the
control, it is assuming a null value? - and only return
null values. What I want to happen is that is the value
in the control is null, for the criteria to be nothing -
or Like "*", so that I get all of the records returned.
How can I do this?

Thanks in advance for your help.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


WHERE (Forms!FormName!ControlName Is Null OR ColumnName =
Forms!FormName!ControlName)
AND ... etc.

The expression in the parentheses will evaluate to a TRUE if the value
of the ControlName is NULL. This will pull all records. Otherewise, if
the ControlName is NOT NULL it's value will be compared to the value in
ColumnName.

Be sure to enclose the expression in parentheses for each control that
you are evaluating.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQSpZqoechKqOuFEgEQLzYACfcfQhF7rgSNwy2Piq1CfM5aLpb7gAoKiw
DC0MRHBdVFeTrGseAoSd7O44
=5MXi
-----END PGP SIGNATURE-----

 

[John Spencer (MVP)]

I think you will run into a "Too Complex" error with ten (10) different controls
supplying criteria.

If you have fields that you are trying to search and they do contain null
values, then the only way to really do this is to use VBA to build the SQL
string. On the other hand, if the field ALWAYS contains a value there are some
workarounds that can be used.


For TEXT fields:
Field: TextAlwaysWithValue
Criteria: Like NZ(Forms!YourFormName!YourControl,"*")

Date fields and number fields need a range specified.
Field: DateFieldAlwayswithValue
Criteria: Between NZ(Forms!YourFormName!YourDateControl,#1/1/1750#) and NZ(Forms!YourFormName!YourDateControl,#1/1/3750#)

Field: NumberFieldAlwaysWithValue
Criteria: Between NZ(Forms!YourFormName!YourNumberControl,-9999999999) and NZ(Forms!YourFormName!YourNumberControl,999999999)

I'll repeat myself and say, that the above only work if your fields never have a
null value or if you don't care to retrieve records that have null values. The
only other way I know that works reliably with that many criteria is to build
the SQL on the fly using VBA. With just a couple, than MGFoster's proposed
solution works well.