Records with null contents fail query

[John Gilchrist]

I created a query to exclude certain records

I placed a criteria on Field1

not like "*foreign*"

This query excluded all records which contained the text string "foreign" in
Field1, which is what I wanted.

This query also excluded all records for which Field1 was null - I did not
want this results.

My workaround was to require some default content for Field1.

Is there any way to set up a query such that null fields do not "fall out"
of the query??

Thanks,
John

 

[Sergey Poberezovskiy]

Simple:

IsNull(Field1, "aaa") not like "*foreign*"

HTH

 

[tina]

try

Not Like "*foreign*" Or Is Null

hth

 

[Douglas J. Steele]

The IsNull function you've posted is the SQL Server version, not the Access
one. In Access, IsNull doesn't take a second argument. The equivalent in
Access would be

Nz(Field1, "aaa") not like "*foreign*"

On the other hand, he could use

(Field1 & "") not like "*foreign*"

(or Tina's suggestion)

 

[Jamie Collins]

The IsNull function you've posted is the SQL Server version, not the Access
one. In Access, IsNull doesn't take a second argument. The equivalent in
Access would be

Nz(Field1, "aaa") not like "*foreign*"

On the other hand, he could use

(Field1 & "") not like "*foreign*"

(or Tina's suggestion)

What a great example of why to avoid proprietary syntax if possible. Nz
may only be used in the MS Access UI i.e. is not native Jet syntax.
Better to go with Tina's because it uses standard SQL and is portable
between Jet, MS Access, SQL Server and everywhere else.

Jamie.

--