Is Null

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

 

[Stefan Hoffmann]

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.

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

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 <--

 

[France]

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

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.

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

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 <--
.