Using SELECT in DoCmd.RunSQL

[Dom]

Dear Newsgroup,

I'd like to read data from a table into a variable in my
VB code using an SQL statement like:

"SELECT Field1 FROM table WHERE Field2 = 'MyValue';"

I don't seem to be able to make it work by typing

Docmd.RunSQL "SELECT Field1 FROM table WHERE Field2
= 'MyValue';"

as this raises an exception.

Can anyone point me in the right direction. By the way
Field1 and Field2 of the table are both of type Text.

Regards

Dom

 

[David Whitson]

Try this:

Docmd.RunSQL "SELECT Field1 FROM table WHERE Field2
= '" & MyValue & "';"

 

[Tom Ellison]

Dear Dom:

From online help, topic: RunSQL Method:

It uses an INSERT INTO, DELETE, SELECT...INTO, UPDATE, CREATE TABLE,
ALTER TABLE, DROP TABLE, CREATE INDEX, or DROP INDEX statement.

RunSQL does not work for SELECT queries (except SELECT INTO).

If you want to open a query datasheet, you must first save the query,
then use the OpenQuery method. It is best to save the query in the
front end to eliminate conflicts between users.

Dear Newsgroup,

I'd like to read data from a table into a variable in my
VB code using an SQL statement like:

"SELECT Field1 FROM table WHERE Field2 = 'MyValue';"

I don't seem to be able to make it work by typing

Docmd.RunSQL "SELECT Field1 FROM table WHERE Field2
= 'MyValue';"

as this raises an exception.

Can anyone point me in the right direction. By the way
Field1 and Field2 of the table are both of type Text.

Regards

Dom

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

 

[Van T. Dinh]

Check Access VB Help on the DLookUp() function.

HTH
Van T. Dinh
MVP (Access)

 

[Marty L]

I'm doing this where I want to store the date that I last performed a
function in a table in a parameter table:

=DLookUp("[field1]","table1","[field2]=""LookUpValue""")

 

[Dom]

Guys,

Thanks for all the feedback.
I now have plenty of alternative approaches.

Dom