wild cards and null

[tc3of4]

front end: Access XP
backend: Oracle 8i

Question: Using running a query which has a criteria that is populated by a textbox from a form.

this is the easy part.

My confusion is when a user enters a wildcard "*"

the query looks somewhat like this

Like form![field]
so if they enter a wild card the query will still work.

my problem is that Like * will not validate null values. So how do I add a check for this....

i wrote this
Like iif(form![field]="*", "*" or Is null, form![field])

but when I went to check out the sql it looks like this

Like iif(form![field]="*", form![field]like "*" or form![field] is null, form![field])

can anybody tell me whats wrong (unless it is correct)

tc3of4

 

[RV]

Use this kind of syntaxis in your WHERE clause:

...Where whateveryourcolumn LIKE form![field]
OR form![field] Is Null;

RV

 

[spalmateer]

Is there a way to modify that for multiple fields? I've tried using AND after the first where statement but it doesn't work. For example if the first value is null, its showing all records and doesn't filter for the second criteria.

[This message has been edited by spalmateer (edited 04-23-2002).]

 

[spalmateer]

Thanks Pat! That worked perfectly!