NULL vs. Empty String

[obiron]

Hi guys,



I have linked tables to an SQL2000 database. using Access 2007.

I have a query that links CONTACTS to STAFF on a left join (staff_ID) and to INTERFACE on a left join (on CONTACTS.team and STAFF.Specialty)
The staff record SHOULD always exist but Specialty may contain a string, NULL or be an empty string - I can't modify this data as it comes from an external system.

I may have two records in the Interface table, one for Team = "XXX" and Specialty = "" and one for Team = "XXX" and specialty = NULL.

The filter criteria are coming from a form which has the fields bound to the the INTERFACE records. I am trying to show the CONTACT records that match the INTERFACE record currently selected.

I can't work out how to sucessfully test for the difference between null and empty string. Both isNULL() and NZ() seem to return true for both conditions.


TIA

Obiron

[rpeare]

Where (((Team) = "XXX") AND ((Speciality) = "" OR isnull(Speciality)))

[obiron]

Good try, but that will return records that match both interface lines because Access does not make a distinction between = "" and NULL. I only want to return records that only match ONE interface line. I need to be able to determine whether the form field is NULL or "" and then only return records from the query that match that form value.

[rpeare]

so you are attempting to use "" to test your string if a record exists in the speciality and NULL to determine whether there is no specialty record at all?

If so I'd probably use
not isnull(specialtyID), isnull(specialtyname)
for the staff with a specialty record but no specialty recorded
and
isnull(specialtyID) for the staff with no specialty record at all