Trim and Concatenate

[brianborn]

I'm having a little trouble knowing what to search for to find an answer, so I'm hoping someone here will be able to help. For a novice like myself, it is difficult, but for the experienced, this should be a piece of cake.

On a report I have the following code to concatenate two fields.

=Trim([ContactAddress] & ", " & [ContactAddress2])

However, most records will have a NULL value in the second field so I would like to know how to eliminate the comma between the fields if this second field is NULL.

 

[bob fitz]

Untested, but try:
=Trim([ContactAddress]) & IIF Not IsNull(Trim([ContactAddress2])), ", " & Trim([ContactAddress2]),"")

 

[brianborn]

Untested, but try:
=Trim([ContactAddress]) & IIF Not IsNull(Trim([ContactAddress2])), ", " & Trim([ContactAddress2]),"")

Thanks Bob! I tried you code and received the following error.

"You must enclose IIf function arguments in parentheses."

I tried adding parentheses in places that seemed logical to me, but without success. Does this error make sense to you?

 

[bob fitz]

Yes. Still untested but try:
=Trim([ContactAddress]) & IIF(Not IsNull([ContactAddress2]), ", " & Trim([ContactAddress2]),"")

 

[brianborn]

Yes. Still untested but try:
=Trim([ContactAddress]) & IIF(Not IsNull([ContactAddress2]), ", " & Trim([ContactAddress2]),"")

Thank you! This works perfectly! I really do appreciate you helping me out with this issue.

Have a wonderful day!

Brian

 

[bob fitz]

Thank you! This works perfectly! I really do appreciate you helping me out with this issue.

Have a wonderful day!

Brian

You're welcome. Glad you you've got it working.

 

[brianborn]

If not too much trouble, can you help with the following code?

This is the code I have on another form.

=Trim([ContactName] & ", " & [ContactAddress] & ", " & [ContactCity] & ", " & [ContactState] & " " & [ContactZip] & ", " & [ContactPhone])

I tried both...

=Trim([ContactName] & ", " & [ContactAddress]) & IIf(Not IsNull([ContactAddress2]),", " & Trim([ContactAddress2]),"") & ", " & [ContactCity] & ", " & [ContactState] & " " & [ContactZip] & ", " & [ContactPhone])

=Trim([ContactName] & ", " & [ContactAddress]) & IIf(Not IsNull([ContactAddress2]),", " & Trim([ContactAddress2],"") & ", " & [ContactCity] & ", " & [ContactState] & " " & [ContactZip] & ", " & [ContactPhone])

I get errors for either too many parentheses or wrong number of arguments.

 

[bob fitz]

I will take a look now and post back.

 

[bob fitz]

I have not been able to find a way of producing the desired result using IIF. Until someone else can offer a solution with IIF you may like to try this. It seems to work for me.
Copy the function below into a module.

Public Function fnFormatAddress(ContactName, ContactAddress, ContactCity, ContactState, ContactZip, ContactPhone) As String
On Error GoTo Err_fnFormatAddress_Error
Dim str As String
  If Not IsNull(ContactName) Then
    str = str & ContactName & ", "
  End If
  If Not IsNull(ContactAddress) Then
    str = str & ContactAddress & ", "
  End If
  If Not IsNull(ContactCity) Then
    str = str & ContactCity & ", "
  End If
  If Not IsNull(ContactState) And Not IsNull(ContactZip) Then
    str = str & ContactState & " "
  ElseIf Not IsNull(ContactState) Then
    str = str & ContactState & ", "
  End If
  If Not IsNull(ContactZip) Then
    str = str & ContactZip & ", "
  End If
  If Not IsNull(ContactPhone) Then
    str = str & ContactPhone & ", "
  End If
str = Left(str, Len(str) - 2)
fnFormatAddress = str
Exit_ErrorHandler:
  Exit Function
Err_fnFormatAddress_Error:
  MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fnFormatAddress of Module Module4 at Line " & Erl
  Resume Exit_ErrorHandler
 
End Function

Use the following line as the Coxontrol Source property of a text box on your form.

=fnFormatAddress([ContactName],[ContactAddress],[ContactCity],[ContactState],[ContactZip],[ContactPhone])

 

[brianborn]

Thanks for all your effort Bob! This was really more than I expected and I almost feel like I should pay you for the time.

I copied the code and pasted it into a new module, added the code to the field's control source and when I switched to Report View, a dialog box opened with the title of "Enter Parameter Value" and the label was the name of the function "fnFormatAddress".

Did I do something wrong or is something missing from the code?

 

[bob fitz]

I copied the code and pasted it into a new module, added the code to the field's control source and when I switched to Report View, a dialog box opened with the title of "Enter Parameter Value" and the label was the name of the function "fnFormatAddress".

In post #7 you said this was for a form but now you are talking about a report?

 

[brianborn]

In post #7 you said this was for a form but now you are talking about a report?

If this makes a difference and the reason that the code doesn't work, I really do apologize. I've been working on a lot of different parts of my database and made a mistake of typing form instead of report. You put way more effort into providing code than I thought it would take and I feel real bad if it was for nothing.

 

[bob fitz]

I don't do a lot with reports but I have tried the code as the Control Source of text box in the detail section and it seems to work ok. Can you check that you have entered the Control Source property correctly.
If you are still unable to get it working, please attach a copy of your db in A2003 mdb format so that I can try to figure out what's gone wrong.

 

[brianborn]

I don't do a lot with reports but I have tried the code as the Control Source of text box in the detail section and it seems to work ok. Can you check that you have entered the Control Source property correctly.
If you are still unable to get it working, please attach a copy of your db in A2003 mdb format so that I can try to figure out what's gone wrong.

I cannot upload the DB because of the data inside. Although I'm a student trying to learn, this is a database I created for work and it has confidential data in it. I can attach an image.

http://screencast.com/t/RhsrxXEZdv

 

[bob fitz]

Does it work if you move the text box to the Page Header section or if you move it to the Detail section.

 

[brianborn]

I moved and tried it in both header sections and the detail section. I still get the same dialog messages. Also I decided to delete the textbox and add a new one, that did not work either.

 

[bob fitz]

The function I gave you was called fnFormatAddress(). In the image that you attached it looks at though the module that holds that function has the same name. Can you give the module a different name. I don't know that it will make any difference but its easy to try.

 

[brianborn]

Can you give the module a different name. I don't know that it will make any difference but its easy to try.

This worked in that I do not get the same errors as before, but it does not include ContactAddress2. I modified your code as follows and get an error with "Enter Parameter Value" for "ContactAddress2". With you doing the hardest part, it seems like it would be simple to copy/paste to add another field.

Public Function fnFormatAddress(ContactName, ContactAddress, ContactAddress2, ContactCity, ContactState, ContactZip, ContactPhone) As String
On Error GoTo Err_fnFormatAddress_Error
Dim str As String
  If Not IsNull(ContactName) Then
    str = str & ContactName & ", "
  End If
  If Not IsNull(ContactAddress) Then
    str = str & ContactAddress & ", "
  End If
  If Not IsNull(ContactAddress2) Then
    str = str & ContactAddress2 & ", "
  End If
  If Not IsNull(ContactCity) Then
    str = str & ContactCity & ", "
  End If
  If Not IsNull(ContactState) And Not IsNull(ContactZip) Then
    str = str & ContactState & " "
  ElseIf Not IsNull(ContactState) Then
    str = str & ContactState & ", "
  End If
  If Not IsNull(ContactZip) Then
    str = str & ContactZip & ", "
  End If
  If Not IsNull(ContactPhone) Then
    str = str & ContactPhone & ", "
  End If
str = Left(str, Len(str) - 2)
fnFormatAddress = str
Exit_ErrorHandler:
  Exit Function
Err_fnFormatAddress_Error:
  MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fnFormatAddress of Module Module4 at Line " & Erl
  Resume Exit_ErrorHandler
 
End Function

=fnFormatAddress([ContactName],[ContactAddress],[ContactAddress2],[ContactCity],[ContactState],[ContactZip],[ContactPhone])

 

[bob fitz]

Is ContactAddress2 a field in the table/query that the report is bound to.

 

[brianborn]

Is ContactAddress2 a field in the table/query that the report is bound to.

Thank you so much Bob. This was a field that I recently added to my table and I did not add it to the Record Source Query. Once I did that, everything works perfectly.

Your are awesome!!!