wild cards and null (1 Viewer)

[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).]

 

[Pat Hartman]

You need to suround the various clauses of your selection criteria with parenthese so that Access can correctly interpret your intent.

Where (YourField1 Like Forms!YourForm!YourField1 OR Forms!YourForm!YourField1 Is Null) AND (YourField2 Like Forms!YourForm!YourField2 OR Forms!YourForm!YourField2 Is Null) AND (YourField3 Like Forms!YourForm!YourField3 OR Forms!YourForm!YourField3 Is Null);

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

 

[spalmateer]

Thanks Pat! That worked perfectly!