Add Currency Format to VBA Concat String

[adrienne_r30]

Please help. I have inherited a DB and really need help cleaning it up. I have a Concatenate string of text and currency. When joined together, the currency format disappears. I need to reformat so the string stays together with new format. Below is what I have:

Public Function ConcatAgreementFundsCommitted(ID As String) As String
'/ Purpose: Generate a concatenated string of Tracks for selected line.
On Error GoTo Err_Handler
Dim lngLen As Long 'Length of string.
Dim strOut As String 'Output string to concatenate to.
Dim strSeparator As String
strOut = ""
strSeparator = ", "
'Initialize to Null
ConcatAgreementFundsCommitted = ""
Set rs = DBEngine(0)(0).OpenRecordset("Select Fund_Source & ': ' & '$' & Funds_Committed from qry_SQL_Agreement_Funds_Committed where Agreement_ID=" & ID, dbOpenDynaset)

'Loop through the matching records
Do While Not rs.EOF
strOut = strOut & rs(0) & strSeparator
rs.MoveNext
Loop
'Return the string without the trailing separator.
lngLen = Len(strOut) - Len(strSeparator)
If lngLen > 0 Then
ConcatAgreementFundsCommitted = Left(strOut, lngLen)
End If
Exit_Handler:
On Error Resume Next
'Clean up
Set rs = Nothing
Exit Function
Err_Handler:
'MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
Resume Exit_Handler
End Function


The red text is my problem area. I need 'Funds_Committed' to display as currency.

 

[CJ_London]

try

Select Fund_Source & ': ' & format(Funds_Committed,"currency")

 

[adrienne_r30]

Thanks so much. I saw that solution a couple of other places too and just couldn't seem to get it to work. I finally found this, & FormatCurrency(Funds_Committed,0), and it did the trick.

Thanks for the help tho