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:

[CODE=vb]

Dim db As Database, rs As Recordset
Dim strSQL As String
Dim ToDate, TillDate As String

ToDate = "" & GP_DateFrom.Val ue & ""
TillDate = "" & GP_DateUntil.Va lue & ""


strSQL = "SELECT * FROM SBB_General_Pur pose WHERE Datum BETWEEN " _
& ToDate & " AND " & TillDate & ";"

Set db = CurrentDb()
Set rs = db.OpenRecordse t(strSQL)

' if it gets here then the code to open the recordset has worked
lstDisplay.Requ ery
rs.Close
db.Close


[/CODE]

It gives the error on the Set rs=db.OpenRecor dset(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_Pur pose 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:

[CODE=vb]
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.Applicati on
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.Val ue = "" 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.Va lue & "?", 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_DateFro m.Value, 2) & _
"-" & Right(GP_DateFr om.Value, 4) & "#"
TillDate = "#" & Mid(GP_DateUnti l.Value, 4, 2) & "-" & Left(GP_DateUnt il.Value, 2) & _
"-" & Right(GP_DateUn til.Value, 4) & "#"

' This should be the final SQL cmd
strSQL = "SELECT * FROM SBB_General_Pur pose WHERE Datum BETWEEN " _
& ToDate & " AND " & TillDate & ";"


Set db = CurrentDb()
Set rs = db.OpenRecordse t(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.Worksheet s(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("A 2").CopyFromRec ordset rs

'Format the header row as bold and autofit the columns
With oSheet.Range("a 1").Resize(1 , iNumCols)
.Font.Bold = True
.EntireColumn.A utoFit
End With

oApp.Visible = True
oApp.UserContro l = 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

[/CODE]

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(d tDate 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_Modifie d) Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Query2.Date_Pr oject_Modified >= " & GetDateFilter(M e.Date_Project_ Modified)
ElseIf Nz(Me.Date_Proj ect_Modified) <> "" Then
strError = cInvalidDateErr or
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?