?
Solved

How get rid of second comma in a concatenated field if there is no data in the 2nd field

Posted on 2014-03-15
2
Medium Priority
?
307 Views
Last Modified: 2014-03-15
I have this syntaxin a query designer field but need to get rid of the 2nd comma if there is no data in the field "Address2)

Street Address: [Address1] & IIf([Address2]="","",", " & [Address2])

Right as it stands I get, for example,

1234 West Circle Drive,

but want to get just

1234 West Circle Drive  (no comma)


What is wrong with my syntax?

--Steve
0
Comment
Question by:SteveL13
[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
2 Comments
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 39931629
Generally, the address2 would be NULL not "", if that is the case, you can simply use:

StreetAddress: [Address1] & (", " + [Address2])

The use of the + to concatenate the comma and [Address2] will result in a NULL value if [Address2] is NULL. If you use a '&' to do the concatenation, it would result in ','

If [Address2] could be NULL or an empty string, then I would recommend:

StreetAddress: = [Address1] & IIF([Address2] & "" = "", NULL, ", " & [Address2])
0
 

Author Comment

by:SteveL13
ID: 39931650
Perfect solution.  Thanks.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

718 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