VBA: Update SQL via GUI

M

Mike

Hi everyone, I've been fiddling around with something for an interface for my
users. I have the following query in MS Access 2003:
SELECT qryACRCNHW.CASE_NUMBER, qryACRCNHW.DEFAULT_SEVERITY,
qryACRCNHW.FIELD_MODIFIED, qryACRCNHW.OLD_VALUE, qryACRCNHW.NEW_VALUE,
[TIME_ACTION_TAKEN]-isDST() AS TimeEST
FROM qryEmpList RIGHT JOIN (tblAcceptExc RIGHT JOIN qryACRCNHW ON
tblAcceptExc.REMTicket = qryACRCNHW.CASE_NUMBER) ON qryEmpList.Login =
qryACRCNHW.NEW_VALUE
GROUP BY qryACRCNHW.CASE_NUMBER, qryACRCNHW.DEFAULT_SEVERITY,
qryACRCNHW.FIELD_MODIFIED, qryACRCNHW.OLD_VALUE, qryACRCNHW.NEW_VALUE,
[TIME_ACTION_TAKEN]-isDST(), qryACRCNHW.ID, qryACRCNHW.SUPPORT_GROUP,
tblAcceptExc.REMTicket, qryEmpList.[Job Title]
HAVING (((qryACRCNHW.FIELD_MODIFIED)="ASSIGNEE") AND
(([TIME_ACTION_TAKEN]-isDST()) Between #11/1/2006# And #11/8/2006#) AND
((tblAcceptExc.REMTicket) Is Null) AND ((qryEmpList.[Job Title])="L2")) OR
(((qryACRCNHW.FIELD_MODIFIED)="SUPPORTGROUP") AND
((qryACRCNHW.NEW_VALUE)="DESKTOP-NONHARDWARE-OPER") AND
(([TIME_ACTION_TAKEN]-isDST()) Between #11/1/2006# And #11/8/2006#) AND
((tblAcceptExc.REMTicket) Is Null)) OR (((qryACRCNHW.NEW_VALUE)="SUBMIT") AND
(([TIME_ACTION_TAKEN]-isDST()) Between #11/1/2006# And #11/8/2006#) AND
((tblAcceptExc.REMTicket) Is Null)) OR (((qryACRCNHW.NEW_VALUE)="RE-OPENED")
AND (([TIME_ACTION_TAKEN]-isDST()) Between #11/1/2006# And #11/8/2006#) AND
((tblAcceptExc.REMTicket) Is Null))
ORDER BY qryACRCNHW.ID, qryACRCNHW.CASE_NUMBER, [TIME_ACTION_TAKEN]-isDST();

As you can see I have some 'between' statements. These statements need to be
updated everytime I run the report, I would like to build an interface just
to change this. So I figured, I'll just create a function/procedure in VBA
that uses the DoCmd.RunSQL and add some string variables, but when I put in
the above SQL I get an error right off the bat saying its not a valid SQL
string. I'm 98% sure that it is because I'm calling custom functions in some
of the calculated fields. Is there another way of doing this w/o getting rid
of my custom functions?

Thanks everyone,
 
A

Allen Browne

A simple solution would be to drop the dates out of the query.
Instead, apply them as the WhereCondition of OpenReport.
There's an example of how to do that here:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html


If that doesn't appeal to you, you can build the string up in 3 parts: the
unchanging first part (the stub), and unchanging last part (the tail), and
the changing bit in the middle. You can simplify this since the phrase:
([TIME_ACTION_TAKEN] - isDST() Between #11/1/2006# And #11/8/2006#)
is repeated in every one of the four OR blocks of your having clause.
You could therefore re-write this as:
HAVING (([TIME_ACTION_TAKEN] - isDST() Between #11/1/2006# And
#11/8/2006#) AND (this OR that OR the other))

Now there is only *one* spot in the string where the dates have to be
concatenated, you can split the string into 3 parts:
- the stub of the string (everything up to HAVING, which stays the same);
- the tail of the string (the rest of the HAVING and ORDER BY clauses, after
the dates);
- the bit that changes in the middle.

Dim strStub As String
Dim strTail As String

strStub = "SELECT ... FROM ... GROUP BY ... " & _
"HAVING ((tblAcceptExc.REMTicket Is Null) AND ([TIME_ACTION_TAKEN] -
isDST() Between "

strTail = " AND (((qryACRCNHW.FIELD_MODIFIED = "ASSIGNEE" ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Mike said:
Hi everyone, I've been fiddling around with something for an interface for
my
users. I have the following query in MS Access 2003:
SELECT qryACRCNHW.CASE_NUMBER, qryACRCNHW.DEFAULT_SEVERITY,
qryACRCNHW.FIELD_MODIFIED, qryACRCNHW.OLD_VALUE, qryACRCNHW.NEW_VALUE,
[TIME_ACTION_TAKEN]-isDST() AS TimeEST
FROM qryEmpList RIGHT JOIN (tblAcceptExc RIGHT JOIN qryACRCNHW ON
tblAcceptExc.REMTicket = qryACRCNHW.CASE_NUMBER) ON qryEmpList.Login =
qryACRCNHW.NEW_VALUE
GROUP BY qryACRCNHW.CASE_NUMBER, qryACRCNHW.DEFAULT_SEVERITY,
qryACRCNHW.FIELD_MODIFIED, qryACRCNHW.OLD_VALUE, qryACRCNHW.NEW_VALUE,
[TIME_ACTION_TAKEN]-isDST(), qryACRCNHW.ID, qryACRCNHW.SUPPORT_GROUP,
tblAcceptExc.REMTicket, qryEmpList.[Job Title]
HAVING (((qryACRCNHW.FIELD_MODIFIED)="ASSIGNEE") AND
(([TIME_ACTION_TAKEN]-isDST()) Between #11/1/2006# And #11/8/2006#) AND
((tblAcceptExc.REMTicket) Is Null) AND ((qryEmpList.[Job Title])="L2")) OR
(((qryACRCNHW.FIELD_MODIFIED)="SUPPORTGROUP") AND
((qryACRCNHW.NEW_VALUE)="DESKTOP-NONHARDWARE-OPER") AND
(([TIME_ACTION_TAKEN]-isDST()) Between #11/1/2006# And #11/8/2006#) AND
((tblAcceptExc.REMTicket) Is Null)) OR (((qryACRCNHW.NEW_VALUE)="SUBMIT")
AND
(([TIME_ACTION_TAKEN]-isDST()) Between #11/1/2006# And #11/8/2006#) AND
((tblAcceptExc.REMTicket) Is Null)) OR
(((qryACRCNHW.NEW_VALUE)="RE-OPENED")
AND (([TIME_ACTION_TAKEN]-isDST()) Between #11/1/2006# And #11/8/2006#)
AND
((tblAcceptExc.REMTicket) Is Null))
ORDER BY qryACRCNHW.ID, qryACRCNHW.CASE_NUMBER,
[TIME_ACTION_TAKEN]-isDST();

As you can see I have some 'between' statements. These statements need to
be
updated everytime I run the report, I would like to build an interface
just
to change this. So I figured, I'll just create a function/procedure in VBA
that uses the DoCmd.RunSQL and add some string variables, but when I put
in
the above SQL I get an error right off the bat saying its not a valid SQL
string. I'm 98% sure that it is because I'm calling custom functions in
some
of the calculated fields. Is there another way of doing this w/o getting
rid
of my custom functions?

Thanks everyone,
Click to expand...
 
J

John Spencer

You could just use a simple parameter query or you could use a form and
controls for the dates and run the report from a button on the form.

Parameters [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT qryACRCNHW.CASE_NUMBER
, qryACRCNHW.DEFAULT_SEVERITY
, qryACRCNHW.FIELD_MODIFIED
, qryACRCNHW.OLD_VALUE
, qryACRCNHW.NEW_VALUE
, [TIME_ACTION_TAKEN]-isDST() AS TimeEST
FROM qryEmpList RIGHT JOIN (tblAcceptExc RIGHT JOIN qryACRCNHW ON
tblAcceptExc.REMTicket = qryACRCNHW.CASE_NUMBER)
ON qryEmpList.Login = qryACRCNHW.NEW_VALUE

WHERE
(((qryACRCNHW.FIELD_MODIFIED)="ASSIGNEE") AND
(([TIME_ACTION_TAKEN]-isDST()) Between [Enter Start Date] And [Enter End
Date]) AND
((tblAcceptExc.REMTicket) Is Null)
AND ((qryEmpList.[Job Title])="L2"))
OR
(((qryACRCNHW.FIELD_MODIFIED)="SUPPORTGROUP") AND
((qryACRCNHW.NEW_VALUE)="DESKTOP-NONHARDWARE-OPER") AND
(([TIME_ACTION_TAKEN]-isDST()) Between [Enter Start Date] And [Enter End
Date]) AND
((tblAcceptExc.REMTicket) Is Null))
OR
(((qryACRCNHW.NEW_VALUE)="SUBMIT") AND
(([TIME_ACTION_TAKEN]-isDST()) Between [Enter Start Date] And [Enter End
Date]) AND
((tblAcceptExc.REMTicket) Is Null))
OR
(((qryACRCNHW.NEW_VALUE)="RE-OPENED") AND
(([TIME_ACTION_TAKEN]-isDST()) Between [Enter Start Date] And [Enter End
Date]) AND
((tblAcceptExc.REMTicket) Is Null))

GROUP BY qryACRCNHW.CASE_NUMBER
, qryACRCNHW.DEFAULT_SEVERITY
, qryACRCNHW.FIELD_MODIFIED
, qryACRCNHW.OLD_VALUE
, qryACRCNHW.NEW_VALUE
, [TIME_ACTION_TAKEN]-isDST()
, qryACRCNHW.ID
, qryACRCNHW.SUPPORT_GROUP
, tblAcceptExc.REMTicket
, qryEmpList.[Job Title]

ORDER BY qryACRCNHW.ID, qryACRCNHW.CASE_NUMBER, [TIME_ACTION_TAKEN]-isDST();

If you still want to do this by writing an SQL statement with VBA code, then
some suggestions that may help.
First - replace all the quotation marks with apostrophes. " to '
Second - make sure if you are putting in date strings with your functions
that you have # marks as delimiters around the literal strings
Third - make sure you have spaces in all the right places

Instead of running the query, use
Debug.Print YourSQLString

and then copy and paste that into a new query. Try to run it. If it fails
you will get a better error message and a good chance of the cursor moving
to the location of the error in the SQL.

Some other advice is to change the HAVING clause to a WHERE clause to
improve the efficiency of the query.

I suggest you post your entire procedure that is constructing the query if
you want someone to help you troubleshoot the code.

Mike said:
Hi everyone, I've been fiddling around with something for an interface for
my
users. I have the following query in MS Access 2003:
SELECT qryACRCNHW.CASE_NUMBER, qryACRCNHW.DEFAULT_SEVERITY,
qryACRCNHW.FIELD_MODIFIED, qryACRCNHW.OLD_VALUE, qryACRCNHW.NEW_VALUE,
[TIME_ACTION_TAKEN]-isDST() AS TimeEST
FROM qryEmpList RIGHT JOIN (tblAcceptExc RIGHT JOIN qryACRCNHW ON
tblAcceptExc.REMTicket = qryACRCNHW.CASE_NUMBER) ON qryEmpList.Login =
qryACRCNHW.NEW_VALUE
GROUP BY qryACRCNHW.CASE_NUMBER, qryACRCNHW.DEFAULT_SEVERITY,
qryACRCNHW.FIELD_MODIFIED, qryACRCNHW.OLD_VALUE, qryACRCNHW.NEW_VALUE,
[TIME_ACTION_TAKEN]-isDST(), qryACRCNHW.ID, qryACRCNHW.SUPPORT_GROUP,
tblAcceptExc.REMTicket, qryEmpList.[Job Title]
HAVING (((qryACRCNHW.FIELD_MODIFIED)="ASSIGNEE") AND
(([TIME_ACTION_TAKEN]-isDST()) Between #11/1/2006# And #11/8/2006#) AND
((tblAcceptExc.REMTicket) Is Null) AND ((qryEmpList.[Job Title])="L2")) OR
(((qryACRCNHW.FIELD_MODIFIED)="SUPPORTGROUP") AND
((qryACRCNHW.NEW_VALUE)="DESKTOP-NONHARDWARE-OPER") AND
(([TIME_ACTION_TAKEN]-isDST()) Between #11/1/2006# And #11/8/2006#) AND
((tblAcceptExc.REMTicket) Is Null)) OR (((qryACRCNHW.NEW_VALUE)="SUBMIT")
AND
(([TIME_ACTION_TAKEN]-isDST()) Between #11/1/2006# And #11/8/2006#) AND
((tblAcceptExc.REMTicket) Is Null)) OR
(((qryACRCNHW.NEW_VALUE)="RE-OPENED")
AND (([TIME_ACTION_TAKEN]-isDST()) Between #11/1/2006# And #11/8/2006#)
AND
((tblAcceptExc.REMTicket) Is Null))
ORDER BY qryACRCNHW.ID, qryACRCNHW.CASE_NUMBER,
[TIME_ACTION_TAKEN]-isDST();

As you can see I have some 'between' statements. These statements need to
be
updated everytime I run the report, I would like to build an interface
just
to change this. So I figured, I'll just create a function/procedure in VBA
that uses the DoCmd.RunSQL and add some string variables, but when I put
in
the above SQL I get an error right off the bat saying its not a valid SQL
string. I'm 98% sure that it is because I'm calling custom functions in
some
of the calculated fields. Is there another way of doing this w/o getting
rid
of my custom functions?

Thanks everyone,
Click to expand...
 
Top