CODEWITHSUNDEEP
sqlvbaexcel
Shania
Shania

Reputation: 2535

Using Input from Worksheet in a Query in a Macro

I am trying to take the percent column, column 3, from the "input" file and use that to take a discount on a price and put that price in the output file, one by one in column 7.

I pass the SQL connection to a function, a UPC value that looks up information in a database, and then the percent value (a Double) as input.

However, I keep getting the error "Incorrect syntax near the keyword percent". I have tried renaming the percent variable, and also using the Val() function (as you can see in the code).

If I take the Val() out, I get a type mismatch error

Basically: I cannot figure out how to use an input for "percent" such as .10 in my Query.

 For i = 1 To 381
     wrkb.Worksheets("Output").Cells(i + 1, 2).CopyFromRecordset extractInfo(cnn, wrkb.Worksheets("Input").Cells(i, 2).Value, Val(wrkb.Worksheets("Input").Cells(i, 3).Value))
Next i


Function extractInfo(cnn As ADODB.Connection, upc As String, percent As Double) As ADODB.Recordset

'Initializes variables

Dim rst As New ADODB.Recordset
Dim StrQuery As String

'The query to run, feed the UPC value you acquired to then get all the other variables
StrQuery = "SELECT 'N' as Division, zzeupcnr.style as Style, color_code as Color, ' ' as label_code, dimension as Dimension, ROUND((a_price * (1.00 - percent)), 2), ret_price " & _
        "FROM zzeupcnr JOIN zzxstylr " & _
        "ON zzeupcnr.style = zzxstylr.style " & _
        "WHERE upc = '" & upc & "'"

'Performs the actual query
rst.Open StrQuery, cnn

Set extractInfo = rst 'Stores result

Upvotes: 1

Views: 266

Answers (1)

addohm
addohm

Reputation: 2475

You're using percent in your query string as a string.

If you want to use the variable value, you need to do this:

StrQuery = "SELECT 'N' as Division, zzeupcnr.style as Style, color_code as Color, ' ' as label_code, dimension as Dimension, ROUND((a_price * (1.00 - " & percent & ")), 2), ret_price " & _
        "FROM zzeupcnr JOIN zzxstylr " & _
        "ON zzeupcnr.style = zzxstylr.style " & _
        "WHERE upc = '" & upc & "'"

Note the " & percent & "

Upvotes: 1

Related Questions