Query Criteria Null Values

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Hi Folks - I created an unbound form with two text boxes. The text boxes are
used to choose criteria for two fields in a query. I then pass the textbox
values to the query via the Criteria row. Here's my problem: If I set
criteria for both fields, there is no problen. If I leave one field blank,
then the query does not run properly. So I built the following criteria
string: Is Null Or Like [Forms]![frmGenericRpt]![txtLevel] & "*".

Now the problem is if I enter a criteria in the form text box, it returns a
query result with the criteria OR the nulls.

So, if there is criteria in the text box, then the query should display just
the matching records. If there is NOT any criteria in the text box, then it
should return all blanks and nulls. Do-able?

Thanks,

Michael
 
If [Forms]![frmGenericRpt]![txtLevel] is blank do you want ALL records
returned or do you want only the NULL records returned?

If ALL records should be returned, enter the following criteria

Field: YourField
Criteria: =[Forms]![frmGenericRpt]![txtLevel] OR
[Forms]![frmGenericRpt]![txtLevel] Is Null

If only records with a null value should be returned when the control is
blank then
Field: YourField
Criteria: =[Forms]![frmGenericRpt]![txtLevel] OR
([Forms]![frmGenericRpt]![txtLevel] Is Null and Is Null)
 
Back
Top