VBA with SQL Statement (Dynamic field names)

Susan Stevenson
Susan Stevenson used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try adding a couple of quotes:  "SELECT LkupQuoteData." & Currentfield  &"  FROM LkupQuoteData;"
I'd declare a string variable and resolve the SELECT LkupQuoteData." & Currentfield  &"  FROM LkupQuoteData into the variable the use that variable in the db.Execute


Kelvin

Author

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Can you advise what LkupQuoteData is - a table, view, function ......

Kelvin
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Remove the underscore:

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

/gustav
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
I've never seen this syntax.

you could do:

CurrentFieldValue = DLOOKUP(CurrentField, "LkupQuoteData")

or

set rs = db.openrecordset("SELECT ....")
CurrentFieldValue = iif(not rs.eof, rs(0), NULL)
rs.close
set rs = nothing
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Ah, true. Execute is for action queries.

/gustav

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial