Search Query in a Form

[rhernand]

I have a Search Query based on a Form. It does not retrieve any columns from the table if any column is empty. Why? I thought the & "*" would find all records.

SELECT DISTINCT LITE.OWNER, LITE.LAMP_SUFF, LITE.LAMP_NUMBER, LITE.TOWN_CODE, LITE.ACCT_NO, LITE.STR_NAME, LITE.STR_DESC, LITE.WATTS_LITE, LITE.LUMENS
FROM LITE
WHERE (((LITE.OWNER) Like [Forms]![frm_search]![owner] & "*") AND ((LITE.LAMP_SUFF) Like [Forms]![frm_search]![lamp_suff] & "*") AND ((LITE.LAMP_NUMBER) Like [Forms]![frm_search]![lampno] & "*") AND ((LITE.TOWN_CODE) Like [Forms]![frm_search]![town] & "*") AND ((LITE.ACCT_NO) Like [Forms]![frm_search]![accountno] & "*") AND ((LITE.STR_NAME) Like [Forms]![frm_search]![strname] & "*") AND ((LITE.STR_DESC) Like [Forms]![frm_search]![location] & "*") AND ((LITE.WATTS_LITE) Like [Forms]![frm_search]![watts] & "*") AND ((LITE.LUMENS) Like [Forms]![frm_search]![lumens] & "*"));

 

[EMP]

I have a Search Query based on a Form. It does not retrieve any columns from the table if any column is empty. Why? I thought the & "*" would find all records.

Take a look at Note (2) in this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

^

 

[rhernand]

oooooooooooohhhhhhhhhh

I now understand that I can not have empty columns in a table for a search. I have put a default of "Unknown" on all text fields and 0 for all numeric fields.

Thanks

 

[EMP]

I now understand that I can not have empty columns in a table for a search.

Actually you can if you follow Jon's Basic Criteria Format in the link. Default values and Null serve different purposes.

^

 

[rhernand]

I problem is the table data. Not all the columns I am serching on have data(empty). This is Jon's last statement:

"Unfortunately, the Like operator cannot return Null values. So when the text box or combo box is left blank, it fails to return all the records if the field happens to contain Null values."

I have gone back to the table and populated all Null columns with "Unknown" or 0. And force the same for all new Inserted fields. This is only for the columns I use in the Search.