Wildcard not returning blank fields

[Solola]

I swear I've never had this problem before, but now I am (maybe I was just never previously aware that I wasn't getting the data I thought I was getting):

I have a query that prompts the user to enter text to be used as criteria: Like "*" & [Enter Supplier Name] & "*"

I used to think that if I did not enter anything in the prompt, it would return all records. However, it is not returning any rows for which the Supplier field is blank. Why not?

To get it to return 1) what is entered at the prompt, or 2) all fields if nothing is entered, how should I change my criteria?

Thanks!!

 

[SydneyGeek]

Try:
Like "*" & [Enter Supplier Name] & "*" OR Like "*"

Denis

 

[Solola]

Thanks for the reply, but nope - that still doesn't work. It seems that using the "*" tells Access that there should be something in the field in order to be returned. So blanks are still excluded. Any other ideas?

(To make matters more complicated, I'd prefer that if the user does enter a supplier name at the prompt, the blanks are excluded, b/c the user wants to see only the supplier he/she entered. I only want to see blanks if the user does not enter a supplier name - then I want to see all rows.)

 

[Trevor G]

Thanks for the reply, but nope - that still doesn't work. It seems that using the "*" tells Access that there should be something in the field in order to be returned. So blanks are still excluded. Any other ideas?

(To make matters more complicated, I'd prefer that if the user does enter a supplier name at the prompt, the blanks are excluded, b/c the user wants to see only the supplier he/she entered. I only want to see blanks if the user does not enter a supplier name - then I want to see all rows.)

Why not use a form and set the criteria to the form element. Like "Form Name, Text Box Name " &"*"

Trevor

 

[Joe4]

There is a write-up here on dealing with Nulls:
http://support.microsoft.com/kb/209261

 

[Solola]

Trevor - I think what you're suggesting is actually what I'm doing. (I simplified my problem for purposes of posting the question on MrExcel.) I am using a form, which has unbound entry boxes for 4 different fields of my data. A query is pulling from the form to determine what should be used as criteria.

The actual criteria (now we're getting detailed and specific) in my query is: Like "*" & [Forms]![frmMain].[SuppCust1] & "*"

[SuppCust1] is the name of the unbound text box on my form, into which the user enters the criteria he/she wants.

However, I think the same issue applies. When a box on the form is left blank, if that field contains any null values in my underlying table, the record is not retreived.

Joe - I'll take a look at the link you sent. Thanks! Hopefully it helps me...

 

[nstevox]

Hi, Did you ever find a solution for this? I am having the same issue

 

[Micron]

Wow, this is an oldie.
A wild card won't find Null and probably won't find empty strings either. Solution could be

Like "*" & [Enter Supplier Name] & "*" OR Is Null

I might test for empty strings (zls) in those fields first if the data is coming from another source, such as Excel and if the fields were created with "allow zero length string" default property. Simply create a test query that uses "" as criteria in those fields. If any records are returned, the solution is likely

Like "*" & [Enter Supplier Name] & "*" OR Is Null OR = ""

That is because Is Null won't find zls ("") but both fields would look the same.

 

[JonXL]

Access has the Nz() function for this. Use it to convert Null fields to a string (can be a space if not wanting zero-length). The trick is to put it around the field nam in the criteria expression and not the criteria. For example:

WHERE Nz([supllierNameField]," ") LIKE "*" & [Enter Supplier Name] & "*"

 

[nstevox]

Thank you both, but unfortunately no joy with either of those.