Use 1st query to populate fields then use 2nd query to filter fields (1 Viewer)

[deanvilar]

Hello Gurus, is there a way in report that I have 2 reference queries just 1 is to populate all details and 2nd query to filter details and will be the final reporting information???

 

[Beetle]

You don't need a second query to filter results, you just need some criteria that can be applied as a filter on the original recordset. This criteria could be applied in the first query, it could be applied during the OpenReport method, etc.

 

[deanvilar]

can you assist me on this sir @Beetle? I have this query

SELECT *
FROM assetMovementTable
WHERE ((assetMovementTable.toJobCode)=Forms!ASSET_MOVEMENT_PRINT_MODULE!txtJobCode) And ((assetMovementTable.toDate Between Forms!ASSET_MOVEMENT_PRINT_MODULE!txtFromDate And Forms!ASSET_MOVEMENT_PRINT_MODULE!txtToDate)
Or
(assetMovementTable.toJobCode)=Forms!ASSET_MOVEMENT_PRINT_MODULE!txtJobCode And Reports!rptAssetMovementByJobCode!fromEndDate.value Is Null) 
Or
((assetMovementTable.toJobCode)=Forms!ASSET_MOVEMENT_PRINT_MODULE!txtJobCode And Reports!rptAssetMovementByJobCode!fromEndDate>=Forms!ASSET_MOVEMENT_PRINT_MODULE!txtFromDate<=Forms!ASSET_MOVEMENT_PRINT_MODULE!txtToDate) 
ORDER BY assetMovementTable.assetNo, assetMovementTable.moveCode;

then I want to Filter my report when released (text box from report) = NO
NOTE: released (text box from report) is not a field from my table I just coded it from detail format event of my report ....

 

[Beetle]

I'm not sure I follow what you're trying to do here. Reports are a static (read only) display of data, so any criteria needed for filtering should be applied before the report is opened.

 

[deanvilar]

Yes ... that's correct .... the real problem am facing is that I put this value i created in query which is not reading because am not pretty sure if query comes first before having the detail format event of my report

 

[Beetle]

You cannot have a query try to retrieve criteria from a report that uses that same query as its record source because, as you guessed, the query needs the criteria before the report is opened. You will need to supply the criteria some other way, like in a form - which is what one would normally do - then open the report.