Link to home
Start Free TrialLog in
Avatar of Susan Stevenson
Susan StevensonFlag for United Kingdom of Great Britain and Northern Ireland

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
Avatar of David Christal CISSP
David Christal CISSP

Try adding a couple of quotes:  "SELECT LkupQuoteData." & Currentfield  &"  FROM LkupQuoteData;"
SOLUTION
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Susan Stevenson

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
Can you advise what LkupQuoteData is - a table, view, function ......

Kelvin
Remove the underscore:

CurrentfieldValue = db.execute("SELECT LkupQuoteData." & Currentfield & " FROM LkupQuoteData;")

/gustav
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ah, true. Execute is for action queries.

/gustav