J
Jack
Hi,
I have the following code that generates output of an access query by the
use of filter. However I need to export the filtered query to a excel
spreadhset. I have exported from access using transfer spreadsheet method.
However I am not sure how to handle this situation where the filtered record
needs to be exported to excel.
I appreciate any help for resolution of this issue. Thanks
CODE:
Private Sub cmdSubmit_Click()
On Error GoTo Err_cmdSubmit_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim strBizUnit As String
DoCmd.OpenForm "frmCompositeSearch"
Forms!frmCompositeSearch.Visible = False
stDocName = "frmActionRequestFiltered"
If IsNull(Frame71.Value) Then
'Add Message Box
MsgBox ("You must choose a type of business unit")
Exit Sub
End If
If Frame71.Value = 3 Then
Else
stLinkCriteria = "[BizUnit]=" & "'" & Me![txtBusUnit] & "'"
End If
If Me.txtOpenClosed <> "" Then
stLinkCriteria = stLinkCriteria _
& " And " & "[Status]=" & "'" & Me![txtOpenClosed] & "'"
End If
If Me.cboplant <> "" Then
stLinkCriteria = stLinkCriteria _
& " And " & "[Plant]=" & "'" & Me.cboplant & "'"
' Me.cboplant.Value = ""
End If
If Me.cboCust <> "" Then
stLinkCriteria = stLinkCriteria _
& " And " & "[Customer Supplier]=" & "'" & Me.cboCust & "'"
'Me.cboCust.Value = ""
End If
If Me.cboSource1 <> "" Then
stLinkCriteria = stLinkCriteria _
& " And " & "[Source]=" & "'" & Me![cboSource1] & "'"
' Me.cboOriginator.Value = ""
'Me.cboSource1 = ""
End If
If Me.cboDefectCode <> "" Then
stLinkCriteria = stLinkCriteria _
& " And " & "[Defect Code]=" & "'" & Me![cboDefectCode] & "'"
End If
If Me.cboCategory <> "" Then
stLinkCriteria = stLinkCriteria _
& " And " & "[Category]=" & "'" & Me![cboCategory] & "'"
End If
If Me.txtStartDate <> "" And Me.txtEndDate <> "" Then
stLinkCriteria = stLinkCriteria _
& " And " & "[RequestDate]" & " Between " _
& "#" & Me![txtStartDate] & "#" & " And " _
& "#" & Me![txtEndDate] + 1 & "#"
End If
If Frame71.Value = 3 Then
stLinkCriteria = Mid(stLinkCriteria, 6)
DoCmd.OpenQuery "qryRptIndustrialCar", , acReadOnly
If stLinkCriteria <> "" Then
DoCmd.ApplyFilter , stLinkCriteria
End If
End If
I have the following code that generates output of an access query by the
use of filter. However I need to export the filtered query to a excel
spreadhset. I have exported from access using transfer spreadsheet method.
However I am not sure how to handle this situation where the filtered record
needs to be exported to excel.
I appreciate any help for resolution of this issue. Thanks
CODE:
Private Sub cmdSubmit_Click()
On Error GoTo Err_cmdSubmit_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim strBizUnit As String
DoCmd.OpenForm "frmCompositeSearch"
Forms!frmCompositeSearch.Visible = False
stDocName = "frmActionRequestFiltered"
If IsNull(Frame71.Value) Then
'Add Message Box
MsgBox ("You must choose a type of business unit")
Exit Sub
End If
If Frame71.Value = 3 Then
Else
stLinkCriteria = "[BizUnit]=" & "'" & Me![txtBusUnit] & "'"
End If
If Me.txtOpenClosed <> "" Then
stLinkCriteria = stLinkCriteria _
& " And " & "[Status]=" & "'" & Me![txtOpenClosed] & "'"
End If
If Me.cboplant <> "" Then
stLinkCriteria = stLinkCriteria _
& " And " & "[Plant]=" & "'" & Me.cboplant & "'"
' Me.cboplant.Value = ""
End If
If Me.cboCust <> "" Then
stLinkCriteria = stLinkCriteria _
& " And " & "[Customer Supplier]=" & "'" & Me.cboCust & "'"
'Me.cboCust.Value = ""
End If
If Me.cboSource1 <> "" Then
stLinkCriteria = stLinkCriteria _
& " And " & "[Source]=" & "'" & Me![cboSource1] & "'"
' Me.cboOriginator.Value = ""
'Me.cboSource1 = ""
End If
If Me.cboDefectCode <> "" Then
stLinkCriteria = stLinkCriteria _
& " And " & "[Defect Code]=" & "'" & Me![cboDefectCode] & "'"
End If
If Me.cboCategory <> "" Then
stLinkCriteria = stLinkCriteria _
& " And " & "[Category]=" & "'" & Me![cboCategory] & "'"
End If
If Me.txtStartDate <> "" And Me.txtEndDate <> "" Then
stLinkCriteria = stLinkCriteria _
& " And " & "[RequestDate]" & " Between " _
& "#" & Me![txtStartDate] & "#" & " And " _
& "#" & Me![txtEndDate] + 1 & "#"
End If
If Frame71.Value = 3 Then
stLinkCriteria = Mid(stLinkCriteria, 6)
DoCmd.OpenQuery "qryRptIndustrialCar", , acReadOnly
If stLinkCriteria <> "" Then
DoCmd.ApplyFilter , stLinkCriteria
End If
End If