Records with null contents fail query

  • Thread starter Thread starter John Gilchrist
  • Start date Start date
J

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
 
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)
 
Douglas said:
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)
Click to expand...

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.

--
 
Back
Top