434,930 Members | 1,383 Online
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
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

 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

 P: n/a

 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 Took me some time to figure out that the "," needs to be an ";" but now itworks!Great! Thanksjohn

### This discussion thread is closed

Replies have been disabled for this discussion.