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
Microsoft AccessVisual Basic ClassicVB Script
Last Comment
Gustav Brock
Try adding a couple of quotes: "SELECT LkupQuoteData." & Currentfield &" FROM LkupQuoteData;"
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Ah, true. Execute is for action queries.
/gustav
/gustav