INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Hi, I created a form for users t

Hi, I created a form for users t

(OP)
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:

CODE -->

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

RE: Hi, I created a form for users t

(OP)
Thanks Duane,

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

TIA,

OCM

RE: Hi, I created a form for users t

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

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

RE: Hi, I created a form for users t

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

When I click ‘Export’ button nothing happens.

CODE -->

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

RE: Hi, I created a form for users t

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

RE: Hi, I created a form for users t

(OP)
Duane,

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

Regards,

OCM

RE: Hi, I created a form for users t

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

RE: Hi, I created a form for users t

(OP)
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.

CODE -->

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:

CODE -->

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

RE: Hi, I created a form for users t

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close