SEND SQL parameters to report

[jmsjazz]

I have an SQL statement which is dynamically built on a form.
It can be truncated to read
[Colour] = 'Blue'
or it can be left as a complete statement - SELECT * from tblOrders WHERE [Colour] = 'Blue'
The statements work when I open a form.
Is there a simple way to open a report (or query) with these parameters?
I have been strugglling to uderstand Querydefs, and wondered if there is a simpler way.

Thanks

 

[pbaldy]

I often use this method:

http://www.baldyweb.com/wherecondition.htm

 

[jmsjazz]

Thanks, but I need to use the strSQL variable, as I don't know what field(s) will be chosen. An example string might include, e.g.

[Colour] = 'Blue' AND [Year] = '2011' AND [OrderNo] = '1234'

 

[pbaldy]

You can use your variable, as long as it contains the correct syntax, like you posted.

 

[billmeye]

If you are opening the report using a button or some such from your form I'm guessing it is in a Macro? I'm really not familiar with the Macros, more used to using VBA. But in either case, you could use the OpenReport method and you can set a Where clause using your variables:

Docmd.OpenReport "rptOrders", acViewPreview, , "[Colour]='" & Me!cboColour & "'"

Or, you can set your Record Source of your report to have the Colour field Criteria reference your form: [forms]![frmOrders]![Colour]

This is essentially the same as creating a query as the underlying source for your report, but it is cleaner to not waste time with a query if all you are doing is filtering your report.

If this is not clear for you let me know and you could attach your DB and I could adjust it and send it back to you for an example.

 

[jmsjazz]

Many thanks - I didn't realise it worked for reports as well as forms!

 

[billmeye]

Your welcome.