How to change the criteria of a Query from the VBA?

[Sony170]

I have a database consisting of one table with 5 columns, called Name, Zip Code, Model, Serial #, and Reference #

I made a form with 5 text boxes (generically named Field 1 through 5), and then I built a query. I put all five of the table's fields in the query. In the criteria fields of those five columns, I have

Criteria: Like "*" & [Forms]![Search_form]![Field1] & "*"
Criteria: Like "*" & [Forms]![Search_form]![Field2] & "*"
Criteria: Like "*" & [Forms]![Search_form]![Field3] & "*"
Criteria: Like "*" & [Forms]![Search_form]![Field4] & "*"
Criteria: Like "*" & [Forms]![Search_form]![Field5] & "*"

However, here in lies the problem:
Some of the records don't have a reference field included, and others don't have serial numbers, and etc. Basically, some records have blank fields.

So to find all of "John"s records, we would put John in Field1, and then run the query.

The first criteria is now looking for *John*, which is correct (it might find Jack John, Johnny, John John, or anything else with j o h n in it).

The rest of the criteria are now looking for **.

However, what about the records with blank fields?

John ; _____ ; SDMS71 ; 1231234 ; REF9001 will NOT make it into the query, because that blank field for some reason doesn't meet the ** wildcard!

how can I accomodate this "blank field" problem? I want those records with blank fields to still be included in the query, because they still belong to "John", and that's what i wanted to search for!

 

[QTNM]

Search Criteria

What is your search criteria ??

Or, do you want to be able to search on all of the above ??

QTNM

 

[Sony170]

This is my search criteria:

John, blank, blank, blank, blank

I want it to return the following records:
1: JJohn , data, data, data, data
2: johnny, data, data, data, data
3: john, data, data, data, data
4: john, blank, blank, blank, blank

for some reason, it is not returning Record 4.

I posted what the query criteria is above; why won't Record 4 return?

 

[RuralGuy]

Define separate fields in your query using the Nz() function.

ie: Search1: Nz([FirstField],0)

Then search on your SearchN fields instead.

I believe your "blank" is really a Null and Null is nothing! Hopefully you are not really using spaces or "#" in your field names. Both will give you unexpected grief some day. Better to use CamelFontNames or Under_Score_Names.

 

[Sony170]

that's SICK, it works!!!! ty

 

[RuralGuy]

yw I think! Glad I could help.

 

[QTNM]

Define separate fields in your query using the Nz() function.

ie: Search1: Nz([FirstField],0)

Then search on your SearchN fields instead.

What does the Nz() function do?? Just curious ... I've been workin' with Access for about 5 years and I have never come across this function. Interesting .... I will DEFINATELY be utilizing this function in the near future.

I have a work around ... but CHEERS to the Nz() function !!!!!

QT

 

[RuralGuy]

It returns whatever you desire (usually zero) if the field is Null.

 

[QTNM]

Good stuff ...

As they say ... leave something new everyday!!

Thanks !
QT