Concatenation

G

Guest

I have a query that returns the following fields - First_Name, MI, Last_Name,Suffix, Address_1, Address_2, Address_3, as well as others
I want to concatenate the name fields and address fields. I created an IF statement that look like is should work but its not. Now there may not be data in several of the fields. When I use my IF I get syntax, oerpand errors. I since have made 3 of the 4 name fields required entry, but that leaves the address fields. What I want is that if Add_3 is null give me Add_1, Add_2, If Add_2 is null I just want Add_1, ia all three have valuse I want all three. I hope this is clear. For the life of me I can't figure out where I'm going wrong. Please help, this is urgent
 
T

Ted Allen

Hi Bradley,

You didn't post the formula, so it is hard to say what is
wrong. But, you may want to try using the Nz() function,
which can return an empty string in place of Null.

for example, you could use:

Nz([Address1],"") & Nz([Address2],"") & Nz([Address3],"")

I believe this will give you what you want in any case.

-Ted Allen
-----Original Message-----
I have a query that returns the following fields -
First_Name, MI, Last_Name,Suffix, Address_1, Address_2,
Address_3, as well as others.
I want to concatenate the name fields and address
fields. I created an IF statement that look like is
should work but its not. Now there may not be data in
several of the fields. When I use my IF I get syntax,
oerpand errors. I since have made 3 of the 4 name fields
required entry, but that leaves the address fields. What
I want is that if Add_3 is null give me Add_1, Add_2, If
Add_2 is null I just want Add_1, ia all three have valuse
I want all three. I hope this is clear. For the life of
me I can't figure out where I'm going wrong. Please
help, this is urgent
 
D

Douglas J. Steele

How are you concatenating them?

If you're just trying to concatenate them all together, you shouldn't have a
problem: "text" & Null will give you "text"

On the other hand, if you want commas and spaces between them (or new line
characters), you can take advantage of the fact that "text" + Null gives you
Null, and do something like:

[Address_1] & (", " + [Address_2]) & (", " + [Address_3])

or

[Address_1] & (Chr(13) & Chr(10) + [Address_2]) & (Chr(13) & Chr(10) +
[Address_3])

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



BradleyJ said:
I have a query that returns the following fields - First_Name, MI,
Last_Name,Suffix, Address_1, Address_2, Address_3, as well as others.
I want to concatenate the name fields and address fields. I created an IF
statement that look like is should work but its not. Now there may not be
data in several of the fields. When I use my IF I get syntax, oerpand
errors. I since have made 3 of the 4 name fields required entry, but that
leaves the address fields. What I want is that if Add_3 is null give me
Add_1, Add_2, If Add_2 is null I just want Add_1, ia all three have valuse I
want all three. I hope this is clear. For the life of me I can't figure
out where I'm going wrong. Please help, this is urgent
 
G

Graham Mandeno

You can use a nifty little trick here which utilises the difference between
the & and the + operators.

"some string" & Null results in "some string"
"some string" + Null results in Null

So, you can make an expression like this:

NameAndAddress: FirstName & (" " + MI) & Last_Name & (", " + Suffix)
& (Chr(13) + Chr(10) + Address_1)
& (Chr(13) + Chr(10) + Address_2)
& (Chr(13) + Chr(10) + Address_3)

This will only add the extra literal strings (blank before MI, ", " before
Suffix and new line before each address line) if there is data in those
fields.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand - Home of Lord of the Rings


BradleyJ said:
I have a query that returns the following fields - First_Name, MI,
Last_Name,Suffix, Address_1, Address_2, Address_3, as well as others.
I want to concatenate the name fields and address fields. I created an IF
statement that look like is should work but its not. Now there may not be
data in several of the fields. When I use my IF I get syntax, oerpand
errors. I since have made 3 of the 4 name fields required entry, but that
leaves the address fields. What I want is that if Add_3 is null give me
Add_1, Add_2, If Add_2 is null I just want Add_1, ia all three have valuse I
want all three. I hope this is clear. For the life of me I can't figure
out where I'm going wrong. Please help, this is urgent
 
J

John Vinson

I have a query that returns the following fields - First_Name, MI, Last_Name,Suffix, Address_1, Address_2, Address_3, as well as others.
I want to concatenate the name fields and address fields. I created an IF statement that look like is should work but its not. Now there may not be data in several of the fields. When I use my IF I get syntax, oerpand errors. I since have made 3 of the 4 name fields required entry, but that leaves the address fields. What I want is that if Add_3 is null give me Add_1, Add_2, If Add_2 is null I just want Add_1, ia all three have valuse I want all three. I hope this is clear. For the life of me I can't figure out where I'm going wrong. Please help, this is urgent

There's actually a very sneaky and implementation-dependent way to do
this, but it works so well I use it routinely anyway. The & and +
operators both concatenate text strings - but the + operator returns
NULL if either string is NULL, and & treats a NULL as a zero-length
string. This lets you put punctuation between the components that
you're concatenating without having annoying extra blanks or commas:

[First_Name] & (" " + [MI]) & (" " + [Last_Name]) & (", " + [Suffix])
& (", " & [Address_1]) & (" " + [Address_2]) & (" " + [Address3])
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top