Error 13 Type Mismatch

[jasperz01]

Hello,

I'm kind of new to VBA and Access, so please forgive me if I'm doing stuff all wrong:

I have the following code wich gives me a Error 13 Type Mismatch:

 
    Dim db As Database, rs As Recordset
    Dim strSQL As String
    Dim ToDate, TillDate As String
 
    ToDate = "" & GP_DateFrom.Value & ""
    TillDate = "" & GP_DateUntil.Value & ""
 
 
    strSQL = "SELECT * FROM SBB_General_Purpose WHERE Datum BETWEEN " _
              & ToDate & " AND " & TillDate & ";"
 
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSQL)
 
    ' if it gets here then the code to open the recordset has worked
    lstDisplay.Requery
    rs.Close
    db.Close
 
 
 

It gives the error on the Set rs=db.OpenRecordset(strSQL) line.
Anyone any ideas on how to resolve this?

Thanks in advance,

Jasper

[Killer42]

datum is a date type field, right? If so, you need to place # delimiters around the date values in the SQL string. See the Access article which explains the issue.

Also, I believe SQL requires dates to be in U.S. format (m/d/y), which may or may not be a problem. I'd suggest you halt execution after the line that sets strSQL, and display the value in the string. Post it here if you like, and we can see how it looks..

[jasperz01]

Yes, you are right, it is a date field. However: I tried using # around the dates, this did not work. Also, I get the same error when I don't use any WHERE-statement at all:

   ' Now without the WHERE 
 
    strSQL = "SELECT * FROM SBB_General_Purpose"
 
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSQL)
 
 

Gives me the same error. Strange, ain't it?

Jasper

[ansumansahu]

Yes, you are right, it is a date field. However: I tried using # around the dates, this did not work. Also, I get the same error when I don't use any WHERE-statement at all:

   ' Now without the WHERE 
 
    strSQL = "SELECT * FROM SBB_General_Purpose"
 
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSQL)
 
 

Gives me the same error. Strange, ain't it?

Jasper

Hi ,

The basic reason for this error is that the SQL syntax is incorrect. But from your query it does not seem so. Are you able to execute the query sucessfully on the query analyser.

good luck
-ansuman sahu

[jasperz01]

Hi ,

The basic reason for this error is that the SQL syntax is incorrect. But from your query it does not seem so. Are you able to execute the query sucessfully on the query analyser.

good luck
-ansuman sahu

I'll check the query analyser, i'll post the result in a moment.

[jasperz01]

Back ;)

I can use this query without any probs in the Access Query analyzer:

SELECT * FROM SBB_General_Purpose WHERE Datum BETWEEN #01-01-1900# AND #01-01-2100#

It also works when I lose the WHERE.... statement.
Gives me 2 results, which is OK since I only have 2 records in the DB at the moment (I'm still developing it).

So I guess my SQL statement is OK. I'm kinda stuck here. Any suggestions?

[Killer42]

Perhaps some sort of problem with your connection string? I'm pretty vague on that whole area.

[jasperz01]

Just found it. I declared rs as a recordset, it should be a DAO.Recordset. Works pretty nice now :)

For the ones interested, here is the complete code. It does a query based on date-input on a form, then exports this data to Excel:

Private Sub cmdOk_Click()
    On Error GoTo Err_cmdOk_Click
 
    Dim db As Database, rs As DAO.Recordset
    Dim strSQL As String
    Dim ToDate, TillDate As String
    Dim i As Integer
    Dim iNumCols As Integer
 
    'Start a new workbook in Excel
    Dim oApp As New Excel.Application
    Dim oBook As Excel.Workbook
    Dim oSheet As Excel.Worksheet
 
    ' Start date was empty, assume user wants all history so set it to 1-1-1990
    If GP_DateFrom.Value = "" Then
      GP_DateFrom = "01-01-1900"
    End If
 
    If GP_DateFrom = "01-01-1900" Then
      Answer = MsgBox("Are you sure you want to see all data from 01-01-1900 till " & GP_DateUntil.Value & "?", vbYesNo, "Are you sure?")
      If Answer = vbNo Then Exit Sub
    End If
 
    ' SQL uses mm-dd-yyyy, our users give us dd-mm-yyyy, so let's change that
    ' also add a # in front and at the end to make MS happy
    ToDate = "#" & Mid(GP_DateFrom.Value, 4, 2) & "-" & Left(GP_DateFrom.Value, 2) & _
             "-" & Right(GP_DateFrom.Value, 4) & "#"
    TillDate = "#" & Mid(GP_DateUntil.Value, 4, 2) & "-" & Left(GP_DateUntil.Value, 2) & _
             "-" & Right(GP_DateUntil.Value, 4) & "#"
 
    ' This should be the final SQL cmd
    strSQL = "SELECT * FROM SBB_General_Purpose WHERE Datum BETWEEN " _
              & ToDate & " AND " & TillDate & ";"
 
 
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSQL)
 
    If Not rs.BOF And Not rs.EOF Then ' it has records
 
      iNumCols = rs.Fields.Count
 
      Set oBook = oApp.Workbooks.Add
      Set oSheet = oBook.Worksheets(1)
 
      For i = 1 To iNumCols
          oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
      Next
 
      ' Add the data starting at cell A2
      oSheet.Range("A2").CopyFromRecordset rs
 
      'Format the header row as bold and autofit the columns
      With oSheet.Range("a1").Resize(1, iNumCols)
        .Font.Bold = True
        .EntireColumn.AutoFit
      End With
 
      oApp.Visible = True
      oApp.UserControl = True
    Else
 
      MsgBox "No data in query!", vbOKOnly, "No data"
 
    End If
 
    ' Close the DB
    rs.Close
    db.Close
 
    Exit Sub
 
Err_cmdOk_Click:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error " & Err.Number
    Resume Next
 
End Sub
 
 

Hope this might be usefull for some, and thanks for any input you gave me guys, I appreciate it :)

Jasper

[Killer42]

Glad to see you got it. :)

And thanks for posting the working code. If I can find time, I might edit it a bit (for consistency with other entries) and add it to the "how-to samples" in the VB Articles area. We often get questions about things like importing to Excel, and database-related ones. As a sample, this will hit two areas in one go.

[SulPal]

hI.. i am getting the same error.

The thing is ive used this code else where and it didn't give me problems but in this particular case where I have it it does.

Function GetDateFilter(dtDate As Date) As String
GetDateFilter = "#" & Format(dtDate, "DD/MM/YYYY hh:mm:ss AM/PM") & "#"
End Function

The above function is called in the following code....


If IsDate(Me.Date_Project_Modified) Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Query2.Date_Project_Modified >= " & GetDateFilter(Me.Date_Project_Modified)
ElseIf Nz(Me.Date_Project_Modified) <> "" Then
strError = cInvalidDateError
End If


Any ideas?

[Killer42]

Which line produces the error?

Oh! And what version of VB are you using?

And what does Me refer to?