[AccessD] How to do Like and is null in where clause

Hi John,
Yes it's annoying that a combo in Access doesn’t distinguish an empty string from a Null.
Another way would be to base the combo on a query using an IIF([status codes]="";"N/A";[status codes]) - and then pass "" to the method, if the combo's value is N/A. This will have the same effect as your solution but it will also distinguish an empty string from a Null which might be wanted in some cases.
Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af jwcolby
Sendt: 28. oktober 2010 21:58
Til: Access Developers discussion and problem solving
Emne: [AccessD] How to do Like and is null in where clause

I have a query where I need to select sets of data.  I am looking at existing data in a field, 
status codes, which I display in a combo so that the user can select sets of records based on those 
codes.  I basically pass the combo box value into the where clause (through a fltr() method) and 
back come the filtered results.  In order to handle the "*" (all) I use a like Fltr().

The problem is that if the value in the status is an empty string, the combo returns a null.  Null 
and Like are mutually exclusive.

I finally got around this by testing the combo in the after update of the combo and if the combo 
value is null, passing a "" to fltr() and thus into the where of the query.

I'm just wondering if anyone has found another way to do this.

-- 
John W. Colby
www.ColbyConsulting.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com