Concatenate - Replace Characters- Remove Characters 5

[BoxHead]

For some time, I've wondered why there weren't more aggregate functions like DLookup. Common needs continuously arise in our apps to concatenate fields or values or to find and replace or strip characters from strings. I figure that Microsoft is using much the same code as the rest of us, so I came up with the following functions that perform like the aggregates.

Pasting them into their own modules will make them available throughout the database.

ConcatField() strings together the values in a single field from a table or saved query.

ConcatRows() strings together the values in different fields from a single record.

StripChar() finds a specific character or string within a string and removes it or replaces it with a specified character or string.

Public Function ConcatField(MyFld As String, MyBreak As String, TblQryName As String) As String

Dim MyString As String, strSQL As String
Dim db As Database, rst As Recordset

strSQL = "SELECT [" & MyFld & "] FROM " & TblQryName & ";"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst

    Do Until rst.EOF
    
    MyString = MyString & rst.Fields(0) & MyBreak
    rst.MoveNext
    Loop
    
ConcatField = Left(MyString, Len(MyString) - Len(MyBreak))

End Function

With the ConcatField function, the three parts you would provide are:
"The name of the field",
"comma or other separator",
"the name of your table or saved query".

_____________________________

Public Function ConcatRow(MyFld As String, MyBreak As String, _
                            TblQryName As String, Optional strCriteria As String) _
                            As String

Dim MyString As String, strSQL As String, intF As Integer
Dim db As Database, rst As Recordset, FString As String
Dim FirstFld As String
If InStr(MyFld, &quot;,&quot;) <> 0 Then
FirstFld = Left(MyFld, InStr(MyFld, &quot;,&quot;) - 1)
Else
FirstFld = MyFld
End If

If strCriteria = &quot;&quot; Then
strSQL = &quot; SELECT &quot; & MyFld & &quot; FROM &quot; & TblQryName _
& &quot; WHERE &quot; & TblQryName & &quot;.&quot; & FirstFld & &quot; = &quot; & FirstFld & &quot;;&quot;
Else
strSQL = &quot; SELECT &quot; & MyFld & &quot; FROM &quot; & TblQryName & &quot; WHERE &quot; & strCriteria & &quot;;&quot;
End If
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

For intF = 0 To rst.Fields.Count - 1
    MyString = MyString & rst.Fields(intF) & MyBreak
Next intF

ConcatRow = Left(MyString, Len(MyString) - Len(MyBreak))

End Function

With ConcatRow, you would provide the names of the fields you want concatenated, the separator, the table or saved query and any criteria. The criteria follows the rules of criteria in a DLookup function. If you omit the criteria, the first record only is returned.

Example: In query design view,

Expr1: concatrow("[prpid],[prpname],[prpstreet]","; ","tblproperties","[prpid] = " & [prpid])

returns each record's property ID#, the name of the property and the street address using semicolons as separators.

______________________________________

Public Function StripChar(MyChar As String, _
    MyString As Variant, Optional NewChar As String) As String

Dim NewVal As String
NewVal = Nz(MyString, "There is no text to evaluate.")

    Do
    If InStr(NewVal, MyChar) = 0 Then
        Exit Do
    Else
        NewVal = Left(NewVal, InStr(NewVal, MyChar) - Len(MyChar)) _
        & NewChar & Mid(NewVal, InStr(NewVal, MyChar) + Len(MyChar))
    End If
    Loop
    
StripChar = NewVal

End Function

The StripChar function simply finds a specified character/s in a string and replaces it with the character/s you specify. If you don't specify a new character, the character (and/or space) is removed.

EG:

StripChar("(312)", [custPhone])

returns phone numbers without the 312 area code so it isn't dialed.

StripChar("(312)", [custPhone], "(847)")

changes the 312 area code to the newer 847.

An extra FYI...

Len(MyString)-len(StripChar(" ",MyString))

tells you the number of spaces in MyString.

_______________________________________

It's been a lot of fun working on these and I hope someone finds them useful.







John

Use what you have,
Learn what you can,
Create what you need.

 

[LarryDavidow]

Hi John

Thanks for the code. I've used it quite a bit. Question...

Is there any way to strip multiple chars from a text string. Eg. I have a phone number field set as text. It follows this format: 999/999-9999. The "/" and "-" are chars. I would like to remove them so I can convert the phone number to numeric.

 

[dschomburg]

the StripChar function is a great idea but I'm having issues with.

IE, if MyChar = "CTO" and MyString ="Hey CTO, Please Call The Office"

Then the result would be = "Hey , Please all he ffie"
So that it's removing every instance of each letter in MyChar and not MyChar as a complete string.???

Any thoughts?

 

[allanon]

Hey all. First off thanks to BoxHead for supplying the function. Lots of fun. I found a small bug in StripChar so I modified one line. I created a new one from it called StripCharArray. Here are the two functions:

Public Function StripChar(MyChar As String, _
MyString As Variant, Optional NewChar As String) As String

Dim NewVal As String

NewVal = Nz(MyString, "There is no text to evaluate.&quot

Do
If InStr(NewVal, MyChar) = 0 Then
Exit Do
Else
NewVal = Left(NewVal, InStr(NewVal, MyChar) - 1) & _
Mid(NewVal, InStr(NewVal, MyChar) + Len(MyChar))
End If
Loop

StripChar = NewVal

End Function

Public Function StripCharArray(MyString As Variant, ParamArray myChars()) As String

Dim NewVal As String
Dim i As Integer

NewVal = Nz(MyString, "There is no text to evaluate.&quot

For i = 0 To UBound(myChars)
Do
If InStr(NewVal, myChars(i)) = 0 Then
Exit Do
Else
NewVal = Left(NewVal, InStr(NewVal, myChars(i)) - 1) & _
Mid(NewVal, InStr(NewVal, myChars(i)) + Len(myChars(i)))
End If
Loop
Next i
StripCharArray = NewVal

End Function

Notice in the second that the order of parameters is differnt and you cannot specify a replacement. Would not make sense in this case. If you want to do the replace use stripchar multiple times. here is what I did to test.

MyField = StripCharArray("Hey CTO, Please Call The Office", "CTO", "pl", "ll", "ff&quot

after that MyField = Hey , ease Ca The Oice

I actually used form fields for my test.

txtOutput = StripCharArray(txtInput2, Me.t1, Me.t2, Me.t3, Me.t4)

Have Fun

 

[MichaelRed]

in a more general sense, the functions are generally replaceable with either simple uses of the standard "REPLACE()" function or some simple instantiations of RegExp().

for those who have some (non-programatic) need to express themselves programatically, relatively simple uses of the Split() & Join() functions should be easier to program and understand.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[allanon]

I have neither the Split() nor the Join() function. What version of access are you using?

 

[MichaelRed]

ver 2K, but versions (basSplit and basJoin) have been posted in Tek-Tips for Retros' use.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[LarryDavidow]

Allanon

You're the bomb!!

Thanks a bunch. Works like a charm.

 

[JohnHarkins]

Great functions. Please tell me how I might use Concatfield in the following situation:

I have a report with subreports. In the report, I have a concatenated key called "key5" (really just a string comprised of 5 fields' values) from a "big" table that allows me to link the parent and children in the subreports (there's probably a better way to do this, but the concatenated key "solves" the problem of null field values that would result in imperfect/missing matches when using join lines between five pairs of records).

I can certainly use the function concatfield to create a single field of the multiple field values. But, I'm stymied in figuring out how to specify my query in the detail section that yields the Concatfield value for just "key5". Concatfield works great on feeding it a query and one field, but how do I specify that I want it to work on the fields for just a given concatenated key. Said slightly differently, is there a way to build a new table with unique values of Key5 and the Concatfield values? If so, I could process that before calling the report.

Is my problem just that I cannot reference Key5 as a criterion in the query that is the third argument of Concatfield? When I get the message that I've sent it the wrong number of parameter, then I know I'm just not seeing this clearly.

I know this may be confusing, but I'd appreciate any help you can offer.

Regards, John Harkins