Why do I get a type mismatch error (13)?

[mark]

I have a form with the following code on Click that calls a procedure passing a
date data type value. The field in the table is a date/time data type.
What is causing the type mismatch when it reaches the line (Set rstVehID =
dbsVehID.OpenRecordset())? GStartDate and GEndDate are declared as public
variables. Thanks.

Private Sub cmdSelectSTDate_Click()
Dim datDateSelected As Date

cboSTDATERange.SetFocus
If IsNull(cboSTDATERange) = False Then
datDateSelected = cboSTDATERange.Value
Call ProcGetVehID(datDateSelected)
Else
MsgBox ("Please select a Date from the Drop Box, then click the [Select
Date] button")
cboSTDATERange.SetFocus
End If

End Sub
Private Sub ProcGetVehID(ByVal datDateSelected As Date)
'Dim strEndDate As String
Dim dbsVehID As Database
Dim qdfVehID As QueryDef
Dim rstVehID As Recordset
'Dim fld As Field
Dim sqlVehID As String

If datDateSelected = Null Then
GoTo err_ProcGetVehID
End If

If IsNull(datDateSelected) = False Then
GStartDate = datDateSelected
GEndDate = DateAdd("d", 6, datDateSelected)

Set dbsVehID = CurrentDb
Set qdfVehID = dbsVehID.QueryDefs("qryGetVehID")
qdfVehID.SQL = "SELECT VehicleID FROM ChargeLog WHERE STDATE BETWEEN #"
& GStartDate & "# AND " & "#" & GEndDate & "#"
Set rstVehID = dbsVehID.OpenRecordset()
End If

If Not rstVehID.EOF Or Not rstVehID.BOF Then
cboVehicleID.SetFocus
'cboVehicleID.Visible
'cboVehicleID.RowSource = rstVehID
cboVehicleID = rstVehID
Else
Exit Sub
End If

rstVehID.Close
dbsVehID.Close
Set rstVehID = Nothing
Set dbsVehID = Nothing

err_ProcGetVehID:
Exit Sub
end sub

[Peter Doering]

> Set qdfVehID = dbsVehID.QueryDefs("qryGetVehID")

qdfVehID.SQL = "SELECT VehicleID FROM ChargeLog WHERE STDATE BETWEEN #"
& GStartDate & "# AND " & "#" & GEndDate & "#"
Set rstVehID = dbsVehID.OpenRecordset()

Use instead:
Set rstVehID = dbsVehID.OpenRecordset(qdfVehID.SQL, dbOpenDynaset)

But you can easier fill a string ...

strSelect = "SELECT VehicleID FROM ChargeLog WHERE STDATE BETWEEN #"
& GStartDate & "# AND " & "#" & GEndDate & "#;"

.... and open it:

Set rstVehID = dbsVehID.OpenRecordset(strSelect, dbOpenDynaset)

Then you can get rid of the QueryDef which you're not using otherwise.

HTH - Peter

--
No mails please.

[Douglas J. Steele]

What version of Access? If you're using Access 2000 or 2002, when you added
the reference to DAO, did you remove the reference to ADO?

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rstVehID as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rstVehID As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"mark" <[e-mail]> wrote in message
news:3v********************@comcast.com...

I have a form with the following code on Click that calls a procedure passing a date data type value. The field in the table is a date/time data type.
What is causing the type mismatch when it reaches the line (Set rstVehID =
dbsVehID.OpenRecordset())? GStartDate and GEndDate are declared as public
variables. Thanks.

Private Sub cmdSelectSTDate_Click()
Dim datDateSelected As Date

cboSTDATERange.SetFocus
If IsNull(cboSTDATERange) = False Then
datDateSelected = cboSTDATERange.Value
Call ProcGetVehID(datDateSelected)
Else
MsgBox ("Please select a Date from the Drop Box, then click the [Select Date] button")
cboSTDATERange.SetFocus
End If

End Sub
Private Sub ProcGetVehID(ByVal datDateSelected As Date)
'Dim strEndDate As String
Dim dbsVehID As Database
Dim qdfVehID As QueryDef
Dim rstVehID As Recordset
'Dim fld As Field
Dim sqlVehID As String

If datDateSelected = Null Then
GoTo err_ProcGetVehID
End If

If IsNull(datDateSelected) = False Then
GStartDate = datDateSelected
GEndDate = DateAdd("d", 6, datDateSelected)

Set dbsVehID = CurrentDb
Set qdfVehID = dbsVehID.QueryDefs("qryGetVehID")
qdfVehID.SQL = "SELECT VehicleID FROM ChargeLog WHERE STDATE BETWEEN #" & GStartDate & "# AND " & "#" & GEndDate & "#"
Set rstVehID = dbsVehID.OpenRecordset()
End If

If Not rstVehID.EOF Or Not rstVehID.BOF Then
cboVehicleID.SetFocus
'cboVehicleID.Visible
'cboVehicleID.RowSource = rstVehID
cboVehicleID = rstVehID
Else
Exit Sub
End If

rstVehID.Close
dbsVehID.Close
Set rstVehID = Nothing
Set dbsVehID = Nothing

err_ProcGetVehID:
Exit Sub
end sub