Building a SQL string

[DZ]

The project manager want the user to select names using checkboxes.
Then run a report with those names as filter.

I was thinking of accomplishing this by using DAO to loop through the
records and a building a WHERE clause to tack onto a query with something
like the following


If ckFilter = -1 then
strWhere = strWhere & " Or Name = '" & rs(Name).Value & "'"
End if

I was just checking if anyone out there might use different method to
filter a report based on checked off items.

Thanks for any ideas

 

[sfisher973]

The project manager want the user to select names using checkboxes.
Then run a report with those names as filter.

I was thinking of accomplishing this by using DAO to loop through the
records and a building a WHERE clause to tack onto a query with something
like the following


If ckFilter = -1 then
strWhere = strWhere & " Or Name = '" & rs(Name).Value & "'"
End if

I was just checking if anyone out there might use different method to
filter a report based on checked off items.

Thanks for any ideas

Well, from the rs(name) syntax it look like you have a table of names and
checkboxes that you're using to select the names from... If that is the case
then your where clause could just be a sub-query as in:

SQL = "SELECT ReportData FROM ReportDataTableQuery WHERE
ReportDataTableQuery.Name IN (SELECT UserName FROM NamesTable WHERE
CheckFilter = -1)

IN can also be used if you just have a finite number of names instead of
OR'ing them together you can instead build a string of comma separated names

so that the WHERE would look like: "WHERE Name IN ('Jones','Smith','Johnson')

Look up the IN Operator in Microsoft Access Help (Not VBA Help) Also
'Subqueries'

Hope this helps...
-Steve