Solved

Replace empty string with No Data

Posted on 2013-05-29
7
363 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
  • 4
  • 3
7 Comments
 
LVL 48

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 48

Expert Comment

by:PortletPaul
ID: 39206514
by the way, you should include sql server as a topic if asking sql server related questions
0
Industry Leaders: 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 48

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 48

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

685 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