SQL Nulll Query

please find below my coding
trim(iif(isnull[UserAddress1])=true,"",[UserAddress2]))
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
LVL 2
lankapalaAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
You would update table with this:
IIF(TRIM([UserAddress1])="", NULL, TRIM([UserAddress2]))

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Use this:

Trim(IIF(UserAddress1 IS NULL, "", UserAddress2))

0
 
Kevin CrossChief Technology OfficerCommented:
You basically have to put back the NULL's :)
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Kevin CrossChief Technology OfficerCommented:
NULLs should stay NULL.
Empty strings ("") will now become NULL.
Other text will remain same unless had extra spaces, it will be trimmed.
0
 
lankapalaAuthor Commented:
its possible to use like this
trim(iif(isnull[UserAddress1])=true,NULL,[UserAddress2]))

I DON"T WANT TO USE "" DOUBLE QUATATION MARK.
thx
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
If you do then...
(what this does is makes NULLs, "", and " " all equal NULL)
IIF(TRIM(NZ([UserAddress1], ""))="", NULL, TRIM([UserAddress2]))

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.