How do I apply a filter to make a table query

[AjilonSS]

I have been successful in using this report when running data through a simple report and having the code apply a filter...

The challenge I am now having is that I have a much more complex report, with sub-reports and all, where the filter approach does not work.

So, what I am trying to do now is apply the filter to the make table query - from there the report then uses the temp table and the problem of filtering via the report goes away...

What I cannot figure out how to do is apply the filter to the make table query...from there it will be easy to loop through the report create/distribution steps...

Any help would be greatly appreciated.


Option Explicit

'****************************************************************
'This function will walk through a table and email the
'report, which is filtered using
'MS Outlook through the MS Access SendObject.
'
'This function assumes the report has the default printer
'set to PDF.
'****************************************************************

Public strInvoiceWhere As String

Function AJSendStaffingReport()
Dim headcounts As DAO.Database

'set table containing data source?
Dim Staffing_Report_ALL_NEW_BREAKS As DAO.Recordset

Set headcounts = Currentdb()
Set Staffing_Report_ALL_NEW_BREAKS = headcounts.OpenRecordset("Staffing_Report_ALL_NEW_BREAKS", _
dbOpenDynaset)

'If MsgBox("Do you want to create report" & Chr(13) & _ <put prompt back in later
' "to all receiptents using Microsoft Outlook?", 4) = 6 Then

With Staffing_Report_ALL_NEW_BREAKS
Do Until .EOF

'Create the report Filter <-how to replace in query
'used by the Report_Open event. <-how to replace report_open to define variable

strInvoiceWhere = "[hierarchy_code] = '" & ![hierarchy_code] & "'"

DoCmd.SetWarnings False
DoCmd.OpenQuery "Staffing_Report_ALL_NEW"
DoCmd.SetWarnings True


' create report as pdf with unique filename
'SendKeys "c:\z\Staffing_Report_" & ![hierarchy_code] & "_" & ![rvp_avp_last_name] & "_" & Format(Now(), "YYYYMMDD") & "~^sy", False
'DoCmd.OpenReport "_Staffing Report - COMBINED - Consolidated", acViewNormal, "", "", acHidden



' then attach both files and send
' DoCmd.SendObject acReport, "_Staffing Report - COMBINED - Consolidated", acFormatRTF, _
' ![test], , , "Weekly Open Requisition Report - " & Format(Now(), "mm/dd/yy"), ![hierarchy_name] & " THIS IS A TEST open requisition report attached - ARE YOU HAPPY???", False


.MoveNext
Loop
End With
' End If < remove comment later when prompt is put back in

Staffing_Report_ALL_NEW_BREAKS.Close ' close break list
'DONE with hierarchy pass

 

[bboffin]

In order to apply a filter - a WHERE clause in SQL - you must change the SQL for the query.

The usual way of doing this is to get the SQL of your base query.

Then append the WHERE clause to the SQL of the new QueryDef and execute it.

Try something like this function:

Public Function GetFilteredSQL(ColXvalue As Long, ColYValue As String) As String
Dim lSQL as String
lSQL = Trim(CurrentDb.QueryDefs("MyBaseQuery").SQL)
' remove possible semicolon from the end of the SQL
If Right(lSQL, 1) = ";" then
    lSQL = Left(lSQL, Len(lSQL) - 1)
End If
lSQL = lSQL & " WHERE ColumnY=" & ColXvalue & " AND ColumnY='" & ColYValue & "'"
GetFilteredSQl = lSQL
End Function

This will return you the SQL to extract the filtered records and you can now set the RecordSource property of the Report to this value and it will execute the filtering SQL.



Bob Boffin

 

[AjilonSS]

Hey Bob,

Where do I insert your code in my code? At the end of my code?