type mismatch

[slimjen1]

All, using access 2003. I have a form with 3 unbound boxes. 1 for users to choose a division(named module) and the other two are date fields. I have a query to open and export to excel. When I choose the division and dates; click the excel export; I get type mismatched. The field for the dates in the query is formated mmddyyyy. Here's my code:

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

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"
    Case LTL
        strnewquery = "qryLTLReports"
    Case DTF
        strnewquery = "qryDTFReports"
    End Select
    
    If Me.cboFROM & vbNullString <> "" And Me.cboTO & vbNullString <> "" Then
        strSQL = strSQL & " ([MONTHPAID] BETWEEN " & Me.cboFROM & " And " & Me.cboTO & ") And "
    End If
    
       stDocName = "Select strnewquery * FROM strnewquery"
    If strSQL <> "" Then
        strSQL = Left(strSQL, (Len(strSQL) - 5))

        strnewquery = strnewquery & " WHERE " & strSQL & ";"
    End If

      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

Can anyone tell me what I am doing wrong.

 

[G37Sam]

I'm assuming the type mismatch error is coming from your SQL statement.

If that's the case, Debug.Print your strSQL in your immediate window and see how it looks like first.

 

[boblarson]

When posting problems it is important to note exactly which line of code is generating the error. It helps us narrow the focus down and not have to try to wade through a bunch of stuff that isn't necessary to wade through.

 

[slimjen1]

Thanks for the replies. I adopted this code from one of my earlier projects that work fine. The difference is I used the name "months" as the criteria if that makes any difference and my back end is in Sql 2008.

debug.print in the immediate window results:

[cboFROM]  like "5/1/2012" And [cboTO]  like "7/1/2012" And  ([MONTHPAID] BETWEEN# 5/1/2012# And# 7/1/2012#) And

When I step through the code it jumps to error handling after the following line:

 DoCmd.OpenQuery strnewquery, acViewNormal, strSQL

Can't figure out why
Thanks

 

[boblarson]

Can't figure out why
Thanks

Fairly obvious from the debug.print that you supplied. There is an AND at the end which needs to be chopped off. So before you do the OpenQuery part, use

If Right(strSQL, 5) = " AND " Then
   strSQL = Left(strSQL, Len(strSQL)-5)
End If

Also, you have a # without a space at the end of BETWEEN and you don't use LIKE with a date and the date needs octothorpes (#) as well.

 

[slimjen1]

I made the changes you suggested and more that I found. I didn't put "" around my case criteria. I think I repeated code where not needed. I blocked the code to build sql statement to see if that made any diff and I still recieved the error.

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

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"
    Case "LTL"
        strnewquery = "qryLTLReports"
    Case "DTF"
        strnewquery = "qryDTFReports"
    End Select
    
    If Me.cboFROM & vbNullString <> "" And Me.cboTO & vbNullString <> "" Then
        strSQL = strSQL & " ([MONTHPAID] BETWEEN #" & Me.cboFROM & "# And #" & Me.cboTO & "#)"
    End If
    
    stDocName = "Select strnewquery * FROM strnewquery"
    If Right(strSQL, 5) = " AND " Then
        strSQL = Left(strSQL, (Len(strSQL) - 5))

        strnewquery = strnewquery & " WHERE " & strSQL & ";"
    End If
Debug.Print strSQL
        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

On the form; I'm using the calendar contol to get the date in the cboto and cbofrom unbound field. "Monthpaid" is a date field format mmddyyyy.
The form itself is unbound. Why im I still getting this error if the datatypes are fine. This is from the immediate window:

 ([MONTHPAID] BETWEEN #5/1/2012# And #7/1/2012#)

 

[slimjen1]

Oh My! I just realized that I have too many sql statements. I don't seem to have the additional sql statement as written. It's just the statement I am trying to filter from the query. I copied some of this code (the part that filters the date) and added the case statement to distinguish between the divisions call modules. The only sql statement is what was printed in the immediate window. I need to regroup. All I want is to do is open the query, filter dates, by division then export the query to excel . I thought this would be simple enough. Even without the sql statement; i am still getting type mismatched. The datatypes are the same. I guess I need more practice but I need this code to work now.
any suggestions?

 

[slimjen1]

I just checked back in my query and the MonthPaid field is formatted as: yyyymmdd ie 2012-05-02. I know the code can be much simplier. There are only three unbound fields on the form: One combo box to choose the module( case statement). When the user selects the division it chooses the cooresponding query. Two text boxes for selection of the date. Using a date picker so the date formats mmddyyyy ie 5/2/2012. Is this whats giving me the type mismatch? Do you know a simple code to achieve what I want? Thanks so much