(Using Access 2003 with file saved as Access 2000)
I have a form to display the hours worked by employees for a given four week period and hours carried forward under a flex
Under the system I have created, users can save the hours worked and balance carried forward to a table, at the end of a
period. But, after this point, changes can be made to the shifts and so the saved hours may be wrong. So, I am trying to
create a system that allows the user to view stored hours and actual hours and if there are differences, then update the
I am getting the following error when I try to access a recordset based on a query in VBA in order to make the changes:
Run-time error ‘3464’:
Data type mismatch in criteria expression.
I have searched through the VBA forum and queries forum and tried several of the possibilities from articles there, but still
am getting the error message.
I am fairly sure the data types of the parameters I am passing to the query are the same as that required by the query (and
the queries and tables it uses) but obviously am missing something.
(I wonder if it is something to do with the fact that the query I reference in the VBA only uses one parameter but two of the
queries it draws on use two parameters)
Can anyone point me in the right direction or offer a link to something that might help?
I have a form: frm_Hours_Worked with two sub forms:
There is a combo box and text box on the form which are both formatted as Short Date.
The subform, Sfrm_Hours_Worked_Update, displays a recordset from a query that draws from a table and second query. The second
query pulls data in from three other queries each of which refines data slightly.
When I click debug on the error message it highlights: Set rs = qdf.OpenRecordset()
The code follows:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Set db = CurrentDb()
DoCmd.OpenForm "frm_Hours_Worked", , , , , acDialog
Set qdf = db.QueryDefs("qry_Hours_Worked_Update")
qdf("Forms!frm_Hours_Worked!cbxPeriod") = Forms!frm_Hours_Worked!cbxPeriod '.Value
qdf("Forms!frm_Hours_Worked!txtPeriodEnd") = Forms!frm_Hours_Worked!txtPeriodEnd '.Value
Rem following line results in error message:
Rem Data Type Mismatch in Criteria expression
Rem as far as I can tell, the data types in both the queries and forms are short dates
Set rs = qdf.OpenRecordset()
Do While rs.EOF = False
Rem if there are differences between values of saved hours and actual hours or
Rem balance carried forward hours then
Rem change the saved hours to actual hours and balance hours as well
If rs!HoursWorked rs!HoursWorkedSum Or rs!BalanceCF rs!BalCF Then
rs!HoursWorked = rs!HoursWorkedSum
rs!BalanceCF = rs!BalCF