Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: type mismatch

  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: type mismatch

    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:
    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by slimjen View Post
    The field for the dates in the query is formated mmddyyyy.

    Help please. Thanks
    format has (virtually) stuff all to do with datatype. Check the underlying datatypes are the same.

    you may need to do an explicit conversion from a value in a control if its unbound using say the cdate function fo r dates, cint, clng, etc.. IIRC the Access runtime uses strings for its storage in controls.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2004
    Posts
    214
    Thank you for replying. I am not quiet following your reply.

    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:

    Code:
    [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:
    Code:
     DoCmd.OpenQuery strnewquery, acViewNormal, strSQL
    Can't figure out why
    Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are supplying date literals then the date literal must be in US date format AND encapsualted by #

    how the SQL server parser will interpret
    Code:
    ([MONTHPAID] BETWEEN# 5/1/2012# And# 7/1/2012#)
    is anybodies guess
    Code:
    ([MONTHPAID] BETWEEN #5/1/2012# And #7/1/2012#)
    may be a better bet

    Im assuming that cbofrom, cboto & monthpaid are datetime columns
    Im not certain you can use like on dates though even if you can there are no wild card values to allow the SQL parser to use the like predicate
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2004
    Posts
    214
    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.

    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 & "#)"
        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:
    Code:
     ([MONTHPAID] BETWEEN #5/1/2012# And #7/1/2012#)
    I am trying to break the code down to see if I can get at least the case statments and the query to openL(

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can we see the actual SQL being sent to the SQL engine?
    ie the value of strSQL
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2004
    Posts
    214
    Oh My! I didn't catch this. 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

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    "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:

    Code:
    ([MONTHPAID] BETWEEN #5/1/2012# And #7/1/2012#)
    Are you sure about the field format? 'mmddyyyy' doesn't allow for slashes, and is, in itself, a text format and not a date format, so it's incorrect to use hash marks (#) as delimeters. You would have to use double quotes (") instead. The correct WHERE clause would be
    Code:
    ([MONTHPAID] BETWEEN "05012012" And "07012012")
    Sam

  9. #9
    Join Date
    Jul 2004
    Posts
    214
    Hi Sam; Thanks for the reply. 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

  10. #10
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by slimjen View Post
    Hi Sam; Thanks for the reply. 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
    Again, dashes and slashes cannot be added. I can't stress that enough. The only change to my previous line of code is the order of appearance:
    Code:
    ([MONTHPAID] BETWEEN "20120501" And "20120701")
    If you are using the Format() function, such as Format(YourDateField, "yyyymmdd"), it makes little difference what the actual order is, as long as you adhere to it throughout the code. Adding dashes or slashes, or deleting numbers (such as using "5" for May, instead of "05" (which matches the pattern "mm"), the program will not work.

    What I really do not understand is why you're using a format altogether. What's wrong with using the date field as a date? And never mind the format.

    Sam

  11. #11
    Join Date
    Jul 2004
    Posts
    214
    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 how I got it to work. All I want is for the query to export based on the forms criteria. I was trying to format the date when I got the error. I changed the code by putting the criteria directly in the query:




    [CODE]Between CVDate([forms]![frmREPORTBUILDER]![txtStartdateAnd CVDateFormat([forms]![frmREPORTBUILDER]![txtEnddateCODE] This is the code reworked:


    Code:
    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.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if your case statement compiles, then defacto it works, the fact it isn't doing what you expect suggests its your logic that is faulty.

    my guess would be that me.module isn't one of the 3 specified values in the case statement, and the fact you don't have a case else/default statement the value is uninitialised, which is never a smart call. either put some error trapping in there or set a default value or display an error message.

    again you refer to the way a date value is stored as aper format.. that aint the way Access/JET stores datetime values. the storage mechanins is a doiuble value (the integer part is the number of days since (IIRC 31/12/1899) the decimal part is the proportion of a day (0.5 = 12:00, 0.75 is 18:00 1/(24*60*60) is 1 second. that numerioc value can be displayed in any format you like

    the value from a date control could well be in a specific format, although the underlying true value will be a number. judicious use of the debugger will tell you all you need to know
    you can test the values by using the is<datatypefunctions> in the immediate window

    eg isnumeric(avalue)


    Im guessign your current code as posted is a cutdown version as you do not set strSQl at any stage
    stdocname is assigned a daft value that isn't valid SQL
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Me.Module refers to the default property of a Form or Report Module, the full reference being:
    Code:
    Me.Module.Name
    It is assigned by Access and has a structure such as:
    <PreFix>_<Object Name>
    Where <Prefix> is either "Form" or Report" and <Object Name> is the name of the concerned form or report.

    It's impossible that this property can be something like "SP", "LTL" or "DTF"
    Have a nice day!

  14. #14
    Join Date
    Jul 2004
    Posts
    214
    I so appreciate the help. I had scratch and regroup. This is what I came up with:

    Code:
    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.

    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!

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Again, I have the suspicion that the problem might come form the misunderstanding (hence the misuse) of the default property of an object. This:
    Code:
    Me.cboDivision
    is a reference to the default property of the Combobox [cboDivision], the full reference being:
    Code:
    Me.cboDivision.Value
    However, this property does not necessarily returns the contents of the Textbox part of the combo. The value returned by the Value property of a Combobox (the same is true for a Listbox too) is the value of the bound column of the object for the selected row, while the value displayed in the Textbox part of the combo is the value of the first column of the List part that has a non-zero width, also for the selected row. If you want to retrieve the value from another column than the bound column, you must use (in your case):
    Code:
    Me.cboDivision.Column(n)
    Where n is the index of the column, with the first column (leftmost) being Column(0).

    As an example:
    1. Table1:
    Code:
    RowID	Name
    ---------------
    1	Davis
    2	Smith
    3	Johnson
    4	Drake
    2. Combo1:
    RowSourceType = Table/Query
    RowSource = SELECT RowID, Name FROM Table1 ORDER BY Name;
    BoundColumn = 1 (As confusing as it may be this actually references Column(0))
    ColumnCount = 2
    ColumnWidths = 0cm;3cm

    3. When the form is open, I select the second line in the list of Combo1 (Drake).
    a) The textbox part of Combo1 now displays Drake
    b) Me.Combo1 (or Me.Combo1.Value or Me.Combo1.Column(0)) returns 4
    c) Me.Combo1.Column(1) returns Drake

    See also: Visual Basic Combo Box Tutorial | Visual Basic 6 (VB6)
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •