By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,930 Members | 1,383 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,930 IT Pros & Developers. It's quick & easy.

Concatenating fields

P: n/a
In a query I have the fields Letter, Prefix, and Surname. I need to make a
report in which there is one field called Name which is a concatenation of
the three fields.
When I do this:
Expr2: [Letter]+" "+[Prefix]+" "+[Surname]
it will only concatenate the fields if all fields have a value, which is not
what I want.
If this problem was solved I guess I would face another problem being that
there will be 2 spaces in a row between the Letter value and Surname value
for the records where Prefix has no value. There should only be one space.

How can I solve these problems? In a query?
Thanks in advance,
john
Aug 30 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
ceate an expression in your query, add this as the calculation:
iif(Isnull([Prefix],[Letter] & " " & [Surname],[Prefix] & " " &
[Letter] & " " & [Surname])
base your report on this. Or you could just create an unbound field in
your report.

Aug 30 '06 #2

P: n/a
Took me some time to figure out that the "," needs to be an ";" but now it
works!
Great! Thanks
john

<pi********@hotmail.comschreef in bericht
news:11**********************@b28g2000cwb.googlegr oups.com...
ceate an expression in your query, add this as the calculation:
iif(Isnull([Prefix],[Letter] & " " & [Surname],[Prefix] & " " &
[Letter] & " " & [Surname])
base your report on this. Or you could just create an unbound field in
your report.

Aug 30 '06 #3

P: n/a
The reason is that the + sign returns a null if either expression is
null. The & does not. This can actually help you.

[Firstname] = John
[LastName] = Smith
[MiddleName] = Null

LastName & ", " & Firstname & " " & MiddleName & " ." will return:

Smith, John .

But:

LastName & ", " & Firstname & " " & MiddleName + " ." will return:

Smith, John
[Firstname] = Null
[LastName] = Smith

LastName & ", " & FirstName
Smith,

LastName & ", " + FirstName
Smith

In your original expression, if ANY of the fields were null, the whole
result would be null. If you replaced them with Ampersands, then there
might be extra spaces. My solution:
Expr2: [Letter] & " "+[Prefix] & " "+[Surname]

Chris Nebinger

john wrote:
Took me some time to figure out that the "," needs to be an ";" but now it
works!
Great! Thanks
john

<pi********@hotmail.comschreef in bericht
news:11**********************@b28g2000cwb.googlegr oups.com...
ceate an expression in your query, add this as the calculation:
iif(Isnull([Prefix],[Letter] & " " & [Surname],[Prefix] & " " &
[Letter] & " " & [Surname])
base your report on this. Or you could just create an unbound field in
your report.
Aug 30 '06 #4

P: n/a
<ch************@gmail.comschreef in bericht
In your original expression, if ANY of the fields were null, the whole
result would be null. If you replaced them with Ampersands, then there
might be extra spaces. My solution:
Expr2: [Letter] & " "+[Prefix] & " "+[Surname]
Cool! Thanks.
john
Aug 30 '06 #5

P: n/a
[LastName] will never be null (IF you don't allow Nulls in that field...)
[FirstName] can be Null
[MiddleName] can be Null

I use: FullName: Trim([FirstName] & " " & Trim([MiddleName] & " " & [LastName]))
==Always a good string with no (double) spaces.

Arno R

<ch************@gmail.comschreef in bericht news:11**********************@i3g2000cwc.googlegro ups.com...
The reason is that the + sign returns a null if either expression is
null. The & does not. This can actually help you.

[Firstname] = John
[LastName] = Smith
[MiddleName] = Null

LastName & ", " & Firstname & " " & MiddleName & " ." will return:

Smith, John .

But:

LastName & ", " & Firstname & " " & MiddleName + " ." will return:

Smith, John
[Firstname] = Null
[LastName] = Smith

LastName & ", " & FirstName
Smith,

LastName & ", " + FirstName
Smith

In your original expression, if ANY of the fields were null, the whole
result would be null. If you replaced them with Ampersands, then there
might be extra spaces. My solution:
Expr2: [Letter] & " "+[Prefix] & " "+[Surname]

Chris Nebinger

john wrote:
>Took me some time to figure out that the "," needs to be an ";" but now it
works!
Great! Thanks
john

<pi********@hotmail.comschreef in bericht
news:11**********************@b28g2000cwb.googleg roups.com...
ceate an expression in your query, add this as the calculation:
iif(Isnull([Prefix],[Letter] & " " & [Surname],[Prefix] & " " &
[Letter] & " " & [Surname])
base your report on this. Or you could just create an unbound field in
your report.
Aug 30 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.