Using query SQL as source for SQL in VBA qdf's

[Guest]

My Access VBA Manual (Smith and Sussman) suggests that a shortcut to writing
SQL strings for a novice is to use the qbe grid to design the required query
and then copy/pasting from SQL view into a vba procedure to create something
like strSQL = "pasted bit" followed by qdf.SQL = strSQL ( having 'dimmed' and
'set' qdf etc as required and having sorted out line breaks, quotes etc in
the pasted text.
However this rarely works and produces error messages about syntax and/or
missing bits, even though it compiles successfully. My Question is - is there
a good source of information about the differences in syntax rules between
the SQL in " SQLview" and SQL used in vba procedures? I am using Access 2003
with DAO.

 

[Douglas J. Steele]

There is absolutely no difference between the SQL in SQL View and that used
in VBA procedures.

Odds are your SQL is incorrect in the VBA: either you're forgetting to add
spaces between terms (especially easy to do if you're continuing from one
line to another), or you're doing something like including variable or
control names inside your SQL.

Paste a sample of one of the bits that isn't working, and we'll see what we
can see.

 

[Guest]

Thanks for your quick response. You were spot on - I had missed some trailing
spaces just before line breaks. Now I'm really motoring, having previosly
learnt how to use variables and controls like listbox input in my SQL strings.