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

Not sure if you have references to both DAO and ADO in your database, but
you should disambigulate your variables:

'Dim strEndDate As String
Dim dbsVehID As DAO.Database
Dim qdfVehID As DAO.QueryDef
Dim rstVehID As DAO.Recordset
'Dim fld As Field
Dim sqlVehID As String

This may take care of your problem; if not, post back with the specific line
where you are hitting this error.

Also, you should NOT .Close your dbsVehID variable ... you didn't open it,
so you you would instead set it to Nothing:
Set dbsVehID = Nothing

Also, you should Close your querydef variable:
Set qdfVehID = Nothing

A minor note: your query string is a bit messy. No need to breakout the "#"
in your code:
qdfVehID.SQL = "SELECT VehicleID FROM ChargeLog WHERE STDATE BETWEEN #"
& GStartDate & "# AND #" & GEndDate & "#"

This won't help your problem, but "clean" code is easier to troubleshoot ...

"mark" <ms (AT) nospam (DOT) comcast.net> wrote

I think your problem is caused by your setting the variable
datdateselected = cboSTDATERange.Value I don't think you need the
..value, also you should be able to pass the field as the parameter and
not have to set a variable for that i.e.

Call ProcGetVehID(cboSTDATERange)

I hope this helps
Philippa

mark <ms (AT) nospam (DOT) comcast.net> wrote

..Value returns the actual Value (not the text typed in) of the combobox ...
referencing the control in this manner would certainly not give a Type
Mismatch error, but may very well result in capturing the wrong Date
information.

"Philippa" <philippa.speirs (AT) systemc (DOT) com> wrote