Creating a 'join two fields' function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello there,

I'm trying to create a function which will join two fields together, and I'm
sure where to start.

Normally I would do a trim(<field1> & " " & <field2>), but it is getting
repetitive, so how would I go about creating this and then 'join3fields' and
'join4fields' functions?

Thank you for your help!
 
You can pass in the fields as a parameter arrow.

For example to concatenate the address fields together, you might pass
something like this:
=ConcateFields(", ", [CompanyName], [Address1], [Address2], [City])

Note that the first argument is the separator between fields (in this case a
comma and space.)

Function ConcatFields(strDelim As String, ParamArray varList()) As Variant
Dim strOut As String
Dim i As Integer

For i = LBound(varList) To UBound(varList)
If Not IsNull(varList(i)) Then
strOut = strOut & varList(i) & strDelim
End If
Next
i = Len(strOut) - Len(strDelim)
If i > 0 Then
ConcatFields = Left(strOut, i)
Else
ConcatFields = Null
End If
End Function
 
Thank you Allen, that was very helpful!

As I am very new to using the VBA aspect of Access, could you recommend any
books for reading to help me along?

Regards,

John

Allen Browne said:
You can pass in the fields as a parameter arrow.

For example to concatenate the address fields together, you might pass
something like this:
=ConcateFields(", ", [CompanyName], [Address1], [Address2], [City])

Note that the first argument is the separator between fields (in this case a
comma and space.)

Function ConcatFields(strDelim As String, ParamArray varList()) As Variant
Dim strOut As String
Dim i As Integer

For i = LBound(varList) To UBound(varList)
If Not IsNull(varList(i)) Then
strOut = strOut & varList(i) & strDelim
End If
Next
i = Len(strOut) - Len(strDelim)
If i > 0 Then
ConcatFields = Left(strOut, i)
Else
ConcatFields = Null
End If
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Kamitsukenu said:
Hello there,

I'm trying to create a function which will join two fields together, and
I'm
sure where to start.

Normally I would do a trim(<field1> & " " & <field2>), but it is getting
repetitive, so how would I go about creating this and then 'join3fields'
and
'join4fields' functions?

Thank you for your help!
Click to expand...
Click to expand...
 
There are several books that deal with VBA as part of Access.

Here's a new one like that:
http://www.microsoft.com/MSPress/books/9784.aspx

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Kamitsukenu said:
Thank you Allen, that was very helpful!

As I am very new to using the VBA aspect of Access, could you recommend
any
books for reading to help me along?

Regards,

John

Allen Browne said:
You can pass in the fields as a parameter arrow.

For example to concatenate the address fields together, you might pass
something like this:
=ConcateFields(", ", [CompanyName], [Address1], [Address2], [City])

Note that the first argument is the separator between fields (in this
case a
comma and space.)

Function ConcatFields(strDelim As String, ParamArray varList()) As
Variant
Dim strOut As String
Dim i As Integer

For i = LBound(varList) To UBound(varList)
If Not IsNull(varList(i)) Then
strOut = strOut & varList(i) & strDelim
End If
Next
i = Len(strOut) - Len(strDelim)
If i > 0 Then
ConcatFields = Left(strOut, i)
Else
ConcatFields = Null
End If
End Function

Kamitsukenu said:
Hello there,

I'm trying to create a function which will join two fields together,
and
I'm
sure where to start.

Normally I would do a trim(<field1> & " " & <field2>), but it is
getting
repetitive, so how would I go about creating this and then
'join3fields'
and
'join4fields' functions?

Thank you for your help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top