Using SELECT in DoCmd.RunSQL

  • Thread starter Thread starter Dom
  • Start date Start date
D

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
 
Try this:

Docmd.RunSQL "SELECT Field1 FROM table WHERE Field2
= '" & MyValue & "';"
 
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
Click to expand...

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
 
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""")
 
Back
Top