type mismatch

[slimjen]

All, using access 2003. I've been trying to write this code. I have a form with 3 unbound controls. 1 for users to choose a module and the other two are date fields based on a query to open with results of the unbound form 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. So it's the same datatype as the date controls on the form. 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

Help please. Thanks

[June7]

In VBA, SQL statements require text and date values to be delimited. Text uses apostrophe, date uses # character.

strSQL = strSQL & " ([MONTHPAID] BETWEEN #" & Me.cboFROM & "# And #" & Me.cboTO & "#) And "

[slimjen]

Thank you for replying. This doesnt deter the error. Does it make a difference that I forgot to state I am working with a sql server 2008 backend?
Thanks

[slimjen]

Thank you for replying. This doesnt deter the error.
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

[John_G]

Hi -

In your Case statement, if SP, LTL and DTF are strings, they need to be in quotes, i.e. Case "SP" etc.

John

[slimjen]

I made the changes you suggested and more that I found. 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#)

[John_G]

When you get the error, what does strSQL contain, i.e. what is the SQL of the query?

[slimjen]

This is the SQL View of the query:

SELECT dbo_tblSPShipInfo.txtINVOICENUM AS INVOICENUM, dbo_tblSPShipInfo.txtPRONUM AS PRONUM, dbo_tblSPInvoice.txtCARRIERID AS CARRIERID, dbo_tblSPInvoice.txtCARRIERNAME AS CARRIERNAME, dbo_tblSPInvoice.txtCARRIERACCTNUM AS CARRIERACCTNUM, dbo_tblSPInvoice.dteDATAENTDTE AS DATAENTRYDATE, dbo_tblSPInvoice.dteINVOICEDTE AS INVOICEDATE, dbo_tblSPInvoice.dteMONTHPAID AS MONTHPAID, dbo_tblSPInvoice.memCOMMENT AS COMMENT, dbo_tblSPShipInfo.txtTRACKINGNUM AS TRACKINGNUM, dbo_tblSPShipInfo.dteSHIPDTE AS SHIPDATE, dbo_tblSPShipInfo.txtSHIPTYPE AS SHIPTYPE, dbo_tblSPShipInfo.numTOTALCHGS AS TOTALCHGS, dbo_tblSPShipInfo.numFUELSURCHG AS FUELSURCHG, dbo_tblSPShipInfo.numACCESCHG AS ACCESSCHG, dbo_tblSPShipInfo.numOTHCHG AS OTHERCHG, dbo_tblSPShipInfo.numTRANSCHG AS TRANSCHG, dbo_tblSPShipInfo.txtSALESORDNUM AS SALESORDNUM, dbo_tblSPShipInfo.txtSENDERNAME AS SENDERNAME, dbo_tblSPShipInfo.txtSENDERCITY AS SENDERCITY, dbo_tblSPShipInfo.txtSENDERSTATE AS SENDERSTATE, dbo_tblSPShipInfo.txtSENDERZIP AS SENDERZIP, dbo_tblSPShipInfo.txtRECIPNAME AS RECIPNAME, dbo_tblSPShipInfo.txtRECIPCITY AS RECIPCITY, dbo_tblSPShipInfo.txtRECIPSTATE AS RECIPSTATE, dbo_tblSPShipInfo.txtRECIPZIP AS RECIPZIP, dbo_tblSPShipInfo.numWEIGHT AS WEIGHT, [numTOTALCHGS]/[numWEIGHT] AS CostPerPound, dbo_tblSPShipInfo.txtSERVTYPE AS SERVICETYPE, dbo_tblSPShipInfo.txtCOSTCENTER AS COSTCENTER, dbo_tblSPShipInfo.txtACCOUNTCODE AS ACCOUNTCODE, dbo_tblSPShipInfo.txtDIVISION AS DIVISION, dbo_tblSPShipInfo.txtRESPFOREXP AS RESPFOREXP, dbo_tblSPShipInfo.txtEXPTYPE AS EXPTYPE, dbo_tblSPShipInfo.txtSENDERCTRYREG AS SENDERCTRYREG, dbo_tblSPShipInfo.txtRECIPCTRYREG AS RECIPCTRYREG, dbo_tblSPShipInfo.dteIMPORTDATE AS IMPORTDATE
FROM dbo_tblSPInvoice INNER JOIN dbo_tblSPShipInfo ON dbo_tblSPInvoice.txtINVOICENUM = dbo_tblSPShipInfo.txtINVOICENUM
ORDER BY dbo_tblSPShipInfo.txtINVOICENUM;

When I debug.print the strsql in the immediate window is:

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

[slimjen]

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

[John_G]

I think I found it -

Your error is here : DoCmd.OpenQuery strnewquery, acViewNormal, strSQL

The third parameter of the openquery is supposed to be a numeric constant representing the datamode - you have given it a string, hence the type mismatch. (This is for A2003, bit I doubt it changed)

What did you want strSQL to do in the OpenQuery statement?

John

[slimjen]

This might explain why Im still getting the error after changing the code. I adopted this code from another project of mine and cant remember after being frustrated for two days what it does. All I want is for the query to export based on the forms criteria. I changed the code by putting the criteria directly in the query:

Between Format([forms]![frmREPORTBUILDER]![txtStartdate,"mm/dd/yyyy"]) And Format([forms]![frmREPORTBUILDER]![txtEnddate,"mmddyyyy"])

Tried with and w/o the date format.
This is the code reworked:

Dim strSQL As String, strnewquery As String
    ' Set the value of the parameter.
    Select Case Me.Module
    Case "SP"
       strnewquery = "qrySPReports"
    Case "LTL"
        strnewquery = "qryLTLReports"
    Case "DTF"
        strnewquery = "qryDTFReports"
    End Select
       
    stDocName = "Select strnewquery * FROM strnewquery"
Debug.Print strnewquery
        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

Besides getting the error; I don't think my case statement is working because when I step thru the code it returns ""
I am open for suggestions.

[John_G]

If the case statement does seem to be setting strnewquery, then you need to check what me!module is (debug.print or msgbox).

When your code fails, which line is it failing on? (it should be highlighted if you use debug). Does it get as far as closing the form?

[slimjen]

When I step thru; it errors at:

DoCmd.OpenQuery strnewquery, acViewNormal, strSQL

I cant remember why I put strSQL. I think its failing there. But when I take it out; I get "this action requires a query named argument. I can'tget it to do a debug.print after the case statement.
Ugh!

[John_G]

Again this is from A2003 -

The first argument of the docmd.openquery command must be the name of an existing query - it cannot be an SQL string. I suspect A2007 is the same - check the Help to verify that.
What the error message is telling you is that it cannot find a query named "Select...."

All is not lost, however - there is a way of creating, using, but not saving a query like you are trying to do - I don't recall exactly how, so I'll have to get back to you tomorrow.

John

[June7]

I should have looked at all your code more carefully. The OpenQuery method does not work with a sql statement constructed in VBA, it opens Access query object. http://msdn.microsoft.com/en-us/libr...ffice.11).aspx An sql SELECT statement in VBA is used to: open a virtual recordset; or to set the RecordSource of form or report; or set the definition of a query object. A recordset cannot be exported with OutputTo. The OutputTo method is used with Access objects (table, query, form, report), not virtual recordset.
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx