Export a query using SQL in VBA - using certain criteria

Question

Hello,

I have a form where people can search and filter records. They can preview reports based on the filtering criteria. They also wanted to be able to update records using that same filter. I was able to accomplish that running an SQL. However, they now want to be able to export the information in an Excel format based on their filters, this is the task I haven't been able to accomplish :(

I would appreciate it if you could help.

This is my Search Code

Sub Search()

   Dim DistrictName, strCriteria, task As String

   Dim strcriteria1, task1 As String

   Dim SchoolType As String

   Dim JPAName As String

   Dim LACOE As String

Me.Refresh

If IsNull(Me.cboDistrictName) Then

         strDistrictName = "[Districtname] like '*'"

   Else

         strDistrictName = "[Districtname]='" & Me.cboDistrictName & "'"

   End If

If IsNull(Me.cboSchoolType) Then

          strSchoolType = "[SchoolType] like '*'"

   Else

          strSchoolType = "[SchoolType]= '" & Me.cboSchoolType & "'"

   End If

If  IsNull(Me.cboJPAName) Then

        strJPAName = "[JPANamefk] like '*'"

   Else

         strJPAName = "[JPANamefk]= " & Me.cboJPAName

   End If

If IsNull(Me.CkLACOE) Then

          strLACOE = "[LACOE] like '*'"

   Else

         strLACOE = "[LACOE]=" & Me.CkLACOE

   End If

     If IsNull(Me.cboFiscalYearFrom) Or IsNull(Me.cboFiscalYearto) Then

strcriteria1 = strDistrictName & "And" & strSchoolType & "And" & strJPAName & "And" & strLACOE

                   task1 = "select * from searchpltoupdateinfo where " & strcriteria1

                    Me.SearchPLtoUpdateInfo_subform.Form.RecordSource = task1

                    Me.SearchPLtoUpdateInfo_subform.Form.Requery

Else

strCriteria = "([FiscalYearstart] >=#" & Me.cboFiscalYearFrom & "# And [FiscalYearstart] <=#" & Me.cboFiscalYearto & "#)" & "And" & strDistrictName & "And" & strSchoolType & "And" & strJPAName & "And" & strLACOE

                task = "Select * from Searchpltoupdateinfo where " & strCriteria

                Me.SearchPLtoUpdateInfo_subform.Form.RecordSource = task

                 Me.SearchPLtoUpdateInfo_subform.Form.Requery   

     End If

End Sub

------------The following is the code where users can update the invoice dates based on their filter. This works perfectly ------------

Private Sub cmdUpdateInvoiceDates ()

       Call Search

If IsNull(Me.cboFiscalYearFrom) Or IsNull(Me.cboFiscalYearto) Then

                    If Not IsNull(Me.txtInvoiceDate) And Not IsNull(Me.txtInvoiceDueDate) Then

                              DoCmd.RunSQL "UPDATE Searchpltoupdateinfo SET Searchpltoupdateinfo.[InvoiceDate] = [forms]!  [SearchPLtoUpdateInfo]![txtInvoiceDate], Searchpltoupdateinfo.[InvoiceDueDate] = [forms]![SearchPLtoUpdateInfo]![txtInvoiceDueDate] where " & strcriteria1

                  ElseIf IsNull(Me.txtInvoiceDate) And Not IsNull(Me.txtInvoiceDueDate) Then

        DoCmd.RunSQL "UPDATE Searchpltoupdateinfo SET Searchpltoupdateinfo.[InvoiceDueDate] = [forms]![SearchPLtoUpdateInfo]![txtInvoiceDueDate] where " & strcriteria1

                  ElseIf IsNull(Me.txtInvoiceDueDate) And Not IsNull(Me.txtInvoiceDate) Then

        DoCmd.RunSQL "UPDATE Searchpltoupdateinfo SET Searchpltoupdateinfo.[InvoiceDate] = [forms]![SearchPLtoUpdateInfo]![txtInvoiceDate] where " & strcriteria1

                 End If

     Else

                  If Not IsNull(Me.txtInvoiceDate) And Not IsNull(Me.txtInvoiceDueDate) Then

        DoCmd.RunSQL "UPDATE Searchpltoupdateinfo SET Searchpltoupdateinfo.[InvoiceDate] = [forms]![SearchPLtoUpdateInfo]![txtInvoiceDate], Searchpltoupdateinfo.[InvoiceDueDate] = [forms]![SearchPLtoUpdateInfo]![txtInvoiceDueDate] where " & strCriteria

                 ElseIf IsNull(Me.txtInvoiceDate) And Not IsNull(Me.txtInvoiceDueDate) Then

        DoCmd.RunSQL "UPDATE Searchpltoupdateinfo SET Searchpltoupdateinfo.[InvoiceDueDate] = [forms]![SearchPLtoUpdateInfo]![txtInvoiceDueDate] where " & strCriteria

                ElseIf IsNull(Me.txtInvoiceDueDate) And Not IsNull(Me.txtInvoiceDate) Then

        DoCmd.RunSQL "UPDATE Searchpltoupdateinfo SET Searchpltoupdateinfo.[InvoiceDate] = [forms]![SearchPLtoUpdateInfo]![txtInvoiceDate] where " & strCriteria

                End If

  End If

End Sub

When I tried to use the same syntax for a QueryDef, it DIDN'T work.

(What I am trying to accomplish: filter the records and based on that filter, export the information in a Excel format)

Private Sub cmdExportDecPageInfo_Click()

       Call Search      

       If  IsNull(Me.cboFiscalYearFrom) Or IsNull(Me.cboFiscalYearto) Then

                   Dim qdf1 as DAO.QueryDef

                    Set qdf1 = CurrentDB.CreateQueryDef( "DecPages1", "SELECT * FROM SearchQ Where " & strcriteria1")

                    DoCmd.OutputTo acOutputQuery, "DecPages1", (xlsx), , TRUE

                    CurrentDB.QueryDefs.Delete "DecPages1"

       Else

                   Dim qdf as DAO.QueryDef

                   Set qdf = CurrentDB.CreateQueryDef( "DecPages", "SELECT * FROM SearchQ Where " & strCriteria")

                   DoCmd.OutputTo acOutputQuery, "DecPages", (xlsx), , TRUE

                    CurrentDB.QueryDefs.Delete "DecPages"

       End If

Your help will be much much appreciated!

***Post moved by the moderator to the appropriate forum category.***

Answer 1

It seems like it is working now, I removed the extra double quote.

 Before ----Set qdf = CurrentDB.CreateQueryDef( "DecPages", "SELECT * FROM SearchQ Where " & strCriteria")

After --- Set qdf = CurrentDB.CreateQueryDef( "DecPages", "SELECT * FROM SearchQ Where " & strCriteria)

Answer 2

I would keep a saved query named "qryForExport" and then simply change the SQL property prior to exporting. This removes the code to create and delete the query. Just use the same one.

DoCmd.OutputTo acOutputQuery, "DecPages1", acFormatXLS, , TRUE