Variables in SQL

[Bumpkin]

I am trying to update one table with a value from another table using the code below.
The first statement works and gets the value from the first table. the value is displayed correctly in the msgbox.
The SQL does not work as it treats the variant as a runtime input variable and asks for the value of M4KUSD.
How do I use the variant in the SQL?
Dim M4KUSD As Variant
Dim SQL As String
M4KUSD = DLookup("[exrate]", "pub_ccyex", "[toccykey] = 'USD' ")
SQL = "Update tbCurrencyRatesTEST Set Rate = M4KUSD where [CUR] = 'USD' "
Msgbox(M4KUSD)
DoCmd.RunSQL SQL

 

[pbaldy]

You have to concatenate the variable into the string (with delimiters if it's text):

SQL = "Update tbCurrencyRatesTEST Set Rate = '" & M4KUSD & "' where [CUR] = 'USD' "

If Rate is numeric, drop the single quotes.

 

[The_Doc_Man]

Dim M4KUSD As String
Dim SQL As String

    M4KUSD = CStr( DLookup("[exrate]", "pub_ccyex", "[toccykey] = 'USD' ") )
    SQL = "Update tbCurrencyRatesTEST Set Rate = " & M4KUSD & " where [CUR] = 'USD' ;"
    Msgbox(M4KUSD)
    DoCmd.RunSQL SQL

Might work better, particularly if [Exrate] is any form of numeric value - WORD, LONG, SINGLE, DOUBLE, CURRENCY, etc. If you find that you get too many decimal places out of the MK4USD then instead of CStr, use Format( value, "####.#####" ) or whatever other format is appropriate.

 

[MarkK]

Another option...

    Const SQL_UPDATE as string = _
        "UPDATE tbCurrencyRate " & _
        "SET Rate = prm0 " & _
        "WHERE [CUR] = 'USD' "

    With CurrentDb.CreateQueryDef("", SQL_UPDATE)
        .Parameters(0) = DLookup("exrate", "pub_ccyex", "toccykey = 'USD' ")
        .Execute dbFailOnError
        .Close
    End With

 

[Bumpkin]

Thanks for all the help guys.
I never stop learning new tricks in MS access

 

[pbaldy]

Happy to help!