Susan Stevenson
asked on
VBA with SQL Statement (Dynamic field names)
I have the below variables but the SQL statement does not work. Getting Runtime 438 - object does not support this property or method.
Currentfield ="quote_id"
CurrentfieldValue = db.execute_("SELECT LkupQuoteData. & Currentfield & FROM LkupQuoteData;")
When hovering over the SQL statement, it is recognising the quote_id as the field name to use and the underlying query is of the same field name.
Any help would be appreciated
Thanks,
Susan
Currentfield ="quote_id"
CurrentfieldValue = db.execute_("SELECT LkupQuoteData. & Currentfield & FROM LkupQuoteData;")
When hovering over the SQL statement, it is recognising the quote_id as the field name to use and the underlying query is of the same field name.
Any help would be appreciated
Thanks,
Susan
Try adding a couple of quotes: "SELECT LkupQuoteData." & Currentfield &" FROM LkupQuoteData;"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for responding so quickly..
test = "SELECT LkupQuoteData." & Currentfield & " FROM LkupQuoteData;"
MsgBox test - this works as I would expect it to returning the string of the sql statement
CurrentfieldValue = db.execute_(test) - This returns the same error as previous :-(
When I run the SQL statement via query wizard, it returns the value I want
test = "SELECT LkupQuoteData." & Currentfield & " FROM LkupQuoteData;"
MsgBox test - this works as I would expect it to returning the string of the sql statement
CurrentfieldValue = db.execute_(test) - This returns the same error as previous :-(
When I run the SQL statement via query wizard, it returns the value I want
Can you advise what LkupQuoteData is - a table, view, function ......
Kelvin
Kelvin
Remove the underscore:
CurrentfieldValue = db.execute("SELECT LkupQuoteData." & Currentfield & " FROM LkupQuoteData;")
/gustav
CurrentfieldValue = db.execute("SELECT LkupQuoteData." & Currentfield & " FROM LkupQuoteData;")
/gustav
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ah, true. Execute is for action queries.
/gustav
/gustav