Query by Form

  • Thread starter Thread starter Bryan
  • Start date Start date
B

Bryan

I am using a search form with combo boxes (5 of them) to run a query. The
results of the query are displayed in a subform in the above mentioned form.

My question / problem is this:

By using the following criteria in each of the fields in my query (Like
[Forms]![Search]![Field1] & "*" Or Is Null) I am able to show all records
when no criteria is entered in the search form. I run into a problem when I
have fields that are blank (which is typical for some fields).

My result is either showing records that are null along with those I am
searching or not showing all records if I remove the & "*" Or Is Null
wording from my query.

Is there another way I should be doing this?

Thank you

Bryan
 
Bryan:

You are combining two different methods of making parameters optional here.
Using the Like operator is not foolproof, as you've found, because it does
not handle Nulls. Change the criteria to:

[Forms]![Search]![Field1] OR [Forms]![Search]![Field1] IS NULL

Ken Sheridan
Stafford, England
 
Thank you! That did it.

Ken Sheridan said:
Bryan:

You are combining two different methods of making parameters optional here.
Using the Like operator is not foolproof, as you've found, because it does
not handle Nulls. Change the criteria to:

[Forms]![Search]![Field1] OR [Forms]![Search]![Field1] IS NULL

Ken Sheridan
Stafford, England

Bryan said:
I am using a search form with combo boxes (5 of them) to run a query. The
results of the query are displayed in a subform in the above mentioned form.

My question / problem is this:

By using the following criteria in each of the fields in my query (Like
[Forms]![Search]![Field1] & "*" Or Is Null) I am able to show all records
when no criteria is entered in the search form. I run into a problem when I
have fields that are blank (which is typical for some fields).

My result is either showing records that are null along with those I am
searching or not showing all records if I remove the & "*" Or Is Null
wording from my query.

Is there another way I should be doing this?

Thank you

Bryan
Click to expand...
Click to expand...
 
I have run into a new problem. Now I cannot do a wildcard search with the
changes made.

I have played around with the query but I cannot figure it out.

Any suggestions?

Thanks,

Bryan

Ken Sheridan said:
Bryan:

You are combining two different methods of making parameters optional here.
Using the Like operator is not foolproof, as you've found, because it does
not handle Nulls. Change the criteria to:

[Forms]![Search]![Field1] OR [Forms]![Search]![Field1] IS NULL

Ken Sheridan
Stafford, England

Bryan said:
I am using a search form with combo boxes (5 of them) to run a query. The
results of the query are displayed in a subform in the above mentioned form.

My question / problem is this:

By using the following criteria in each of the fields in my query (Like
[Forms]![Search]![Field1] & "*" Or Is Null) I am able to show all records
when no criteria is entered in the search form. I run into a problem when I
have fields that are blank (which is typical for some fields).

My result is either showing records that are null along with those I am
searching or not showing all records if I remove the & "*" Or Is Null
wording from my query.

Is there another way I should be doing this?

Thank you

Bryan
Click to expand...
Click to expand...
 
Back
Top