Access2000: Modifying query through VBA

[Arvi Laanemets]

Hi

I have a query
qUserDevices1:
SELECT [qUserDevices0].[QueryDate], _
[qUserDevices0].[DeviceID],
LEFT([qUserDevices0].[DeviceID],2) AS DevGroup, _
[qUserDevices0].[TransactDate], _
[tblTransactions].[TabN], _
ValidValue("ctbUserDep",_
"TabN",_
[tblTransactions].[TabN],_
True,_
"DepID",_
"ValidFrom",_
[qUserDevices0].[QueryDate]) AS DepID, _
DLookup("OutlayCode",_
"tblDepartments",_
"DepID = '" & _
ValidValue("ctbUserDep",_
"TabN",_
[tblTransactions].[TabN],_
True,_
"DepID",_
"ValidFrom",_
[qUserDevices0].[QueryDate]) _
& "'") AS OutlayCode
FROM tblTransactions _
RIGHT JOIN qUserDevices0 _
ON ([tblTransactions].[DeviceID]=[qUserDevices0].[DeviceID]) _
AND
([tblTransactions].[TransactDate]=[qUserDevices0].[TransactDate]);


The query is used as a source for a report. The output of report may be
preview, printer, print file or excel file (when latter, then query result
table is saved as Excel table).

I want the user to be allowed to set filter on report - or rather to source
query. User can select the query field to be used for filtering from combo
box cbbFilterField, and accordingly what was selected, in another combo
cbbFilterValue user can select the filter value for this field. All fields
used for filtering are text ones.

cbbFilterField values may be: "All"; "OutlayCode"; "DepID", "DevGroup" and
"TabN". When "All" is selected, cbbFilterValue remains empty, other 4
selections for cbbFilterField match with field names in query.

The procedure which starts report, must at first to modify the SQL property
for qUserDevices1 - something like:
....
Select Case Me.cbbFilterField
Case "All"
' here the original SQL string must be restored
Me.Application.CurrentDb.QueryDefs("qUserDevices1").SQL = "..."
Case "TabN"
' here the filter condition must be added to original SQL string
' WHERE or HAVING clause with "[tblTransactions].[TabN] = '" &
Me.cbbFilterValue & "'"
Me.Application.CurrentDb.QueryDefs("qUserDevices1").SQL = "..."
...
End Select
....

My problem is, I'm not able to construct the SQL string in VBA properly -
both " and ' are used in original SQL string, and now I have to use a 3rd
level of quotations. Can someone help me with this!


Thanks in advance!

 

[Douglas J. Steele]

If you're using double quotes to delimit your strings, and you want to have
a double quote in your string, you either put two double quotes in a row, or
use Chr$(34). Both of the following will result in the string

I want "this" in my string

strText = "I want ""this"" in my string"

strText = "I want " & Chr$(34) & "this" & Chr$(34) & "in my string"

Similarly, if you're using single quotes to delimit your strings, and you
want to have a single quote (or apostrophe), you either put two single
quotes in a row, or use Chr$(39)

You might be interested in reading my May, 2004 "Access Answers" column in
Pinnacle Publication's "Smart Access". You can download the column (and
sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi

I have a query
qUserDevices1:
SELECT [qUserDevices0].[QueryDate], _
[qUserDevices0].[DeviceID],
LEFT([qUserDevices0].[DeviceID],2) AS DevGroup, _
[qUserDevices0].[TransactDate], _
[tblTransactions].[TabN], _
ValidValue("ctbUserDep",_
"TabN",_
[tblTransactions].[TabN],_
True,_
"DepID",_
"ValidFrom",_
[qUserDevices0].[QueryDate]) AS DepID, _
DLookup("OutlayCode",_
"tblDepartments",_
"DepID = '" & _
ValidValue("ctbUserDep",_
"TabN",_
[tblTransactions].[TabN],_
True,_
"DepID",_
"ValidFrom",_
[qUserDevices0].[QueryDate]) _
& "'") AS OutlayCode
FROM tblTransactions _
RIGHT JOIN qUserDevices0 _
ON ([tblTransactions].[DeviceID]=[qUserDevices0].[DeviceID]) _
AND
([tblTransactions].[TransactDate]=[qUserDevices0].[TransactDate]);


The query is used as a source for a report. The output of report may be
preview, printer, print file or excel file (when latter, then query result
table is saved as Excel table).

I want the user to be allowed to set filter on report - or rather to
source query. User can select the query field to be used for filtering
from combo box cbbFilterField, and accordingly what was selected, in
another combo cbbFilterValue user can select the filter value for this
field. All fields used for filtering are text ones.

cbbFilterField values may be: "All"; "OutlayCode"; "DepID", "DevGroup" and
"TabN". When "All" is selected, cbbFilterValue remains empty, other 4
selections for cbbFilterField match with field names in query.

The procedure which starts report, must at first to modify the SQL
property for qUserDevices1 - something like:
...
Select Case Me.cbbFilterField
Case "All"
' here the original SQL string must be restored
Me.Application.CurrentDb.QueryDefs("qUserDevices1").SQL = "..."
Case "TabN"
' here the filter condition must be added to original SQL string
' WHERE or HAVING clause with "[tblTransactions].[TabN] = '" &
Me.cbbFilterValue & "'"
Me.Application.CurrentDb.QueryDefs("qUserDevices1").SQL = "..."
...
End Select
...

My problem is, I'm not able to construct the SQL string in VBA properly -
both " and ' are used in original SQL string, and now I have to use a 3rd
level of quotations. Can someone help me with this!


Thanks in advance!

 

[Arvi Laanemets]

Thanks!

I thing I'm going for Chr$(34) - because there is concantenation (&) used in
sql string