Help! with query criteria for report

  • Thread starter Thread starter Abbey Normal
  • Start date Start date
A

Abbey Normal

Hi I have a report which also prints another report within it.
I print the report initally from a form creates the criteria string, which
looks like this:
current = true and SKU = '12345678' or SKU = '23456789'
So when I click on print to print the report, it does this:
stWhere = Criteria
DoCmd.OpenReport StDocName, AcNormal,,stWhere
[this report has a record source of qrySpec]
My report-within-the-report, has a record source of qrySpecAuditLogs, which
looks like:
SELECT TOP 1 Auditlog.sku, Auditlog.reason, FROM AuditLog
WHERE AuditLog.sku = '06366036'
ORDER BY AuditLog.chgdate DESC;

Is there any way to change (qrySpecAuditLogs) so it can use the same
criteria that I created above? Perhaps I could do it before it performs the
DoCmd.OpenReport?
Thanks for your help
 
Create an unbound form with unbound text boxes to enter the criteria into.
You can set a default on the text boxes for most often used criteria.
 
Firstly the filter example you gave mixes AND and ORs. It is really
important to use brackets here, because you will give *different* records
from these two filters:
(current = true) and (SKU = '12345678' or SKU = '23456789')
(current = true and SKU = '12345678') or SKU = '23456789'
Both have to be current to match the first filter;
2345679 is included in the 2nd one whether it's current or not.

Regarding the subreport, it might be easiest to set its source query to read
the filter criteria directly from your form, e.g.:
[Forms].[Form1].[Text0]

If you can't do that, it is possible to re-write the SQL of the subreport's
query before you OpenReport:
Dim strSql As String
strSQL = "SELECT ... FROM ... WHERE ...
CurrentDb.QueryDefs("Query1").SQL = strSql
 
Back
Top