Data type mismatch in criteria expression? (1 Viewer)

(Using Access 2003 with file saved as Access 2000)

Hi,

I have a form to display the hours worked by employees for a given four week period and hours carried forward under a flex time system.

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 stored hours.

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?

Thanks!

Frozbie

More info:

I have a form: frm_Hours_Worked with two sub forms:
Sfrm_Hours_Worked
Sfrm_Hours_Worked_Update

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:

You need to set a reference to DAO. [Read here]

Hi SJ,

Thanks for your reply.

I did have a reference to DAO added. I've attached a picture of all references currently set in the VBE.

Would it be helpful to upload the database? It is quite large now, even as a zip file (380kb)

Mark,
Frozbie
(I made the mistake of changing my email address in the profile at work and am currently locked out of my usual profile/user name!

Move the DAO reference into 4th place.

Thanks again for taking the time to look at this.

I have moved the DAO reference into fourth place and tried running the routine again. I still get the same Data type mismatch in criteria expression and the debugger is looking at the same line:

Set rs = qdf.OpenRecordset()

I closed the database, reopened, same error message. Tried moving the DAO reference to seventh place and repeated with no joy.

The weird thing is I have used virtually identical bits of code which reference text and combo boxes on forms in several parts of my system with no problems. I have moved the DAO reference back to fourth place...

Mark
(Frozbie)

are you sure about these lines

qdf("Forms!frm_Hours_Worked!cbxPeriod") = Forms!frm_Hours_Worked!cbxPeriod '.Value
qdf("Forms!frm_Hours_Worked!txtPeriodEnd") = Forms!frm_Hours_Worked!txtPeriodEnd '.Value

Click to expand...

they imply a querydef object called:-
Forms!frm_Hours_Worked!txtPeriodEnd

Peter

They certainly look suspicious. I hadn't noticed them at first. I was distracted by the Rem statements - I haven't seen that word used in code for years.

Peter, SJ,

I was sure about the lines until you questioned them...

In the queries I am using, I capture the start and end dates of the periods I want to look at from the main form frm_Hours_Worked

the SQL for qry_Hours_Worked_Update is below:

SELECT tbl_Hours_Worked.EmpHoursID, tbl_Hours_Worked.FK_EmpID, tbl_Hours_Worked.RotaPeriodEnd AS Expr1, tbl_Hours_Worked.HoursWorked, tbl_Hours_Worked.BalanceCF, tbl_Hours_Worked.Comments, qry_Hours_Worked_balCF_3.HoursWorkedSum, qry_Hours_Worked_balCF_3.BalCF
FROM qry_Hours_Worked_balCF_3 LEFT JOIN tbl_Hours_Worked ON qry_Hours_Worked_balCF_3.EmpID = tbl_Hours_Worked.FK_EmpID
WHERE (((tbl_Hours_Worked.RotaPeriodEnd) Like [Forms]![frm_Hours_Worked]![txtPeriodEnd]));

As you will see from the code I pasted into my first posting, I declare and set a QueryDef object and then pass it two parameters. The above query uses one of the parameters but two of the queries it refers to use the first parameter and a second.

It is quite messy but does allow me to view the information I need in the form. However, if there is a more efficient way of going about this I would appreciate any pointers or links to tutorials etc.

I appreciate your help!

Mark
(Frozbie)

I, personally, would refer to my parameters like this.

or loop through them:-

Dim db As dao.Database
Dim qdf As dao.QueryDef
Dim rs As dao.Recordset
Dim prm As dao.Parameter
Set db = CurrentDb()


DoCmd.OpenForm "frm_Hours_Worked", , , , , acDialog
Set qdf = db.QueryDefs("qry_Hours_Worked_Update")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm


Rem !!!!!!!!!!!!!!!!!!!!!!!

Peter

I can only see one of the parameters being used in the SQL also.

Also once you get it to compile it won't run anyway

You are opening the form as dialog, that will stop the code running until the form is closed or hidden!

Peter

Peter, SJ,

Thanks for your help!

I like the advice on declaring parameters, much more readable!

I will look into the form being opened as dialog tomorrow. I'll post a reply and let you know how its gone!

Mark
(Frozbie)

Sorted! (well, it works at least!)

SJ, Peter,

Thanks for all your help. I have put together something that works. I'll paste the code below so you can have a look at it.

I ended up ditching the parameters and replaced them with functions. The functions return date values that are set when the user selects the period they want to view. I am still not sure why I was getting the error message which is annoying but perhaps the lesson I really need to learn is to try a different method when I get stuck and come back to it later.

Since the query I was trying to use in my recordset was based on four other queries and two tables, and three of the queries used dates as parameters, it could be the whole thing was doomed from the start.

Now, using the queries and code below I can view the existing hours value that is stored in the table and the actual hours which are calculated from live data, AND if there are differences between the two, the user can update the stored hours with the new Actual hours!

I was banging my head against a wall today and really benefitted from your comments! Thanks!

The VBA code is below.

Mark
(Frozbie - I will get my name back!!!)

Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click

If MsgBox("All records that do not match will be updated with Actual Hours. Do you want to continue?", vbOKCancel) = vbOK Then

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset ' recordset to hold query values : qry_Hours_Worked_Update
Dim rsU As DAO.Recordset ' recordset based on table tbl_Hours_Worked

Set db = CurrentDb()

Set qdf = db.QueryDefs("qry_Hours_Worked_Update")

Set rs = qdf.OpenRecordset()
Set rsU = db.OpenRecordset("tbl_Hours_Worked", dbOpenDynaset)

rs.MoveFirst

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
rsU.FindFirst "EmpHoursID = " & rs!EmpHoursID
rsU.Edit
rsU!HoursWorked = rs!HoursWorkedSum
rsU!BalanceCF = rs!BalCF
rsU.Update
End If
rs.MoveNext
Loop
End If

Me.sfrm_Hours_Worked_Update.Requery

Exit_cmdUpdate_Click:
Exit Sub

Err_cmdUpdate_Click:
MsgBox Err.Description
Resume Exit_cmdUpdate_Click

End Sub