Searching for Blank Text fields

[Jaxson]

Hi,

I have created a filter for my database. I have a combo box for the user to select what they want to filter the database by. Every option works except when the user wants to see the records with blanks in a certain field. Using debug.print, this is what the code looks like, do you see anything wrong? It doesn't return any results.

SELECT * FROM myTable WHERE ( [Field1l] = "" ) AND ( [Field2] = "L" ) AND ( [Field3] = "L" )

So the Field1 = "" is what's giving me problems. Searching for "L" in Field2 and "L" in Field3 alone is fine.

 

[pbaldy]

It may be Null, so try

Nz([Field1l], "") = ""

 

[datAdrenaline]

SELECT *
FROM myTable
WHERE ( [Field1] Is Null Or [Field1] = "") AND ( [Field2] = "L" ) AND ( [Field3] = "L" )

... Or ...

SELECT *
FROM myTable
WHERE (Len([Field1] & "") = 0) AND ( [Field2] = "L" ) AND ( [Field3] = "L" )

.......

The basic premise is that you need to check for Null AND a ZLS ... if your field is marked in table design to NOT Allow Zero Length, then a test for Null is sufficient.

 

[Jaxson]

I just discovered that the search turns up for the blanks as long as I set the database default value for the text fields to "". However because I am importing this table from excel, I dont know how to apply the default values to the imported data. Im not sure how the blank cells are interpreted. It seems like it only lets me apply the new table settings to newly created records. Any suggestions? Thanks

 

[datAdrenaline]

I would not set the default of text values to "" simply because the leads to ambiguity that can easily become a bear to manage! ... As a matter of fact, I set up my text fields to NOT Allow Zero Length strings!!! ...

Both paul and myself have indicated valid methods to search for "blanks". Blanks can be in the form of a Null or a Zero Length String (please note that a Null and a ZLS are NOT the same thing!!).

With Pauls method, he coerces Nulls to a ZLS, with the implication to then filter as you are ...

With my sugguestion, I test for either a ZLS OR a Null value for the field in question...

Both *should* filter out your "blanks".

...

Also, another factor that you have not exposed to us is HOW you create your filter, I assume you have a form, then create a literal SQL statment that retreives your data... but the way you form your AND's and OR's can have an effect that you did not expect.

 

[Jaxson]

datAdrenaline, i want to thank you for your help. I highly appreciate it. This WHERE ( [Field1] Is Null Or [Field1] = "") AND ( [Field2] = "L" ) AND ( [Field3] = "L" ) statement worked perfectly.

 

[datAdrenaline]

Excellent News Jaxson!! ... I am glad you got things sorted!....