Solved

Replace empty string with No Data

Posted on 2013-05-29
7
371 Views
Last Modified: 2013-05-30
I have a field1 text type in MS access table1 that could have empty string in.

I need to create select statement that will work in Access and SQL Server to replace empty string to “No Data”
Something as replace function, however replace is not working in SQL Server, not sure with Nz too.

Any idea?
0
Comment
Question by:Taras
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39206502
does "empty string" mean "null"? (I will assume it does.)

syntax differences between the 2 products may preclude a single solution

sql server:

isnull([field1],'no data')
case when [field1] is null then 'no data' else [field1] end

in access: (I think, not my strength)

nz([field1],"no data")
iif(isnull([field1]),"no data",[field1])
0
 

Author Comment

by:Taras
ID: 39206503
Empty string means "" not Null
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39206514
by the way, you should include sql server as a topic if asking sql server related questions
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Expert Comment

by:PortletPaul
ID: 39206529
ok, my bad - wasn't sure

I think you face the same sort of issues however in that syntax differs between the 2 products.

sql server:

case when [field1] ='' then 'no data' else [field1] end

in access: (I think, not my strength)

iif([field1]="","no data",[field1])
0
 

Author Comment

by:Taras
ID: 39206549
I need Select statment.
I know for IIF(condition,true,false)
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39206561
>>I need to create select statement that will work in Access and SQL Server to replace empty string to “No Data”

I am unable to suggest one that will work in both - which was my point really. Perhaps someone else can.

select -- sql server
case when [field1] ='' then 'no data' else [field1] end as field1
from [dbo].[table1]

select -- access, I think
iif([field1]="","no data",[field1]) field1
from [table1]

I'd be more than happy to provide a more comprehensive query, but I only know of [field1] & [table1]

{+ edit sorry a typo fixed}
0
 

Author Closing Comment

by:Taras
ID: 39209179
Thanks a lot
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

632 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