This query is killing me - IsNull problem in IIf argument

[wilkob]

I am trying to clean up and organize a database through a series of queries.

First I have a maketable query that creates a local table
Then I have some update queries that clean up the local table and put parts of a long line of text in a column over various columns

Finally I have a select query on my local table which puts the values for some of these values in a neat way in a column called Internal text.

I have created an expression:

Internal Text: IIf(IsNull([SPEC0]);"";"Spec : " & [SPEC0] & " ")

Now I am completely pulling my hair out over the following:

When I run the query the field Internal Text shows by default Spec : even though the field SPEC0 is empty. The record type is text, but if I am not misstaken the IsNull should also work on text fields
I have also tried the IsEmpty function and this also did not work

I have created a brand new table to test my formula on and there it works

It seems that although the field look empty Access does not recognize this

PLEASE HELP !

 

[khawar]

Use this
Internal Text: IIf(IsNull([SPEC0]),"","Spec : " & [SPEC0] & " ")

 

[boblarson]

If it is indeed null then IsNull will deal with it. However, if it is an empty string, it will not. So it could actually look blank but it be an empty string and not null. To test for both null and empty string use something like this:

IIf (Len(Nz([YourFieldName],"") & ""), put whatever if true here, put whatever if false here)

 

[wilkob]

If it is indeed null then IsNull will deal with it. However, if it is an empty string, it will not. So it could actually look blank but it be an empty string and not null. To test for both null and empty string use something like this:

IIf (Len(Nz([YourFieldName],"") & ""), put whatever if true here, put whatever if false here)

WORKS LIKE A CHARM !!!!

many thanks

 

[boblarson]

Glad we could help.