Syntax for programmatically writing queries

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

Every time I try to write one of these I find myself
pulling my hair our trying to figure out where I need
quotation marks, single quotes, etc.

Anyone know where I might be able to find some sort of
syntax diagram or documentation on what needs to go where
and how?

More often than not unless the query is very simple and
straight forward I end up simply creating a query and
calling it programmatically. This seems to really clutter
things up unnecessarily. I don't have access to any good
Access reference materials beyond the web.

Thanks and Happy/safe fourth!

Rick
 
Hi,


If it is a query without embedded parameter value,


Type the final text you need.

Double each double-quote.


Add a double-quote at the start, and one at the end.


Ex.:

SELECT * FROM mt WHERE f1="January";


would become:


str="SELECT * FROM mt WHERE f1=""January""; "



(the ending ; is not mandatory, in JET, so you may end up with three " ).



Same thing if the string constant already contain a double quote. as
example, for 4'-6", you will type:


SELECT * FROM mt WHERE f1="4'-6""";

(we doubled the " for inch, in 6", and add one at the start and one at the
end) so

str="SELECT * FROM mt WHERE f1=""4'-6"""""";"

where we double every " , and add one at the start, and one at the end. If
you removed the ;, that makes 7 double quotes in succession.



Hoping it may help,
Vanderghast, Access MVP
 
Great! Thanks! Will give it a try. BUT, what if it is
WITH embedded parameter value? I do this frequently.

Rick
 
Hi,


If you got the 4'-6" right, you won't have a problem.

Assuming you want the final SQL statement:

SELECT ... WHERE f1="value_here" ;


As a string, that would be

str= "SELECT ... WHERE f1=""value here"" ; "


Now, if the value here is to be read from a variable, we have to cut
open the string in three parts: a first string that precedes, the variable,
the last string that ends. We glue the three parts with &. To cut a string
in two, we just add a " at the end of the first part, and add a " at the
beginning of the second part:

str= "my full string"
str= "my fu" & "ll string"


are two logically equivalent statements. So, lets us break our initial
string:

str= "SELECT ... WHERE f1=""value here"" ; "

to

str="SELECT ... WHERE f1=""" & my_variable_here & """; "


if you prefer, using b to clearly show a space, we have inserted "b&b
before and b&b" after the value that becomes a variable.


In the end, you can also use


Debug.Print str

to see, in the Immediate Debug Window, if the string you got is exactly the
SQL statement you expected, right at the beginning of the process.




Hoping it may help,
Vanderghast, Access MVP
 
You make it seem so simple! ;) and I am sure that it is.
It is just a matter of taking those couple simple rules
and applying them to some very complex expressions.
Sounds much like the use of "(" yet it never fails I find
myself staring at a complex statement trying to figure out
where I forgot one or have an extra.

I certainly appreciate your example. I am sure if I apply
those rules carefully I will not go wrong. But I will
likely violate them stupidly for some time before the
light comes on.

I began to attempt to apply this to a simple query I had
written that finds a specific record in a table and
appends it to another table. Written and a "query"
object, in the designer it looks pretty simple. 4 fields,
one of them a simple "Now() calculation.

SO I am venturing into turning this thing into code. For
my application my intention is to import some records from
a text file. One of those records will contain summary
info on the rest. I want to keep a log table of those
summaries. SO I first find the summary record and append
it to my log table. My simple venture starts like this
(from the 4 field append query)

INSERT INTO tblImportLog (TotalRecords, TotalImported,
DateImported )
SELECT [Import destination table].CCODE, [Import
destination table].[Amount], Format(Now(),"mm-dd-yy") AS
dte
FROM [Import destination table]
WHERE ((([Import destination table].[ACCT-NO])="Total"));

(Not asking you to convert, just showing you where I will
be taking my first steps from;)

The rules don't seem SO simple, and the rest gets worse!

I am sure you have equiped me with the tools for the task!
And for that I thank you!

I really appreciate you taking the time to steer me in the
right direction on this!

Rick
 
Hi,


Just in case you are close to give up, the solution seems to be (page
down, just in case you don't want to see it NOW... )





























Dim str As string

str= "INSERT INTO tblImportLog (TotalRecords, TotalImported, " & _
" DateImported ) SELECT [Import destination table].CCODE, " & _
" [Import destination table].[Amount], " & _
" Format(Now(),""mm-dd-yy"") AS dte " & _
" FROM [Import destination table] " & _
" WHERE ((([Import destination table].[ACCT-NO])=""Total"")); "

' Debug.Print str ' debug stage


Note that I broke the statement in multiple lines, to make the code more
readable, in VBA, but then, a point to remember is to start new lines with a
space, or else, a keyword, like WHERE, risks to be glued to the last part of
the FROM clause, and makes a syntax error.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top