Concatenation

[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

 

[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

 

[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)

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

 

[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

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

 

[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])