Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Nulll Query

Posted on 2009-07-09
8
Medium Priority
?
202 Views
Last Modified: 2013-11-29
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
Comment
Question by:lankapala
  • 6
8 Comments
 
LVL 85
ID: 24818274
Use this:

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

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24818411
You basically have to put back the NULL's :)
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 24818430
You would update table with this:
IIF(TRIM([UserAddress1])="", NULL, TRIM([UserAddress2]))

Open in new window

0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24818441
NULLs should stay NULL.
Empty strings ("") will now become NULL.
Other text will remain same unless had extra spaces, it will be trimmed.
0
 
LVL 2

Author Comment

by:lankapala
ID: 24818448
its possible to use like this
trim(iif(isnull[UserAddress1])=true,NULL,[UserAddress2]))

I DON"T WANT TO USE "" DOUBLE QUATATION MARK.
thx
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24818503
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24818515
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24818524
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question