Issue when combining fields

[Tara]

I have combined 3 fields (firstname, lastname, business) in a query to come
up with a concatenated field called custname. The only problem is when one
of the fields has no data it puts a space in where the data would have been
inserted into the string. For instance, if neither firstname nor lastname
have data, and business does, I end up with something like (space) Scott's.
The problem with this is that if I want to sort alphabetically, anything with
a space before it comes up first. Is there a way to fix this?

Thanks!

 

[Michel Walsh]

Trim(FirstName & " " & LastName )

If FirstName is null, or a strings of blanks, Trim will end by eliminating
them. So:


Trim(firstName & " " & LastName) & ( ", " + business )


could do (assuming business could be null, then the + will remove the ", "
pre-concatenated to it, since + propagates the null )




Hoping it may help,
Vanderghast, Access MVP

 

[Klatuu]

You can check for Null values before concatenating the values:

SELECT IIf(firstname Is Null,"",firstname & " ") & IIf(lastname Is
Null,"",lastname & " ") & IIf(business Is Null,"", business) AS custname

 

[Roger Carlson]

I assume your concatenation looks like this:

custname: [firstname] & " " & [lastname] & " " & [business]

Change it to this:

custname: Trim([firstname]+" " & [lastname]+" " & [business])

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Tara]

Worked like a charm! Thanks!

You can check for Null values before concatenating the values:

SELECT IIf(firstname Is Null,"",firstname & " ") & IIf(lastname Is
Null,"",lastname & " ") & IIf(business Is Null,"", business) AS custname