SQL to select all records, including blanks

[garywood84]

I have a form with code behind it to generate a query in SQL. The generated SQL is like this:

SELECT * FROM bqryVolunteers WHERE bqryVolunteers.[Organisation] Like '*' AND bqryVolunteers.[Town/City] Like '*' AND bqryVolunteers.[County] Like '*';

This works fine, but only if NONE of the fields (Organisation, Town/City, County) contains a blank field. If there are blanks, then the query returns no records.

I managed to get around this by modifying the code so that the SQL string generated has WHERE bqryVolunteers.[Organisation] Like '*' or "". However, this doesn't work satisfactorily because when I then have a criteria for a field, I still get all the records returned, i.e.:

SELECT * FROM bqryVolunteers WHERE bqryVolunteers.Organisation] = "AnyCompany" AND bqryVolunteers.[Town/City] Like '*' or "" AND bqryVolunteers/[County] Like '"' or "";

returns all the records as if no Organisation had been specified.

Please can someone advise me how I can make this work correctly (i.e. in the case of this example, I should see records for all volunteers from "AnyCompany" regardless of what town/city or county they are from, even if they are blank.

Thanks in advance,

Gary

 

[ejstefl]

The problem is that your records might contain nulls as well, not just zero length strings (""). Try setting your criteria to be:

Like "*" or "" or Is Null

That should take care of it.

 

[RV]

Gary,

what you're actually saying is that this is what you need:

SELECT * 
FROM bqryVolunteers 
WHERE bqryVolunteers.[Organisation] Is Not Null;

RV