concatenate

[doran_doran]

Hi everyone, new look, I like it, but need to learn funtionality. But it still does not change the color for the one that has been views. the link color.

Anyway.

I had to import 4 fields from a contact database into my current database.
salutation
first name
middle initial
last name

I am trying to concetanate. But guess what there are some null values for these fileds.

for example.
salutation firstname mi lastname
record1: mr john s edwards
record2: mrs venessa smith
record3: micheal moore
record4: april r roberts

ok, here is how they should look like after concatenate.

mr john s edwards
mrs venessa smith
micheal moore
april r roberts

I am using following it's working but not for all the scenerio.
--- code starts here ---
BillingContactName2: IIf(IsNull([BillingContactSalutation]),[BillingContactFirstName],[BillingContactSalutation]) & " " & IIf(IsNull([BillingContactSalutation]),(IIf(IsNull([BillingContactMiddleInitial]),Left([BillingContactMiddleInitial],0),[BillingContactMiddleInitial])),[BillingContactFirstName]) & " " & IIf(IsNull([BillingContactSalutation]),[BillingContactLastName],(IIf(IsNull([BillingContactMiddleInitial]),Left([BillingContactMiddleInitial],0),[BillingContactMiddleInitial]))) & " " & IIf(IsNull([BillingContactSalutation]),Left([BillingContactLastName],0),[BillingContactLastName])
--- code ends here ---

by the this code is in a query.

See following results when I impleted the code

mr john s edwards (works fine)
mrs venessa smith (has an extra space between vanessa and smith)
micheal moore (has an extra space between micheal and moore)
april r roberts (works fine)

PROMPT RESPOND WILL BE APPRECIATE.

DIANNA

 

[Mile-O]

Hi everyone, new look, I like it, but need to learn funtionality. But it still does not change the color for the one that has been views. the link color.

Unread links are in bold; read links are not.

I am trying to concetanate. But guess what there are some null values for these fileds.

Does this work?

FullName: Nz([salutation] & " ", "") & Nz([first name] & " ", "") & Nz([middle initial] & " ", "") & Nz([last name])

PROMPT RESPOND WILL BE APPRECIATE.

Patience is a virtue.

 

[doran_doran]

nz did not work

Sorry, following suggession did not work.

FullName: Nz([BillingContactSalutation] & " ", "") & Nz([BillingContactFirstName] & " ", "") & Nz([BillingContactMiddleInitial] & " ", "") & Nz([BillingContactLastName])

Result
record1: mr john s edwards (works fine)
record2: mrs vanessa smith (extra space bet vanessa and smith)
record3: micheal moore (space before micheal. extra space bet. two names)
record4: april r roberts (space before april)

 

[Mile-O]

If you have A2000 or above you can surround:

FullName: Nz([BillingContactSalutation] & " ", "") & Nz([BillingContactFirstName] & " ", "") & Nz([BillingContactMiddleInitial] & " ", "") & Nz([BillingContactLastName])

with the Replace function to remove the double spacing. and also the Trim to surround it.

or....

Public Function BuildName(sal, fore, init, sur) As String
    Dim strTemp As String
    If Not IsNull(sal) Then strTemp = sal & " "
    If Not IsNull(fore) Then strTemp = strTemp & fore & " "
    If Not IsNull(init) Then strTemp = strTemp & init & " "
    If Not IsNull(sur) Then strTemp = strTemp & sur
    BuildName = Trim(strTemp)
End Function

and:

FullName: BuildName([BillingContactSalutation], [BillingContactForename], [BillingContactInitial], [BillingContactSurname])

 

[doran_doran]

Access 97 but is not working...

OK I did followings

--- first ----
Public Function BuildName(sal, first, mi, last) As String
Dim strTemp As String
If Not IsNull(BillingContactSalutation) Then strTemp = sal & " "
If Not IsNull(BillingContactFirstName) Then strTemp = strTemp & fist & " "
If Not IsNull(BillingContactMiddleInitial) Then strTemp = strTemp & mi & " "
If Not IsNull(BillingContactLastName) Then strTemp = strTemp & last
BuildName = Trim(strTemp)
End Function

I saved the above as ModBuildName

Then I open the my query and inserted a column and put.

Name: BuildName([BillingContactSalutation], [BillingContactFirstname], [BillingContactMiddleInitial], [BillingContactLastname])


It bumms out. the mod windows open with billingcontactsalutation highlighted in yellow. from this point i cant even close my window.

Thanks


by the way
====== this is the whole query =======
SELECT tblInvoice.InvoiceSAPCustomerNumber, tblInvoice.GA_Number, tblInvoice.PlanNum, tbl_groups.Plan_Name, tbl_groups.GA_Name, tblInvoice.BillingContactSalutation, tblInvoice.BillingContactFirstName, tblInvoice.BillingContactMiddleInitial, tblInvoice.BillingContactLastName, Len(IIf(IsNull([BillingContactSalutation]),[BillingContactSalutation]) & " " & [BillingContactFirstName] & " " & [BillingContactMiddleInitial] & [BillingContactLastName]) AS length, IIf(IsNull([BillingContactSalutation]),[BillingContactFirstName],[BillingContactSalutation]) & " " & IIf(IsNull([BillingContactSalutation]),(IIf(IsNull([BillingContactMiddleInitial]),Left([BillingContactMiddleInitial],0),[BillingContactMiddleInitial])),[BillingContactFirstName]) & " " & IIf(IsNull([BillingContactSalutation]),[BillingContactLastName],(IIf(IsNull([BillingContactMiddleInitial]),Left([BillingContactMiddleInitial],0),[BillingContactMiddleInitial]))) & " " & IIf(IsNull([BillingContactSalutation]),Left([BillingContactLastName],0),[BillingContactLastName]) AS BillingContactName2, Nz([BillingContactSalutation] & " ","") & Nz([BillingContactFirstName] & " ","") & Nz([BillingContactMiddleInitial] & " ","") & Nz([BillingContactLastName]) AS FullName, Nz([BillingContactSalutation] & " ","") & Nz([BillingContactFirstName] & " ","") & Nz([BillingContactMiddleInitial] & " ","") & Nz([BillingContactLastName]) AS FullName2, BuildName([BillingContactSalutation],[BillingContactFirstname],[BillingContactMiddleInitial],[BillingContactLastname]) AS Name, tblInvoice.BillingAddress, tblInvoice.BillingCity, tblInvoice.BillingState, tblInvoice.BillingZip, tbl_groups.Primary_Administrator, DLookUp("([areacode])","tbl_admin","[contact] = '" & [primary_administrator] & "'") AS AreaCode, "-" AS Dash2, DLookUp("([lec])","tbl_admin","[contact] = '" & [primary_administrator] & "'") AS LEC, "-" AS Dash, DLookUp("([phone])","tbl_admin","[contact] = '" & [primary_administrator] & "'") AS Extension, tbl_groups.Manager, tbl_groups.Market_sgmt, tbl_groups.Region, IIf([tblinvoice]![InvoiceSAPMasterUpload]=False,"N","U") AS [SAP Status], tblInvoice.BillingStructure
FROM tblInvoice INNER JOIN tbl_groups ON tblInvoice.GA_Record_ID_2 = tbl_groups.GA_Record_ID_2
WHERE (((Len(IIf(IsNull([BillingContactSalutation]),[BillingContactSalutation]) & " " & [BillingContactFirstName] & " " & [BillingContactMiddleInitial] & [BillingContactLastName]))<40) AND ((tblInvoice.BillingStructure)="Group Billed"))
ORDER BY Len(IIf(IsNull([BillingContactSalutation]),[BillingContactSalutation]) & " " & [BillingContactFirstName] & " " & [BillingContactMiddleInitial] & [BillingContactLastName]) DESC;

 

[doran_doran]

does anyone else wanna pitch in.

does anyone else wanna pitch in.

 

[Mile-O]

Public Function BuildName(sal, first, mi, last) As String
    Dim strTemp As String
    If Not IsNull(BillingContactSalutation) Then strTemp = sal & " "
    If Not IsNull(BillingContactFirstName) Then strTemp = strTemp & fist & " "
    If Not IsNull(BillingContactMiddleInitial) Then strTemp = strTemp & mi & " "
    If Not IsNull(BillingContactLastName) Then strTemp = strTemp & last
    BuildName = Trim(strTemp)
End Function

Is the use of first as a parameter and then fist in the function just a typo?

 

[Mile-O]

Also note how you've customised the function I gave you rather than using it as is - it works as I gave it to you but you replaced the parameters with fe field names within the code for some reason.

 

[neileg]

I think there's some confusion as to whether the 'empty' fields are nulls or zero length strings. If they were nulls, then Mile's Nz() approach would work perfectly. However, if they are zero length strings, then they aren't nulls and the functions Nz([field]&" ","") is returning " " for the condition and so not substituting the "".

Having said that, I don't use A97 any more, and would simply use Trim() in my dbs, under A2k, so I can't help.

 

[doran_doran]

Mile is Correct...

Is my fault not understanding the scope. Sorry. It worked the way it was posted. Thanks Mile. Dianna