Concatenate fields using a public function

[jkirkland]

I have many tables that use the same field names and in my queries I always add a formula to concatenate those fields into an account number.

Could anyone help me figure out how to automate this in a public function? I learning VBA but am not too knowledgable yet on creating my own functions, how to declare the variables and whether the function would need to reference the query name or not.

The fields are GMFUND, GMDPT, GMDIV
My formula in my queries is GMACFM: [GMFUND] & "-" & [GMDPT] & [GMDIV]

Thanks for the help!

 

[snotmare]

Hello jkirkland!

Is this what you're looking for?

MsgBox ConcatField(GMFUND, GMDPT, GMDIV)

Public Function ConcatField(ByVal strField1 As String, ByVal strField2 As String, ByVal strField3 As String)
    ConcatField = strField1 & "-" & strField2 & strField3
End Function

He who has knowledge spares his words, and a man of understanding is of a calm spirit. Even a fool is counted wise when he holds his peace; when he shuts his lips, he is considered perceptive. - King Solomon

 

[jkirkland]

This may be what I'm looking for but I can't figure out how to call in in my query. I keep getting compile errors.

Should I be calling it something like GMACFM: ConcatField("GMFUND","GMDPT","GMDIV")? I've tried this syntax and a few others but can't get them to work.

Thanks for the quick reply earlier!

 

[Tranman]

jk,
You could do this in a function, but I don't think it would really save you any work. You would either have to pass all of your fields to the function as arguments, or pass the primary key to the function and let the function run a query and figure out the values of the fields (big overhead for small return).

In other words, you can't write a function that will automatically know what the field values are for any given row without you telling it, and return them concatenated.

What you could do which might save you some keying is to write a query that contains all of your fields plus the concatenated value, and join that to your queries when you need the fields stitched together. Even here, you're not saving much, and you are creating more overhead.

If you are still interested in creating a function just for the sake of doing it, here is how it would look:

Public Function Concat(strGMFUND As String, strGMDPT As String, strGMDIV As String) As String
Concat = strGMFUND & "-" & strGMDPT & strGMDIV
End Function

You would call the function by saying:
GMACFM: Concat([GMFUND],[GMDPT],[GMDIV])

Like I said, you're not saving much...

Tranman

 

[jkirkland]

Thanks Tranman. Actually your solution will save me some time and it will ensure that the account numbers are built consistently.