glasgowlad1999
Registered User.
- Local time
- Today, 07:58
- Joined
- Jun 17, 2013
- Messages
- 27
Can you help me add a format to the Sql select for currency :banghead:
This is just a section of the full code before
This is just a section of the full code before
Code:
Function RunLWSCA()
Dim cnn As ADODB.Connection
Dim rstUplo As New ADODB.Recordset
Dim rstDiag As New ADODB.Recordset
Dim rstTemp As New ADODB.Recordset
Dim SQL As String
Dim strDiag
Set cnn = CurrentProject.Connection
' Clear temporary table
SQL = "Delete * from [LWorkSheetCA]"
cnn.Execute SQL
' Open temporary table as a recordset
rstTemp.Open "LWorkSheetCA", cnn, adOpenForwardOnly, adLockPessimistic
' Loop through Upload table
rstUplo.Open "QrytblProcedure", cnn, adOpenForwardOnly, adLockReadOnly
Do While Not rstUplo.EOF
' Add a new record in temporary table
' and copy Item and Description to it
rstTemp.AddNew
rstTemp![Claim_Numbers] = rstUplo![Claim_Number]
rstTemp![FacIDs] = rstUplo![FacID]
rstTemp.Update
' Concatenate Charge_Amount in a string
SQL = "Select Format([Charge_Amount],"Currency") as [Charge_Amount] from [QrytblProcedure]" & _
" where [Claim_Number]='" & rstUplo![Claim_Number] & "'"
rstDiag.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly
strDiag = ""
Do While Not rstDiag.EOF
strDiag = strDiag & " " & rstDiag![Charge_Amount]
rstDiag.MoveNext
Loop
' Remove leading comma and space from concatenated string
' and update temporary table with the string
strDiag = Mid(strDiag, 2)
rstTemp![Charge_Amount] = strDiag
rstDiag.Close
rstUplo.MoveNext
Loop
' Clean up
Set cnn = Nothing
Set rstUplo = Nothing
Set rstDiag = Nothing
Set rstTemp = Nothing
End Function
Last edited: