Help - add a format to the Sql select for currency

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

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:
Have you tried this yet?

Code:
SELECT Format([tblProcedure]![Charge_Amount],"Currency") AS ...
 
So like this:

SQL = "Select Format([tblProcedure]![Charge_Amount],"Currency") AS [Charge_Amount] from [tblProcedure]" & _
" where [Claim_Number]='" & rstUplo![Claim_Number] & "'"
 
I don't think ADO understands what the "Currency" format is. Don't remember but I don't think it does. DAO should be fine. In any case you can try:
Code:
Select Format([tblProcedure]![Charge_Amount],"$0.00") AS [Charge_Amount] ...etc
If that doesn't work, create a query that uses the Format("Currency") function, save this query and use the query name in a new query as the source of your recordset.
 
I am getting an Compile error: Expected: end of statement when I use "Currency" and Compile error: Invalid character when I use "$0.00"

Code:
' Concatenate Charge_Amount in a string
      SQL = "Select Format([Charge_Amount],"Currency") as [Charge_Amount] from [QrytblProcedure]" & _
          " where [Claim_Number]='" & rstUplo![Claim_Number] & "'"
 
It is returning the correct strings without formatting with this code

So if my data was as follows:

201
144
10

It is returning

201 144 10

I need it to be $201 $144 $10

Code:
' Concatenate Charge_Amount in a string
SQL = "Select [Charge_Amount] from [QrytblProcedure]" & _
" where [Claim_Number]='" & rstUplo![Claim_Number] & "'"

It gives me a Syntax error when I run this code and doesn't return anything.

Code:
' Concatenate Charge_Amount in a string
SQL = "Select Format([Charge_Amount],"Currency") as [Charge_Amount] from [QrytblProcedure]" & _
" where [Claim_Number]='" & rstUplo![Claim_Number] & "'"
 
I am getting an Compile error:
since you are creating a string

"Select Format([Charge_Amount],"Currency") as ..."

You need to use single quotes around currency, not double quotes otherwise vba "thinks" the string ends after the comma
 
I think you misunderstand my question. Print the SQL to the Immediate Window and paste it here.
 

Users who are viewing this thread

Back
Top Bottom