type mismatch

[slimjen]

This makes since now that pointed this out. My original adopted code had this:

Private Sub cmdExport_Click()
On Error GoTo Err_cmdExport_Click
 
Dim strSQL As String, intCounter As Integer
Dim ctl As Control, strname As String, strnewquery As String
 
Dim strRptSel As String
Dim stMessage As String
Set db = CurrentDb
      'Build SQL String
      For Each ctl In Me.Form
      If ctl.Tag = "input" Then
      'strname = "me." & ctl.Name
      If ctl.Value > "" Then
      strSQL = strSQL & "[" & ctl.Name & "] " & " like " & Chr(34) & ctl.Value & Chr(34) & " And "
      End If
      End If
 
     Next ctl
   
 
    ' Set the value of the parameter.
    Select Case Me.Module
    Case SP
       strnewquery = "[qrySPReports_test_passthru]"
    Case LTL
        strnewquery = "[qryLTLReports_test_passthru]"
    Case DTF
        strnewquery = "[qryDTFReports_test_passthru]"
    End Select
   
    If Me.cboFROM & vbNullString <> "" And Me.cboTO & vbNullString <> "" Then
        strSQL = strSQL & " ([MONTHPROCESSED] BETWEEN " & Me.cboFROM & " And " & Me.cboTO & ") And "
    End If
   
    'strnewquery = "Select [qrySPReports_test_passthru].* FROM [qrySPReports_test_passthru]"
 
    If strSQL <> "" Then
        strSQL = Left(strSQL, (Len(strSQL) - 5))
 
        strnewquery = strnewquery & " WHERE " & strSQL & ";"
    End If
 
    Debug.Print strnewquery
 
        ' Create the recordset
 
        Set rs = db.OpenRecordset(strnewquery)
        If rs.RecordCount > 0 Then
            DoCmd.OpenQuery strnewquery, acViewNormal, strSQL
            DoCmd.Close acForm, "frmREPORTBUILDER"
        Else
            MsgBox "There are no records that match your criteria! Please select new criteria.", vbInformation
            Exit Sub
        End If
        DoCmd.Minimize
        stDocName = strnewquery
        DoCmd.OpenQuery strnewquery, acViewNormal, strSQL
        DoCmd.Close acForm, "frmREPORTBUILDER"
            stDocName = strnewquery
            DoCmd.OpenQuery stDocName, acPreview
            DoCmd.OutputTo acOutputQuery, strnewquery, acFormatXLS
            DoCmd.Close strnewquery
 
Exit_cmdExport_Click:
    Exit Sub
 
Err_cmdExport_Click:
    Select Case Err.Number
        Case 2501 'OpenQuery action was cancelled
            Resume Exit_cmdExport_Click
        Case Else
            MsgBox Err.Description
            Resume Exit_cmdExport_Click
            Resume
        End Select
End Sub

I have to scratch this from the beginning because I need to have something in place now so the users can use it. Im open for suggestions.
Thank you for your patience.

[John_G]

Hi -

It turns out I was only half right. You can create a temporary query, but it has to be an action query, not a select query.

So, what you can do is
1) create a new, named query from your SQL
2) use docmd.transferspreadsheet to export to Excel - you don't even need to open the query
3) delete the query

Here is an example (tested):

Sub test()
Dim qdf As QueryDef
Dim SQL As String
SQL = "Select * from duty"
Set qdf = CurrentDb.CreateQueryDef("Query10", SQL) ' Creates and saves the query
DoCmd.TransferSpreadsheet acExport, , "query10", "c:\query10.xls" ' Export to where you need it
CurrentDb.QueryDefs.Delete "query10" ' Delete the query
End Sub

Check the help file for the various options on transferspreadsheet

John

[slimjen]

Thanks the code. I so appreciate the help. I had scratch and regroup. This is what I came up with:

Private Sub cmdExportArchive_Click()
On Error GoTo Err_cmdExportArchive_Click
 
Dim stDocName As String, strnewquery As String
 
    stDocName = "C:\Reports\Reports.xls"
  
     ' Choose what query to run
    Select Case Me.cboDivision
    Case "SP"
       strnewquery = "qrySPReports"
    Case "LTL"
        strnewquery = "qryLTLReports"
    Case "DTF"
        strnewquery = "qryDTFReports"
    Case Else
    strnewquery = "qrySPReports"
    End Select
   
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strnewquery, stDocName
   
    MsgBox "Report ready. Please check your Report folder on C drive.  Also, please be sure to change the name before running it again."
 
Exit_cmdExportArchive_Click:
    Exit Sub
 
Err_cmdExportArchive_Click:
   
MsgBox Err.Description
    Resume Exit_cmdExportArchive_Click
End Sub

I put parameter in the query to ask for start and end date.

I didn't see where you were defining a date in your sample.
This works with the exception that the only case statement that initializes is the "case else". When I select the others on the form it defaults to the query in the Case Else.
So close!

[June7]

Show the properties of the combobox: RowSource, ColumnCount, BoundColumn, ColumnWidths.

Step debug. What is value of the combobox read by the Select Case?

[slimjen]

RowSource= SELECT dbo_tblModules.ID, dbo_tblModules.txtMODABBV FROM dbo_tblMOdules;
ColumnCount=2 Bound Column=1 Column Widths=0",1"

I choose a division(module) from the list and step thru. When I hover over the selection; I get:

strnewquery=""

[slimjen]

I was trying to find reasons it wouldn't run and I read that the case statements cant be used in Access with Sql? I tried IF statement and it gave me the sames results.

[slimjen]

BINGO!!!!! Light bulb went on and I finally got it to work. The mention of Row source etc clicked for me. me.cboDivision.column(1). Thanks so much for you help and patience.