
Open Report with an SQL Criteria - Got It
A little new to this, so any discovered solution is extremely exciting - had
to share.
The source of the variable criteria is a form with a series of unbound
checkboxes and option groups, which when selcted (or not) give me a sequence
of true and/or false which defines an If statement which attaches a preset
(in code) text to the existing SQL text string.
It builds up an SQL Where statement (strWhere) (ie WHERE Disposition = S{*filter*}
And DateClosed = Null, ORDER BY DateIn DESC...etc.) which I Attach to the
existing SQL of the Undelying Query of the Report (ie. QueryDef.SQL &
strWhere).
Then I use DoCmd.OpenReport to get the records selected,
Then reset the original SQL string to the QueryDef (Which I had saved/held).
Very Cool!!
One Query to drive several reports whose criteria is based on different
users criteria forms.
I love this stuff, wish I had started 5 years ago not 6 months ago. Would do
it for free (in fact I am) but hopefully not for to much longer.
Joe
Quote:
>I'm close but I seem to be missing something.
>I have a function which builds an SQL string based on a user select option
>form.
>I then want to open a report, which is based on a query, with the SQL
string
>passed to the command used to open the report.
>I know I should (or believe I should) use "DoCmd.OpenReport"
>but I am having trouble with getting it to work.
>It's probably a syntax problem.
>I have verified that the SQL is correct by using a SQL scripter utility
>(provided with the VB Designer Handbook).
>The SQL string includes some ORDER commands, if this is any issue.
>Thanks for all help, in advance,
>Joe