Unanswered: Help with parametrization queries and null values
I'm buiding a small application that searches an access database for addresses. The user uses text boxes to search on various aspects of the address (street name, town name, post code etc). The text boxes all act as wildcards and a cmd button triggers a sql query that populates a datagrid with the results.
This is working fine during testing of a sample database I made, but now I've transferred the same techniques to the actual database (1 table, 28,000 records) and I'm getting some funny results. Basically the searches are not returning any records where there are null values. This cannot happen, as some address records do not have certain fields populated.
I am building the sql queries using the query builder, like this:
WHERE ADDRESS_REF LIKE '%' & ? & '%' AND HOUSE_NUMBER LIKE '%' & ? & '%' AND STREET_NAME LIKE '%' & ? & '%' AND TOWN_NAME LIKE '%' & ? & '%'
The idea is that the user enters as much as he/she knows into as many text boxes as they want, leaving any null if they wish, and the applicable results populate.
An example of where it is going wrong is as follows. I know for a fact there are about 80 records in the database for 'Booth Street'. However, typing 'booth' into the street_name text box yields only 30 results. The other 50 have missing/null fields of varying types. Some no post code, some no House Number etc.
I've tried altering the sql code by putting 'Or HOUSE_NUMBER IS NULL' etc, but this doesn't work. I've checked the schema and all seems ok, all fields and type string, and are allowed to have null entries.
It's really annoying me now and I'm gutted I cannot get to the bottom of it.