Is Null

  • Thread starter Thread starter France
  • Start date Start date
F

France

I am having a problem with a query criteria. I am trying to retrieve null
records if a field called txtCurrentCity on a form is null. The criteria
works fine if the txtCurrentCity has a name in it. However, when the field
is null it is not returning the null records.

Here is the criteria
IIf(IsNull([Forms]![FrmTopClients]![txtCurrentCity]),IsNull([QryAllOfficesContacts]![City]),[Forms]![FrmTopClients]![txtCurrentCity])
 
hi France,

I am having a problem with a query criteria. I am trying to retrieve null
records if a field called txtCurrentCity on a form is null. The criteria
works fine if the txtCurrentCity has a name in it. However, when the field
is null it is not returning the null records.
Click to expand...
I assume that you have records where the value is not null, but an empty
string, which in fact is a big difference.
Here is the criteria:
IIf(IsNull([Forms]![FrmTopClients]![txtCurrentCity]),IsNull([QryAllOfficesContacts]![City]),[Forms]![FrmTopClients]![txtCurrentCity])
Click to expand...
So try this condition in the SQL view:

WHERE
(
IsNull([Forms]![FrmTopClients]![txtCurrentCity])
AND
Len(Trim(Nz([QryAllOfficesContacts]![City],"")))=0
)
OR
[Forms]![FrmTopClients]![txtCurrentCity]=[QryAllOfficesContacts]![City]





mfG
--> stefan <--
 
Thank you so much. That worked perfectly.
--
Thank you and have a great day!
France


Stefan Hoffmann said:
hi France,

I am having a problem with a query criteria. I am trying to retrieve null
records if a field called txtCurrentCity on a form is null. The criteria
works fine if the txtCurrentCity has a name in it. However, when the field
is null it is not returning the null records.
Click to expand...
I assume that you have records where the value is not null, but an empty
string, which in fact is a big difference.
Here is the criteria:
IIf(IsNull([Forms]![FrmTopClients]![txtCurrentCity]),IsNull([QryAllOfficesContacts]![City]),[Forms]![FrmTopClients]![txtCurrentCity])
Click to expand...
So try this condition in the SQL view:

WHERE
(
IsNull([Forms]![FrmTopClients]![txtCurrentCity])
AND
Len(Trim(Nz([QryAllOfficesContacts]![City],"")))=0
)
OR
[Forms]![FrmTopClients]![txtCurrentCity]=[QryAllOfficesContacts]![City]





mfG
--> stefan <--
.
Click to expand...
 
Back
Top