Hi, I created a form for users t

[OCM]

Hi,

I created a form for users to answer to a parameter value and click a button to preview the report.
My goal is to export the result to excel.

1. When creating a second button ‘Export to Excel’ I attempted to use the wizard, but I did not see any option to export data to excel

2. I manually created a second button ‘Export to Excel’ and used the following:

DoCmd.OutputTo acOutputQuery, "MyQueryName ", acFormatXLS, , True

However, the exported excel file doesn’t limit to the selection made (it shows everything)

Any idea?

TIA


OCM

 

[dhookom]

I would use a little DAO code to change the SQL property of the querydef faq701-7433.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[OCM]

Thanks Duane,

Do I place the DAO code to the properties of the ‘Export to Excel’ button?

TIA,


OCM

 

[dhookom]

I expect the code would be in any button used to export.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[OCM]

Duane,
I created a new module and pasted the following code.

When I click ‘Export’ button nothing happens.

Function fChangeSQL(pstrQueryName As String, strSQL As String) As String
'=============================================================
' basQueryDefs.ChangeSQL
'-------------------------------------------------------------
' Purpose : update the SQL property of a saved query
' Copyright: Duane Hookom
' Author : Duane Hookom
' Notes :
'-------------------------------------------------------------
' Parameters
'-----------
' pstrQueryName (String) Name of saved query
' strSQL (String)        SQL Statement
'-------------------------------------------------------------
' Returns: the previous SQL statement
'-------------------------------------------------------------
' Revision History
'-------------------------------------------------------------
' 07-09-2001 DKH:
'=============================================================
' End Code Header block
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Set db = CurrentDb
    Set qd = db.QueryDefs(qry_Cases_All) 
    fChangeSQL = qd.SQL
    qd.SQL = strSQL
    Set qd = Nothing
    Set db = Nothing
End Function

TIA,



OCM

 

[dhookom]

You didn't include anything that shows how you are setting the SQL or calling the export.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[OCM]

Duane,

Yes, I think that is where I’m getting stuck and need help.

Regards,


OCM

 

[dhookom]

You shouldn't have changed any lines in the function. There was an example on how to use the code in the link I provided.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[OCM]

Duane,

I'm trying to use the example you provided on "How to Change SQL property of saved query (DAO) w/o any success. The form I created for users to answer to prompts & click run query to preview the filtered data works very well (code below). What I'm trying to accomplish is the ability to export filtered data to excel.

Private Sub cmdRunQuery_Click()

Dim strWhere As String

strWhere = " 1=1 "

If Not IsNull(Me.txtBeginRefDT) Then
    strWhere = strWhere & " AND [REFDT] >= #" & txtBeginRefDT & "# "
End If
If Not IsNull(Me.txtEndRefDT) Then
    strWhere = strWhere & " AND [REFDT] <= #" & txtEndRefDT & "# "
End If
If Not IsNull(Me.txtBeginAssignDT) Then
    strWhere = strWhere & " AND [ASSIGNDT] >= #" & txtBeginAssignDT & "# "
End If
If Not IsNull(Me.txtEndAssignDT) Then
    strWhere = strWhere & " AND [ASSIGNDT] <= #" & txtEndAssignDT & "# "
End If
If Not IsNull(Me.cboStatus) Then
      strWhere = strWhere & " AND [STATDESC] = """ & Me.cboStatus & """ "
End If
If Not IsNull(Me.cboInvesgigator) Then
         strWhere = strWhere & " AND [USERNM] = """ & Me.cboInvesgigator & """ "
End If
If Not IsNull(Me.cboInvestigatorType) Then
      strWhere = strWhere & " AND [INVTYPE] = """ & Me.cboInvestigatorType & """ "
End If
If Not IsNull(Me.txtBeginCloseDT) Then
    strWhere = strWhere & " AND [CLOSEDT] >= #" & txtBeginCloseDT & "# "
End If
If Not IsNull(Me.txtEndCloseDT) Then
    strWhere = strWhere & " AND [CLOSEDT] <= #" & txtEndCloseDT & "# "
End If
If Not IsNull(Me.cboCloseReason) Then
    strWhere = strWhere & " AND [CLSREASON] = " & Me.cboCloseReason
End If
If Not IsNull(Me.txtBeginProsReferredDT) Then
    strWhere = strWhere & " AND [PROSDT] >= #" & txtBeginProsReferredDT & "# "
End If
If Not IsNull(Me.txtEndProsReferredDT) Then
    strWhere = strWhere & " AND [PROSDT] <= #" & txtEndProsReferredDT & "# "
End If
If Not IsNull(Me.cboRefAgency) Then
         strWhere = strWhere & " AND [AGENCYNM] = """ & Me.cboRefAgency & """ "
End If
If Not IsNull(Me.txtBeginAcceptDT) Then
    strWhere = strWhere & " AND [PROSACCPTDT] >= #" & txtBeginAcceptDT & "# "
End If
If Not IsNull(Me.txtEndAcceptDT) Then
    strWhere = strWhere & " AND [PROSACCPTDT] <= #" & txtEndAcceptDT & "# "
End If
If Not IsNull(Me.txtBeginDeclineDT) Then
    strWhere = strWhere & " AND [PROSREJDT] >= #" & txtBeginDeclineDT & "# "
End If
If Not IsNull(Me.txtEndDeclineDT) Then
    strWhere = strWhere & " AND [PROSREJDT] <= #" & txtEndDeclineDT & "# "
End If
If Not IsNull(Me.txtBeginRecovLetteSentDT) Then
    strWhere = strWhere & " AND [LETTERDT] >= #" & txtBeginRecovLetteSentDT & "# "
End If
If Not IsNull(Me.txtEndRecovLetterSentDT) Then
    strWhere = strWhere & " AND [LETTERDT] <= #" & txtEndRecovLetterSentDT & "# "
End If
If Not IsNull(Me.txtBeginFinalSetlmtDT) Then
    strWhere = strWhere & " AND [FINALDT] >= #" & txtBeginFinalSetlmtDT & "# "
End If
If Not IsNull(Me.txtEndFinalSetlmtDT) Then
    strWhere = strWhere & " AND [FINALDT] <= #" & txtEndFinalSetlmtDT & "# "
End If

Debug.Print strWhere   'show me the value

DoCmd.OpenReport "rpt_PICTS_CASES_XVI", acViewPreview, , strWhere

End Sub

Here is how I attempted to use your example in my situation:

Dim strSQL as String
Dim strOldSQL as String
strSQL = "SELECT REFDT, ASSIGNDT, CLOSEDT, PROSDT, LETTERDT, FINALDT FROM tblMyTableName1 " & _
     
"WHERE REFDT BETWEEN #" & Me.txtBeginRefDT & "# AND #" & _
Me.txtEndRefDT & "# " & _

"WHERE ASSIGNDT BETWEEN #" & Me.txtBeginAssignDT & "# AND #" & _
Me.txtEndAssignDT & "# " & _

"WHERE ASSIGNDT BETWEEN #" & Me.txtBeginAssignDT & "# AND #" & _
Me.txtEndAssignDT & "# " & _
.
.
.
strSQL = "SELECT STATDESC FROM tblMyTableName2
.
.
.
strSQL = "SELECT INVTYPE FROM tblMyTableName3
.
.
.
strOldSQL = fChangeSQL("qselMyQuery",strSQL)

I'm not sure if I'm in the right path or not. I appreciate your feedback.


TIA


OCM

 

[dhookom]

A SQL statement should have only one WHERE clause. I'm not sure what your second code is or does.

I typically start with the query's SQL statement in VBA. I then add the WHERE clause which is built based on the values entered into the controls. The last step is to use the SQL to replace the SQL property of a saved query.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016