How to code a SQL statement 2

[annsolomon]

I've shied away from coding SQL statements and have always used queries instead. I'd like to learn how but got Error #3075 (syntax error/missing operator) on my first attempt:

DoCmd.RunSQL "SELECT DISTINCT qryScheduleAppt.WONumber, qryScheduleAppt.ContactID" _
& "FROM qrySchedule INNER JOIN qryScheduleAppt ON" _
& "qrySchedule.ContactID = qryScheduleAppt.ContactID;"

I'd really appreciate it if someone could get me started and point out what I'm doing wrong here.

Ann

 

[oharab]

When debugging SQL statements I usually debug.print them so I can see what is being passed around.
In your case, you have not put any spaces or line breaks at the end of your lines so your statement is:

SELECT DISTINCT qryScheduleAppt.WONumber, qryScheduleAppt.ContactIDFROM qrySchedule INNER JOIN qryScheduleAppt ONqrySchedule.ContactID = qryScheduleAppt.ContactID;


your code should either be:
DoCmd.RunSQL "SELECT DISTINCT qryScheduleAppt.WONumber, qryScheduleAppt.ContactID" & vbcrlf & _
& "FROM qrySchedule INNER JOIN qryScheduleAppt ON" & vbcrlf _
& "qrySchedule.ContactID = qryScheduleAppt.ContactID;"

or
DoCmd.RunSQL "SELECT DISTINCT qryScheduleAppt.WONumber, qryScheduleAppt.ContactID " _
& "FROM qrySchedule INNER JOIN qryScheduleAppt ON " _
& "qrySchedule.ContactID = qryScheduleAppt.ContactID;"

Incidently, you are usually better off using queries, rather than SQL statments where possible. When you run a query it is optimized by Access to make it run faster. This optimization is saved by access when you save a query, but will have to be done each time you send an SQL statement, making things just that little bit slower.

Cheers

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[annsolomon]

Thanks for the advice about queries vs coding SQL.

I decided on using spaces instead of the crlf option but now I get Error #2342 (a RunSQL action requires an argument consisting of a SQL statement). This error has me totally clueless.

Ann

 

[oharab]

oh yeah! Didn't occur to me!
Docmd.RunSQL requires an action query. It does something like update records, deletes records, creates a table, etc.
AFAIK there is no way of opening up an sql statement for view.
You will need to save the SQL as a query, like you did before and then use docmd.openquery to display the results.

Cheers

ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[annsolomon]

Thanks for helping out Ben. I really appreciate it.

Ann

 

[annsolomon]

Ben gave me several pieces of helpful info and advice and I'd like to award a star but my browser is acting up. Is there some kind soul out there that wouldn't mind posting a star on my behalf? Thanks.

Ann