Concatenation Function Issue

[Sevn]

I need to merge several fields across a table into one field. The table was created from a XtabQry that has several blanks across, that I would like excluded. I found some function code on the net, but can't seem to get it to work.

Can someone please provide some assistance?

Here is the Qry string I am trying to run:
?fConcatFld("blah","ParentTieID","Sales Terr SCust","string","STSNew")

I created a new module called "fConcatFld", and pasted the following code into it.

'************ Code Start **********
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Public Function fConcatFld(stTable As String, _
stForFld As String, _
stFldToConcat As String, _
stForFldType As String, _
vForFldVal As Variant) _
As String
'Returns mutiple field values for each unique value
'of another field in a single table
'in a semi-colon separated format.
'
'Usage Examples:
' ?fConcatFld(("Customers","ContactTitle","CustomerID", _
' "string","Owner")
'Where Customers = The parent Table
' ContactTitle = The field whose values to use for lookups
' CustomerID = Field name to concatenate
' string = DataType of ContactTitle field
' Owner = Value on which to return concatenated CustomerID
'
Dim lodb As Database, lors As Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String
Const cQ = """"

On Error GoTo Err_fConcatFld

lovConcat = Null
Set lodb = CurrentDb

loSQL = "SELECT [" & stFldToConcat & "] FROM ["
loSQL = loSQL & stTable & "] WHERE "

Select Case stForFldType
Case "String":
loSQL = loSQL & "[" & stForFld & "] =" & cQ & vForFldVal & cQ
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
loSQL = loSQL & "[" & stForFld & "] = " & vForFldVal
Case Else
GoTo Err_fConcatFld
End Select

Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)

'Are we sure that duplicates exist in stFldToConcat
With lors
If .RecordCount <> 0 Then
'start concatenating records
Do While Not .EOF
lovConcat = lovConcat & lors(stFldToConcat) & "; "
.MoveNext
Loop
Else
GoTo Exit_fConcatFld
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatFld = Left(lovConcat, Len(lovConcat) - 2)


Exit_fConcatFld:
Set lors = Nothing: Set lodb = Nothing
Exit Function

Err_fConcatFld:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
Resume Exit_fConcatFld
End Function
'************ Code End **********

I get the following error when I attempt to run the Qry string:
Expected variable or procedure, not module.

Any help will be greatly appreciated.

Thanks,

 

[Sevn]

Found part of the problem, but still need assistance.

I realized that I had the module & function named the same, and needed to modify my Qry string. The function runs, but doesn't perform the concatenation.

Please help.

 

[WayneRyan]

Sevn,

Can you post a sample DB?

Wayne

 

[Sevn]

Here is a sample DB to explain my problem better.

you will see a table, a query, and a module within the MDB.

Any assistance will be greatly appreciated.

 

[WayneRyan]

Sevn,

I don't see a DB, but DO NOT name the Module the same as the function!

Any other name for the module should make things OK.

Wayne

 

[Sevn]

Here is a sample DB to explain my problem better.

you will see a table, a query, and a module within the MDB.

Any assistance will be greatly appreciated.

 

[WayneRyan]

Sevn,

You're gonna hate this, but:

[Sales Terr SCust]

There are TWO spaces between the "Terr" and "SCust" in your field's name.

Putting spaces and special characters in your field names causes problems
like this.

hth,
Wayne

 

[Sevn]

The field names are derived from the export from the originating DB. Are you saying that I just need to rename the field to make the function work properly? have you managed to get the function to work with a rename field?

Thanks,

 

[Sevn]

I just renamed the field name, and all references to the "old" field name, and still can't get the function to work.

Do I have my query setup correctly?

I am only average when it come to this VBA stuff.

 

[WayneRyan]

Sevn,

Paste this query into the SQL view:

SELECT distinct blah.ParentTieID, 
                blah.Customer
                fConcatFld("blah","ParentTieID","Sales Terr  SCust","string",[ParentTieID]) AS STSNew
FROM blah;

Note the passing of the [ParentTieID] field in the function call.
It must be the parent field (that's what makes it variable).

hth,
Wayne

 

[Sevn]

Wayne,
I am really sorry, but I must be still doing something wrong. I have pasted your SQL query in the appropriate place, and get a missing operator error. I have attached a screenshot to illustrate.

I'm not completely sure what you you mean by the [ParentTieID] needing to be the parent field. Can you explain further? Do I need to set it up differently, or are you referring to it's use in the query?

I appreciate you patience.

Thanks,

 

[WayneRyan]

Sevn,

I lost a comma !!!

SELECT distinct blah.ParentTieID, 
                blah.Customer[B][SIZE="4"],[/SIZE][/B]
                fConcatFld("blah","ParentTieID","Sales Terr  SCust","string",[ParentTieID]) AS STSNew
FROM blah;

You have to get the distinct occurrences of ParentTieID and Customer.

Then for each of those rows returned, you call the function. I changed the
last argument of your function CALL to include --> [ParentTieID] so that it
gets called with that value for each row.

All changes were made to the query.

Wayne

 

[Sevn]

OUTSTANDING! IT WORKS.

Thank you so very much. This is much better than what I have been doing. I had been writing one really long concatenation string, and it left commas after each blank field (B02,,,,,,,B16,,,etc.). This is much nicer.

I also want to thank you for explaining the query to me. Once I understand the architecture of a statement, I can manipulate it much easier to fit my needs.

Thanks,

 

[WayneRyan]

Sevn,

Glad to help.

See ya,
Wayne