Query results based on fields in a form.

[Bretona10]

Hi all, I have a query that has selection criteria based on 3 fields in a
form. The problem is that when any one or two of them are not populated, the
query returns with no records. I believe its a null or zero length issue.
Does anyone have code or a method I can use in the query criteria to ignore
the blank form fields and only evaluate on the fields filled in on the form?

Thank you,

 

[KARL DEWEY]

Like [Forms]![YourForm]![TextBox] & "*"

 

[John Spencer]

If the fields are text fields and are always populated with a value you could use
LIKE Nz(Forms![FormName]![NameOfControl],"*")

If the fields are numeric and always populated with a value you could use
Between Nz(Forms![FormName]![NameOfControl],-9999999999) and
Nz(Forms![FormName]![NameOfControl],9999999999)

You can do similar things with dates that are always populated
Between Nz(Forms![FormName]![NameOfControl],#1900/01/01#) and
Nz(Forms![FormName]![NameOfControl],#3999/12/31#)

IF the fields can ever be null, you can enter something like this as the
criteria for each field
Forms![FormName]![NameOfControl] or Forms![FormName]![NameOfControl] is Null

When you close the query, Access will reorganize the query criteria into a
different format, but the query should still work. Warning: This method only
works when you have a limited number of criteria. If you try to do this with
too many criteria you will end up with a query too complex error.




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