Empty fields & Query criteria

[apachee]

OK, my problem is:

I have a search query.

If I don't use criteria then it works fine. It displays all data stored in table. Even those rows where some columns are empty. I use left join, of course.

But when I use asteriks as criteria, those rows where column on which criteria is used is empty, doesn't display!

Anyone?

 

[Mile-O]

With what data type are you using asterisks? Text, Numeric, Date/Time, etc...

 

[Len Boorman]

Basically * matches any number of characters. Now when you use this with other characters e.g. sear* then it will find all words beginning sear with any number of additional characters including I believe no additional characters but when used on its own it behaves differently.

Also an empty field is Null which is again a slightly different case.

Try searching with * or Is Null

L

 

[apachee]

Try searching with * or Is Null L

Yes, but then if search criteria will be any other word such as "abcdef", then the output will be those records where columns contains "abcdef" with empty ones, which isn't correct.

I need this to work with any search criteria! If user types any string in text box then it works fine. The problem is that when in leaves default value which is "*", then those records that are empty are left out from query. But that is not exeptable for the DB that I am working.

If criteria is "*", then for me the empty fields fall into this criteria, do you agree?

ps. The data type is both, text & number!

 

[Mile-O]

As the criteria where [PARAM] is the parameter:

[PARAM] OR [PARAM] Is Null

 

[Len Boorman]

I think there should be some consideration regarding the field you are searching.

If it is going to be a search field then do not allow Nulls. Put in a default

This way you do not have to handle Null situations.

L

 

[Pat Hartman]

If criteria is "*", then for me the empty fields fall into this criteria, do you agree?

- No, that is not the way nulls work.

You could change the table so that the default value is "" (zero-length string) rather than null. You would also need to update your existing data to change the null values to zero-length strings. I don't recommend this solution because it has its own problems. You are better off learning how to work with null values.