Change filter source from table to query

G

Guest

I was asked to create a form that would search / filter data from various
different tables and display the results. I have done this, however now I
find out that they would like to export the data to Excel as well as generate
reports or possibly print the results. From what I understand this cannot be
done unless I based my filter on a query, which I did not. Can I change my
code (pasted below) to look at a query, which I will create, and then be able
to perform the above actions (export, print, etc)? I'm too new to access and
coding to understand if what I'm thinking of doing is way more trouble than
it's worth, or easily accomplished. Any suggestions or help would be greatly
appreciated.

************************
Option Compare Database

Private Sub cmdClear_Click()
DoCmd.Close
DoCmd.OpenForm "frmSearchDatabase"
End Sub

Private Sub cmdSearch_Click()
'Const cRequiredFieldError As String = "No criteria has been selected."
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim StrError As String

strWhere = "1=1 "

'If Record ID (tblEmployeeProjectDetails)
If Nz(Me.cboRecordID) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Assignment Number] = " & Me.cboRecordID & ""
End If

'If Last Name (tblEmployeeData)
If Nz(Me.cboLastName) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Last Name] = '" & Me.cboLastName & "'"
End If

'If Employee Number (tblEmployeeProjectDetails)
If Nz(Me.cboEmployeeNumber) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Emp #] = " & Me.cboEmployeeNumber & ""
End If

'If Actuals (tblEmployeeProjectDetails)
If Nz(chkActuals, 0) = True Then
strWhere = strWhere & " AND Actuals = True"
End If

'If Assignment Letter (tblEmployeeProjectDetails)
If Nz(chkAssignmentLetter, 0) = True Then
strWhere = strWhere & " AND AssignmentLetter = True"
End If

'If Weekly Allowance (tblEmployeeProjectDetails)
If Not IsNull(Me.txtWeeklyAllowanceLow) And Not
IsNull(Me.txtWeeklyAllowanceHigh) Then
strWhere = strWhere & " AND " & "[Wk/Allow] >= " &
Me.txtWeeklyAllowanceLow & " AND " & " [Wk/Allow] <= " &
Me.txtWeeklyAllowanceHigh
ElseIf Not IsNull(Me.txtWeeklyAllowanceLow) Then
strWhere = strWhere & " AND " & "[Wk/Allow] >= " &
Me.txtWeeklyAllowanceLow
ElseIf Not IsNull(Me.txtWeeklyAllowanceHigh) Then
strWhere = strWhere & " AND " & "[Wk/Allow] <= " &
Me.txtWeeklyAllowanceHigh
End If

'If Project Number (tblEmployeeProjectDetails)
If Nz(Me.cboProjectNumber) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Prj #] = " & Me.cboProjectNumber & ""
End If

'If Project State (tblProjectData)
If Nz(Me.cboProjectState) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "ST = '" & Me.cboProjectState & "'"
End If

'If Business Unit (tblProjectData)
If Nz(Me.cboBusinessUnit) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Business Unit] = '" & Me.cboBusinessUnit &
"'"
End If

'If Set Of Books (tblProjectData)
If Nz(Me.cboSetOfBooks) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "BOOKS = '" & Me.cboSetOfBooks & "'"
End If

'If Tax Status (tblEmployeeProjectDetails)
If Nz(Me.cboTaxStatus) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "TaxStatus = '" & Me.cboTaxStatus & "'"
End If

'If Project Manager (tblEmployeeProjectDetails)
If Nz(Me.cboProjectManager) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[MANAGER] = " & Me.cboProjectManager & ""
End If

'If Projected End Date From(tblEmployeeProjectDetails)
If IsDate(Me.txtProjectedEndDateFrom) Then
'Add it to Predicate - Exact
strWhere = strWhere & " AND " & "[PROJECTED END DATE] >= " &
GetDateFilter(Me.txtProjectedEndDateFrom)
ElseIf Nz(Me.txtProjectedEndDateFrom) <> "" Then
StrError = cInvalidDateError
End If

'If Projected End Date To (tblEmployeeProjectDetails)
If IsDate(Me.txtProjectedEndDateTo) Then
'Add it to Predicate - Exact
strWhere = strWhere & " AND " & "[PROJECTED END DATE] <= " &
GetDateFilter(Me.txtProjectedEndDateTo)
ElseIf Nz(Me.txtProjectedEndDateTo) <> "" Then
StrError = cInvalidDateError
End If

If StrError <> "" Then
MsgBox StrError
Else
'DoCmd.OpenForm "fsubRecordSearch", acFormsDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Debug.Print strWhere
Me.fsubRecordSearch.Form.Filter = strWhere
Me.fsubRecordSearch.Form.FilterOn = True
End If
End Sub
Function GetDateFilter(dtDate As Date) As String
' Date filters must be in dd/mmm/YYYY format
GetDateFilter = "#" & Format(dtDate, "dd/mmm/YYYY") & "#"
End Function

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyReturn Then
KeyCode = 0
cmdSearch_Click
End If
End Sub


Thanks!
 
A

Allen Browne

Your code builds up a filter string (strWhere), and assigns it to the Filter
of the form.

If the user now wants to print the same results, you can use that filter
string as the WhereCondition for OpenReport. The Click event procedure of
the command button would look something like this:
Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

Note that the report must not be open at the time, or the filter will not be
applied.

The export-to-Excel doesn't have a WhereCondition, so create a query to use
for export, and save as (say) qryExport2Excel. You can now patch the WHERE
clause into the query's SQL property,and then export it. This kind of thing:
Dim strWhere As String
Const strcStub = "SELECT * FROM Table 1 "
Const strcTail = "ORDER BY Field1;"
Dim strSql As String

Dim strWhere As String
If Me.FilterOn Then
strWhere = "WHERE (" & Me.Filter & ") "
End If
strSql = strcStub & strWhere & strcTail
'Debug.Print strSql
CurrentDb.QueryDefs("qryExport2Excel").SQL = strSql
DoCmd.TransferSpreadsheet acExport,,"qryExport2Excel", "C:\MyFile.xls"

To get the actual stub and tail of the SQL statement, switch your saved
query to SQL View (View menu in query design), copy from there, and paste
into the code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Justin83716 said:
I was asked to create a form that would search / filter data from various
different tables and display the results. I have done this, however now I
find out that they would like to export the data to Excel as well as
generate
reports or possibly print the results. From what I understand this cannot
be
done unless I based my filter on a query, which I did not. Can I change my
code (pasted below) to look at a query, which I will create, and then be
able
to perform the above actions (export, print, etc)? I'm too new to access
and
coding to understand if what I'm thinking of doing is way more trouble
than
it's worth, or easily accomplished. Any suggestions or help would be
greatly
appreciated.

************************
Option Compare Database

Private Sub cmdClear_Click()
DoCmd.Close
DoCmd.OpenForm "frmSearchDatabase"
End Sub

Private Sub cmdSearch_Click()
'Const cRequiredFieldError As String = "No criteria has been selected."
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim StrError As String

strWhere = "1=1 "

'If Record ID (tblEmployeeProjectDetails)
If Nz(Me.cboRecordID) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Assignment Number] = " & Me.cboRecordID
& ""
End If

'If Last Name (tblEmployeeData)
If Nz(Me.cboLastName) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Last Name] = '" & Me.cboLastName & "'"
End If

'If Employee Number (tblEmployeeProjectDetails)
If Nz(Me.cboEmployeeNumber) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Emp #] = " & Me.cboEmployeeNumber & ""
End If

'If Actuals (tblEmployeeProjectDetails)
If Nz(chkActuals, 0) = True Then
strWhere = strWhere & " AND Actuals = True"
End If

'If Assignment Letter (tblEmployeeProjectDetails)
If Nz(chkAssignmentLetter, 0) = True Then
strWhere = strWhere & " AND AssignmentLetter = True"
End If

'If Weekly Allowance (tblEmployeeProjectDetails)
If Not IsNull(Me.txtWeeklyAllowanceLow) And Not
IsNull(Me.txtWeeklyAllowanceHigh) Then
strWhere = strWhere & " AND " & "[Wk/Allow] >= " &
Me.txtWeeklyAllowanceLow & " AND " & " [Wk/Allow] <= " &
Me.txtWeeklyAllowanceHigh
ElseIf Not IsNull(Me.txtWeeklyAllowanceLow) Then
strWhere = strWhere & " AND " & "[Wk/Allow] >= " &
Me.txtWeeklyAllowanceLow
ElseIf Not IsNull(Me.txtWeeklyAllowanceHigh) Then
strWhere = strWhere & " AND " & "[Wk/Allow] <= " &
Me.txtWeeklyAllowanceHigh
End If

'If Project Number (tblEmployeeProjectDetails)
If Nz(Me.cboProjectNumber) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Prj #] = " & Me.cboProjectNumber & ""
End If

'If Project State (tblProjectData)
If Nz(Me.cboProjectState) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "ST = '" & Me.cboProjectState & "'"
End If

'If Business Unit (tblProjectData)
If Nz(Me.cboBusinessUnit) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Business Unit] = '" & Me.cboBusinessUnit
&
"'"
End If

'If Set Of Books (tblProjectData)
If Nz(Me.cboSetOfBooks) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "BOOKS = '" & Me.cboSetOfBooks & "'"
End If

'If Tax Status (tblEmployeeProjectDetails)
If Nz(Me.cboTaxStatus) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "TaxStatus = '" & Me.cboTaxStatus & "'"
End If

'If Project Manager (tblEmployeeProjectDetails)
If Nz(Me.cboProjectManager) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[MANAGER] = " & Me.cboProjectManager & ""
End If

'If Projected End Date From(tblEmployeeProjectDetails)
If IsDate(Me.txtProjectedEndDateFrom) Then
'Add it to Predicate - Exact
strWhere = strWhere & " AND " & "[PROJECTED END DATE] >= " &
GetDateFilter(Me.txtProjectedEndDateFrom)
ElseIf Nz(Me.txtProjectedEndDateFrom) <> "" Then
StrError = cInvalidDateError
End If

'If Projected End Date To (tblEmployeeProjectDetails)
If IsDate(Me.txtProjectedEndDateTo) Then
'Add it to Predicate - Exact
strWhere = strWhere & " AND " & "[PROJECTED END DATE] <= " &
GetDateFilter(Me.txtProjectedEndDateTo)
ElseIf Nz(Me.txtProjectedEndDateTo) <> "" Then
StrError = cInvalidDateError
End If

If StrError <> "" Then
MsgBox StrError
Else
'DoCmd.OpenForm "fsubRecordSearch", acFormsDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Debug.Print strWhere
Me.fsubRecordSearch.Form.Filter = strWhere
Me.fsubRecordSearch.Form.FilterOn = True
End If
End Sub
Function GetDateFilter(dtDate As Date) As String
' Date filters must be in dd/mmm/YYYY format
GetDateFilter = "#" & Format(dtDate, "dd/mmm/YYYY") & "#"
End Function

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyReturn Then
KeyCode = 0
cmdSearch_Click
End If
End Sub


Thanks!
Click to expand...
 
G

Guest

Thank you for responding Allen.

As I'm a new to Access, I just want to clarify a couple of points.

In your example below you refer to "Report1". I have created a report
(rptResultsReport) which I created with the wizard and made sure that I used
the same control source as the filter form. However I still can't seem to get
the report to filter. It continues to pull ALL records. Can you explain more
about how the report should be created in order to have it be filtered by my
form. Are you aware of any examples of what I'm trying to do anywhere on the
web?

Thank you again.

Allen Browne said:
Your code builds up a filter string (strWhere), and assigns it to the Filter
of the form.

If the user now wants to print the same results, you can use that filter
string as the WhereCondition for OpenReport. The Click event procedure of
the command button would look something like this:
Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

Note that the report must not be open at the time, or the filter will not be
applied.

The export-to-Excel doesn't have a WhereCondition, so create a query to use
for export, and save as (say) qryExport2Excel. You can now patch the WHERE
clause into the query's SQL property,and then export it. This kind of thing:
Dim strWhere As String
Const strcStub = "SELECT * FROM Table 1 "
Const strcTail = "ORDER BY Field1;"
Dim strSql As String

Dim strWhere As String
If Me.FilterOn Then
strWhere = "WHERE (" & Me.Filter & ") "
End If
strSql = strcStub & strWhere & strcTail
'Debug.Print strSql
CurrentDb.QueryDefs("qryExport2Excel").SQL = strSql
DoCmd.TransferSpreadsheet acExport,,"qryExport2Excel", "C:\MyFile.xls"

To get the actual stub and tail of the SQL statement, switch your saved
query to SQL View (View menu in query design), copy from there, and paste
into the code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Justin83716 said:
I was asked to create a form that would search / filter data from various
different tables and display the results. I have done this, however now I
find out that they would like to export the data to Excel as well as
generate
reports or possibly print the results. From what I understand this cannot
be
done unless I based my filter on a query, which I did not. Can I change my
code (pasted below) to look at a query, which I will create, and then be
able
to perform the above actions (export, print, etc)? I'm too new to access
and
coding to understand if what I'm thinking of doing is way more trouble
than
it's worth, or easily accomplished. Any suggestions or help would be
greatly
appreciated.

************************
Option Compare Database

Private Sub cmdClear_Click()
DoCmd.Close
DoCmd.OpenForm "frmSearchDatabase"
End Sub

Private Sub cmdSearch_Click()
'Const cRequiredFieldError As String = "No criteria has been selected."
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim StrError As String

strWhere = "1=1 "

'If Record ID (tblEmployeeProjectDetails)
If Nz(Me.cboRecordID) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Assignment Number] = " & Me.cboRecordID
& ""
End If

'If Last Name (tblEmployeeData)
If Nz(Me.cboLastName) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Last Name] = '" & Me.cboLastName & "'"
End If

'If Employee Number (tblEmployeeProjectDetails)
If Nz(Me.cboEmployeeNumber) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Emp #] = " & Me.cboEmployeeNumber & ""
End If

'If Actuals (tblEmployeeProjectDetails)
If Nz(chkActuals, 0) = True Then
strWhere = strWhere & " AND Actuals = True"
End If

'If Assignment Letter (tblEmployeeProjectDetails)
If Nz(chkAssignmentLetter, 0) = True Then
strWhere = strWhere & " AND AssignmentLetter = True"
End If

'If Weekly Allowance (tblEmployeeProjectDetails)
If Not IsNull(Me.txtWeeklyAllowanceLow) And Not
IsNull(Me.txtWeeklyAllowanceHigh) Then
strWhere = strWhere & " AND " & "[Wk/Allow] >= " &
Me.txtWeeklyAllowanceLow & " AND " & " [Wk/Allow] <= " &
Me.txtWeeklyAllowanceHigh
ElseIf Not IsNull(Me.txtWeeklyAllowanceLow) Then
strWhere = strWhere & " AND " & "[Wk/Allow] >= " &
Me.txtWeeklyAllowanceLow
ElseIf Not IsNull(Me.txtWeeklyAllowanceHigh) Then
strWhere = strWhere & " AND " & "[Wk/Allow] <= " &
Me.txtWeeklyAllowanceHigh
End If

'If Project Number (tblEmployeeProjectDetails)
If Nz(Me.cboProjectNumber) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Prj #] = " & Me.cboProjectNumber & ""
End If

'If Project State (tblProjectData)
If Nz(Me.cboProjectState) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "ST = '" & Me.cboProjectState & "'"
End If

'If Business Unit (tblProjectData)
If Nz(Me.cboBusinessUnit) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Business Unit] = '" & Me.cboBusinessUnit
&
"'"
End If

'If Set Of Books (tblProjectData)
If Nz(Me.cboSetOfBooks) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "BOOKS = '" & Me.cboSetOfBooks & "'"
End If

'If Tax Status (tblEmployeeProjectDetails)
If Nz(Me.cboTaxStatus) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "TaxStatus = '" & Me.cboTaxStatus & "'"
End If

'If Project Manager (tblEmployeeProjectDetails)
If Nz(Me.cboProjectManager) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[MANAGER] = " & Me.cboProjectManager & ""
End If

'If Projected End Date From(tblEmployeeProjectDetails)
If IsDate(Me.txtProjectedEndDateFrom) Then
'Add it to Predicate - Exact
strWhere = strWhere & " AND " & "[PROJECTED END DATE] >= " &
GetDateFilter(Me.txtProjectedEndDateFrom)
ElseIf Nz(Me.txtProjectedEndDateFrom) <> "" Then
StrError = cInvalidDateError
End If

'If Projected End Date To (tblEmployeeProjectDetails)
If IsDate(Me.txtProjectedEndDateTo) Then
'Add it to Predicate - Exact
strWhere = strWhere & " AND " & "[PROJECTED END DATE] <= " &
GetDateFilter(Me.txtProjectedEndDateTo)
ElseIf Nz(Me.txtProjectedEndDateTo) <> "" Then
StrError = cInvalidDateError
End If

If StrError <> "" Then
MsgBox StrError
Else
'DoCmd.OpenForm "fsubRecordSearch", acFormsDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Debug.Print strWhere
Me.fsubRecordSearch.Form.Filter = strWhere
Me.fsubRecordSearch.Form.FilterOn = True
End If
End Sub
Function GetDateFilter(dtDate As Date) As String
' Date filters must be in dd/mmm/YYYY format
GetDateFilter = "#" & Format(dtDate, "dd/mmm/YYYY") & "#"
End Function

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyReturn Then
KeyCode = 0
cmdSearch_Click
End If
End Sub


Thanks!
Click to expand...
Click to expand...
 
A

Allen Browne

Substitute "rptResultsReport" for "Report1"
That's the only change needed.

As mentioned, the report must not be open (not even in design view) at the
time this code calls it.

If you are asked for a parameter, there's something in the form's filter
that is not in the report's filter.

You can see the filter you are trying to apply with:
Debug.Print strWhere
and then open the Immediate Window (Ctrl+G) after the code runs.

A simple example of applying a WhereCondition to OpenReport:
http://allenbrowne.com/casu-15.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Justin83716 said:
Thank you for responding Allen.

As I'm a new to Access, I just want to clarify a couple of points.

In your example below you refer to "Report1". I have created a report
(rptResultsReport) which I created with the wizard and made sure that I
used
the same control source as the filter form. However I still can't seem to
get
the report to filter. It continues to pull ALL records. Can you explain
more
about how the report should be created in order to have it be filtered by
my
form. Are you aware of any examples of what I'm trying to do anywhere on
the
web?

Thank you again.

Allen Browne said:
Your code builds up a filter string (strWhere), and assigns it to the
Filter
of the form.

If the user now wants to print the same results, you can use that filter
string as the WhereCondition for OpenReport. The Click event procedure of
the command button would look something like this:
Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

Note that the report must not be open at the time, or the filter will not
be
applied.

The export-to-Excel doesn't have a WhereCondition, so create a query to
use
for export, and save as (say) qryExport2Excel. You can now patch the
WHERE
clause into the query's SQL property,and then export it. This kind of
thing:
Dim strWhere As String
Const strcStub = "SELECT * FROM Table 1 "
Const strcTail = "ORDER BY Field1;"
Dim strSql As String

Dim strWhere As String
If Me.FilterOn Then
strWhere = "WHERE (" & Me.Filter & ") "
End If
strSql = strcStub & strWhere & strcTail
'Debug.Print strSql
CurrentDb.QueryDefs("qryExport2Excel").SQL = strSql
DoCmd.TransferSpreadsheet acExport,,"qryExport2Excel",
"C:\MyFile.xls"

To get the actual stub and tail of the SQL statement, switch your saved
query to SQL View (View menu in query design), copy from there, and paste
into the code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Justin83716 said:
I was asked to create a form that would search / filter data from
various
different tables and display the results. I have done this, however now
I
find out that they would like to export the data to Excel as well as
generate
reports or possibly print the results. From what I understand this
cannot
be
done unless I based my filter on a query, which I did not. Can I change
my
code (pasted below) to look at a query, which I will create, and then
be
able
to perform the above actions (export, print, etc)? I'm too new to
access
and
coding to understand if what I'm thinking of doing is way more trouble
than
it's worth, or easily accomplished. Any suggestions or help would be
greatly
appreciated.

************************
Option Compare Database

Private Sub cmdClear_Click()
DoCmd.Close
DoCmd.OpenForm "frmSearchDatabase"
End Sub

Private Sub cmdSearch_Click()
'Const cRequiredFieldError As String = "No criteria has been selected."
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim StrError As String

strWhere = "1=1 "

'If Record ID (tblEmployeeProjectDetails)
If Nz(Me.cboRecordID) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Assignment Number] = " &
Me.cboRecordID
& ""
End If

'If Last Name (tblEmployeeData)
If Nz(Me.cboLastName) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Last Name] = '" & Me.cboLastName &
"'"
End If

'If Employee Number (tblEmployeeProjectDetails)
If Nz(Me.cboEmployeeNumber) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Emp #] = " & Me.cboEmployeeNumber &
""
End If

'If Actuals (tblEmployeeProjectDetails)
If Nz(chkActuals, 0) = True Then
strWhere = strWhere & " AND Actuals = True"
End If

'If Assignment Letter (tblEmployeeProjectDetails)
If Nz(chkAssignmentLetter, 0) = True Then
strWhere = strWhere & " AND AssignmentLetter = True"
End If

'If Weekly Allowance (tblEmployeeProjectDetails)
If Not IsNull(Me.txtWeeklyAllowanceLow) And Not
IsNull(Me.txtWeeklyAllowanceHigh) Then
strWhere = strWhere & " AND " & "[Wk/Allow] >= " &
Me.txtWeeklyAllowanceLow & " AND " & " [Wk/Allow] <= " &
Me.txtWeeklyAllowanceHigh
ElseIf Not IsNull(Me.txtWeeklyAllowanceLow) Then
strWhere = strWhere & " AND " & "[Wk/Allow] >= " &
Me.txtWeeklyAllowanceLow
ElseIf Not IsNull(Me.txtWeeklyAllowanceHigh) Then
strWhere = strWhere & " AND " & "[Wk/Allow] <= " &
Me.txtWeeklyAllowanceHigh
End If

'If Project Number (tblEmployeeProjectDetails)
If Nz(Me.cboProjectNumber) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Prj #] = " & Me.cboProjectNumber & ""
End If

'If Project State (tblProjectData)
If Nz(Me.cboProjectState) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "ST = '" & Me.cboProjectState & "'"
End If

'If Business Unit (tblProjectData)
If Nz(Me.cboBusinessUnit) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Business Unit] = '" &
Me.cboBusinessUnit
&
"'"
End If

'If Set Of Books (tblProjectData)
If Nz(Me.cboSetOfBooks) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "BOOKS = '" & Me.cboSetOfBooks & "'"
End If

'If Tax Status (tblEmployeeProjectDetails)
If Nz(Me.cboTaxStatus) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "TaxStatus = '" & Me.cboTaxStatus & "'"
End If

'If Project Manager (tblEmployeeProjectDetails)
If Nz(Me.cboProjectManager) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[MANAGER] = " & Me.cboProjectManager &
""
End If

'If Projected End Date From(tblEmployeeProjectDetails)
If IsDate(Me.txtProjectedEndDateFrom) Then
'Add it to Predicate - Exact
strWhere = strWhere & " AND " & "[PROJECTED END DATE] >= " &
GetDateFilter(Me.txtProjectedEndDateFrom)
ElseIf Nz(Me.txtProjectedEndDateFrom) <> "" Then
StrError = cInvalidDateError
End If

'If Projected End Date To (tblEmployeeProjectDetails)
If IsDate(Me.txtProjectedEndDateTo) Then
'Add it to Predicate - Exact
strWhere = strWhere & " AND " & "[PROJECTED END DATE] <= " &
GetDateFilter(Me.txtProjectedEndDateTo)
ElseIf Nz(Me.txtProjectedEndDateTo) <> "" Then
StrError = cInvalidDateError
End If

If StrError <> "" Then
MsgBox StrError
Else
'DoCmd.OpenForm "fsubRecordSearch", acFormsDS, , strWhere,
acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Debug.Print strWhere
Me.fsubRecordSearch.Form.Filter = strWhere
Me.fsubRecordSearch.Form.FilterOn = True
End If
End Sub
Function GetDateFilter(dtDate As Date) As String
' Date filters must be in dd/mmm/YYYY format
GetDateFilter = "#" & Format(dtDate, "dd/mmm/YYYY") & "#"
End Function

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyReturn Then
KeyCode = 0
cmdSearch_Click
End If
End Sub


Thanks!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Perfect! Thank you.

Allen Browne said:
Substitute "rptResultsReport" for "Report1"
That's the only change needed.

As mentioned, the report must not be open (not even in design view) at the
time this code calls it.

If you are asked for a parameter, there's something in the form's filter
that is not in the report's filter.

You can see the filter you are trying to apply with:
Debug.Print strWhere
and then open the Immediate Window (Ctrl+G) after the code runs.

A simple example of applying a WhereCondition to OpenReport:
http://allenbrowne.com/casu-15.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Justin83716 said:
Thank you for responding Allen.

As I'm a new to Access, I just want to clarify a couple of points.

In your example below you refer to "Report1". I have created a report
(rptResultsReport) which I created with the wizard and made sure that I
used
the same control source as the filter form. However I still can't seem to
get
the report to filter. It continues to pull ALL records. Can you explain
more
about how the report should be created in order to have it be filtered by
my
form. Are you aware of any examples of what I'm trying to do anywhere on
the
web?

Thank you again.

Allen Browne said:
Your code builds up a filter string (strWhere), and assigns it to the
Filter
of the form.

If the user now wants to print the same results, you can use that filter
string as the WhereCondition for OpenReport. The Click event procedure of
the command button would look something like this:
Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

Note that the report must not be open at the time, or the filter will not
be
applied.

The export-to-Excel doesn't have a WhereCondition, so create a query to
use
for export, and save as (say) qryExport2Excel. You can now patch the
WHERE
clause into the query's SQL property,and then export it. This kind of
thing:
Dim strWhere As String
Const strcStub = "SELECT * FROM Table 1 "
Const strcTail = "ORDER BY Field1;"
Dim strSql As String

Dim strWhere As String
If Me.FilterOn Then
strWhere = "WHERE (" & Me.Filter & ") "
End If
strSql = strcStub & strWhere & strcTail
'Debug.Print strSql
CurrentDb.QueryDefs("qryExport2Excel").SQL = strSql
DoCmd.TransferSpreadsheet acExport,,"qryExport2Excel",
"C:\MyFile.xls"

To get the actual stub and tail of the SQL statement, switch your saved
query to SQL View (View menu in query design), copy from there, and paste
into the code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I was asked to create a form that would search / filter data from
various
different tables and display the results. I have done this, however now
I
find out that they would like to export the data to Excel as well as
generate
reports or possibly print the results. From what I understand this
cannot
be
done unless I based my filter on a query, which I did not. Can I change
my
code (pasted below) to look at a query, which I will create, and then
be
able
to perform the above actions (export, print, etc)? I'm too new to
access
and
coding to understand if what I'm thinking of doing is way more trouble
than
it's worth, or easily accomplished. Any suggestions or help would be
greatly
appreciated.

************************
Option Compare Database

Private Sub cmdClear_Click()
DoCmd.Close
DoCmd.OpenForm "frmSearchDatabase"
End Sub

Private Sub cmdSearch_Click()
'Const cRequiredFieldError As String = "No criteria has been selected."
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim StrError As String

strWhere = "1=1 "

'If Record ID (tblEmployeeProjectDetails)
If Nz(Me.cboRecordID) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Assignment Number] = " &
Me.cboRecordID
& ""
End If

'If Last Name (tblEmployeeData)
If Nz(Me.cboLastName) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Last Name] = '" & Me.cboLastName &
"'"
End If

'If Employee Number (tblEmployeeProjectDetails)
If Nz(Me.cboEmployeeNumber) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Emp #] = " & Me.cboEmployeeNumber &
""
End If

'If Actuals (tblEmployeeProjectDetails)
If Nz(chkActuals, 0) = True Then
strWhere = strWhere & " AND Actuals = True"
End If

'If Assignment Letter (tblEmployeeProjectDetails)
If Nz(chkAssignmentLetter, 0) = True Then
strWhere = strWhere & " AND AssignmentLetter = True"
End If

'If Weekly Allowance (tblEmployeeProjectDetails)
If Not IsNull(Me.txtWeeklyAllowanceLow) And Not
IsNull(Me.txtWeeklyAllowanceHigh) Then
strWhere = strWhere & " AND " & "[Wk/Allow] >= " &
Me.txtWeeklyAllowanceLow & " AND " & " [Wk/Allow] <= " &
Me.txtWeeklyAllowanceHigh
ElseIf Not IsNull(Me.txtWeeklyAllowanceLow) Then
strWhere = strWhere & " AND " & "[Wk/Allow] >= " &
Me.txtWeeklyAllowanceLow
ElseIf Not IsNull(Me.txtWeeklyAllowanceHigh) Then
strWhere = strWhere & " AND " & "[Wk/Allow] <= " &
Me.txtWeeklyAllowanceHigh
End If

'If Project Number (tblEmployeeProjectDetails)
If Nz(Me.cboProjectNumber) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Prj #] = " & Me.cboProjectNumber & ""
End If

'If Project State (tblProjectData)
If Nz(Me.cboProjectState) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "ST = '" & Me.cboProjectState & "'"
End If

'If Business Unit (tblProjectData)
If Nz(Me.cboBusinessUnit) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Business Unit] = '" &
Me.cboBusinessUnit
&
"'"
End If

'If Set Of Books (tblProjectData)
If Nz(Me.cboSetOfBooks) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "BOOKS = '" & Me.cboSetOfBooks & "'"
End If

'If Tax Status (tblEmployeeProjectDetails)
If Nz(Me.cboTaxStatus) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "TaxStatus = '" & Me.cboTaxStatus & "'"
End If

'If Project Manager (tblEmployeeProjectDetails)
If Nz(Me.cboProjectManager) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[MANAGER] = " & Me.cboProjectManager &
""
End If

'If Projected End Date From(tblEmployeeProjectDetails)
If IsDate(Me.txtProjectedEndDateFrom) Then
'Add it to Predicate - Exact
strWhere = strWhere & " AND " & "[PROJECTED END DATE] >= " &
GetDateFilter(Me.txtProjectedEndDateFrom)
ElseIf Nz(Me.txtProjectedEndDateFrom) <> "" Then
StrError = cInvalidDateError
End If

'If Projected End Date To (tblEmployeeProjectDetails)
If IsDate(Me.txtProjectedEndDateTo) Then
'Add it to Predicate - Exact
strWhere = strWhere & " AND " & "[PROJECTED END DATE] <= " &
GetDateFilter(Me.txtProjectedEndDateTo)
ElseIf Nz(Me.txtProjectedEndDateTo) <> "" Then
StrError = cInvalidDateError
End If

If StrError <> "" Then
MsgBox StrError
Else
'DoCmd.OpenForm "fsubRecordSearch", acFormsDS, , strWhere,
acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Debug.Print strWhere
Me.fsubRecordSearch.Form.Filter = strWhere
Me.fsubRecordSearch.Form.FilterOn = True
End If
End Sub
Function GetDateFilter(dtDate As Date) As String
' Date filters must be in dd/mmm/YYYY format
GetDateFilter = "#" & Format(dtDate, "dd/mmm/YYYY") & "#"
End Function

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyReturn Then
KeyCode = 0
cmdSearch_Click
End If
End Sub


Thanks!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

runtime error 3075 2
Subform Filter 5
RePost- Currency Range 3
Urgent help needed! I have attached the code so, you can help me. thanks 1
Unexpected Enter Parameter Box 12
Error when Filtering on Unbound Form with Subform 7
search for multiple records in a form 4
Problem with Runtime Error 2448 4

Top