Query by Form

[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

 

[Ken Sheridan]

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]

Thank you! That did it.

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

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]

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

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

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