Trouble converting SQL to VBA

Hi folks,

I'm a complete newbie with Access, but I have a great deal of experience in using VBA with Excel. However, I've realised that one of my Excel toys needs to be converted to Access owing to the volume of data it holds.

As such, yesterday I started to "play" with Access and built a couple of tables, queries and forms. On one of the forms I have a ComboBox (called startDate), I've added an after update event to this to trigger a macro to search for a record in a table called shiftTable, and return 3 pieces of data when it locates the record.

I built the query to do this using the Access query design which works correctly, and then grabbed the SQL to use in the macro, this is shown below.


This is the macro that I've built around that SQL....


However, every time I run it I get a 3075 missing operator error (in this example I selected 27/01/2011 in the startDate ComboBox);

Syntax error(missing operator) in query expression
'shiftTable.nsWatchFROM shiftTable WHERE
shiftTable.activityDate=#27/01/2011#'.

Click to expand...

I've tried various things to fix it, including converting the date to US format, putting a space in front of the FROM command, declaring stDate as string instead of date. I'm getting frustrated now, especially as I suspect I'm missing something very obvious. All help appreciated!

EDIT:

As an experiment, I just chucked a msgBox in before the DoCmd statement to show me the value of ssqlCom, which returns this:

SELECT shiftTable.amWatch, shiftTable.pmWatch, shiftTable.nsWatchFROM shiftTable WHERE shiftTable.activityDate=#27/01/2011#;

Click to expand...

Thanks,

James.

I also susspect that you need to format your datevariable to US format "mm/dd/yyyy"


JR

Was a bit quick there with regards to

DoCmd.OpenQuery (ssqlCom)

would not work here, but you can assign ssqlCom to a recordsource or you can modify an existing query with this sting using QueryDefs collection.

What you can't do is RUN a Select query that is reserved for Action queries like Update, Append, Delete.

JR

JANR said:

What you can't do is RUN a Select query that is reserved for Action queries like Update, Append, Delete.

JR

Click to expand...

Cheers, that explains it. I've altered the way I'm doing it now, so instead have used a Dlookup as I needed to use the returned data to populate a textbox on a form, and figured that was simplest.

Thanks,

James.

To correct this post, you can do a SELECT query with DoCMD.RUNSQL, it just has to be done into a new table. For instance:
DoCMD.RUNSQL "SELECT * FROM table1" .....will not work.....
DoCMD.RUNSQL "SELECT * INTO table2 FROM table1" .....will work.....

To correct this post, you can do a SELECT query with DoCMD.RUNSQL, it just has to be done into a new table. For instance:
DoCMD.RUNSQL "SELECT * FROM table1" .....will not work.....
DoCMD.RUNSQL "SELECT * INTO table2 FROM table1" .....will work.....

Click to expand...

http://www.access-programmers.co.uk/forums/showpost.php?p=1058967&postcount=5
JR