• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

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
0
lankapala
Asked:
lankapala
  • 6
1 Solution
 
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
 
Kevin CrossChief Technology OfficerCommented:
You would update table with this:
IIF(TRIM([UserAddress1])="", NULL, TRIM([UserAddress2]))

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now