Solved

SQL Nulll Query

Posted on 2009-07-09
8
189 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 84
ID: 24818274
Use this:

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

0
 
LVL 59

Expert Comment

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

Accepted Solution

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

Open in new window

0
 
LVL 59

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 59

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 59

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 59

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now