lankapala
asked on
SQL Nulll Query
please find below my coding
trim(iif(isnull[UserAddres s1])=true, "",[UserAd dress2]))
i don't want use Double Quatation mark. ("").becuase when my manager searching not null records using IS NOT NULL keyword its not showing any records. i have to use for that <>"", He don't like it.becuase he is thinking some hidden data in the records.
So, Can any body help me to solve this above issue.thx
trim(iif(isnull[UserAddres
i don't want use Double Quatation mark. ("").becuase when my manager searching not null records using IS NOT NULL keyword its not showing any records. i have to use for that <>"", He don't like it.becuase he is thinking some hidden data in the records.
So, Can any body help me to solve this above issue.thx
You basically have to put back the NULL's :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
NULLs should stay NULL.
Empty strings ("") will now become NULL.
Other text will remain same unless had extra spaces, it will be trimmed.
Empty strings ("") will now become NULL.
Other text will remain same unless had extra spaces, it will be trimmed.
ASKER
its possible to use like this
trim(iif(isnull[UserAddres s1])=true, NULL,[User Address2]) )
I DON"T WANT TO USE "" DOUBLE QUATATION MARK.
thx
trim(iif(isnull[UserAddres
I DON"T WANT TO USE "" DOUBLE QUATATION MARK.
thx
Since you DON'T want the double quotation marks, you will have to check for that and eliminate it. There is no point in checking for NULL just to replace with NULL.
Oh wait, I see now UserAddress1 is a different field than UserAddress2. Thought they were the same. That should be fine, although do you know for sure you don't already have "" in some of the UserAddress1 data.
If you do then...
(what this does is makes NULLs, "", and " " all equal NULL)
(what this does is makes NULLs, "", and " " all equal NULL)
IIF(TRIM(NZ([UserAddress1], ""))="", NULL, TRIM([UserAddress2]))
Trim(IIF(UserAddress1 IS NULL, "", UserAddress2))