Query results based on fields in a form.

  • Thread starter Thread starter Bretona10
  • Start date Start date
B

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,
 
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
 
Back
Top