Data type mismatch in criteria expression error

J

Jonny

Registered User.
Local time
Today, 11:56
Joined
Aug 12, 2005
Messages
144
Trying to create a form that updates FinishWork for employee. However getting a error.
Code: 
Private Sub btnFinishWork_Click()
'    MsgBox cboID
'    MsgBox txtFinishWork

    CurrentDb.Execute "UPDATE EmpList " & _
                    " SET FinishWork =" & txtFinishWork.Value & _
                    " WHERE FinishWork Is Null AND ID =" & cboID.Value & ";"

    Me.Requery
End Sub
Important remark, the data type of ID in EmpList is Short Text and cannot be changed.
 
Last edited:
Text needs to be surround by single quotes ' (or a three double quotes (I think))

You do not need the .Value property and I would be using the Me. prefix as well.?

Edit: A date would need # on either end I believe.

HTH
 
Last edited:
I agree with Gasman re quotes on string/text datatype.
You start by saying FinishDate, but you are referencing FinishWork in the query???
 
My attempt
Code: 
Private Sub btnFinishWork_Click()
'    MsgBox cboID
'    MsgBox txtFinishWork

    CurrentDb.Execute "UPDATE EmpList " & _
                    " SET FinishWork = #" & Me.txtFinishWork & "#" _
                    " WHERE FinishWork Is Null AND ID =[COLOR="Red"]'[/COLOR]" & Me.cboID & "[COLOR="red"]'[/COLOR];"

    Me.Requery
End Sub

HTH
 
Was a typo, FinishWork is correct , I've changed the code as you said and then got an error
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.
Click to expand...
Code: 
Private Sub btnFinishWork_Click()
'    MsgBox cboID
'    MsgBox dteFinishWork

    CurrentDb.Execute "UPDATE EmpList " & _
                    " SET FinishWork =" & Me.txtFinishWork & _
                    " WHERE FinishWork Is Null AND ID =" & """ & Me.cboID & """

    Me.Requery
End Sub
 
Code: 
" WHERE FinishWork Is Null AND ID = [COLOR="Red"]'[/COLOR]" & cboID & "[COLOR="red"]'[/COLOR] ;"

Two comments:

First, note the red apostrophes, which might help that line. That is ONE way you could make a comparison of a substituted text value. (Not the only way, but an easy way.)

Second, regarding cboID: I am going to assume that is a combo box, which means you have at least one column but COULD have more. If the cboID is multi-column, you never need the .Value property because that is the default property chosen for anything that has a value. However, you MIGHT need .Column(n) if the bound column isn't column 0. Remember, combo-box columns number starting from 0, not 1.
 
Well only you know what FinishWork is for and why it has to be unique. If it does not, then change the table.?

Look for that value in the table and see what ID is on the record.?

Jonny said:
Was a typo, FinishWork is correct , I've changed the code as you said and then got an error
Code: 
Private Sub btnFinishWork_Click()
'    MsgBox cboID
'    MsgBox dteFinishWork

    CurrentDb.Execute "UPDATE EmpList " & _
                    " SET FinishWork =" & Me.txtFinishWork & _
                    " WHERE FinishWork Is Null AND ID =" & """ & Me.cboID & """

    Me.Requery
End Sub
Click to expand...
 
What is the Primary Key of table Emplist?
The error indicates you have a duplicate and since you're updating FinishWork, it seems that field can not have duplicates in your table.
 
jdraw said:
What is the Primary Key of table Emplist?
The error indicates you have a duplicate and since you're updating FinishWork, it seems that field can not have duplicates in your table.
Click to expand...
The primary key in EmpList is "ID"
Second, regarding cboID: I am going to assume that is a combo box, which means you have at least one column but COULD have more. If the cboID is multi-column, you never need the .Value property because that is the default property chosen for anything that has a value. However, you MIGHT need .Column(n) if the bound column isn't column 0. Remember, combo-box columns number starting from 0, not 1.
Click to expand...
cboID is a combobox that is populated from ID of EmpList.
The raw source of cboID is "SELECT EmpList.ID FROM EmpList ORDER BY EmpList.ID;"
 
Is FinishWork a Date datatype?
It seems there's been a change somewhere since I first saw this post.

Originally, you posted
Quote:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

Now we are seeing a datatype mismatch???

Dates must be surrounded with octothorpe/hash (#)
 
Other way around I think jdraw

O/P initially had a mismatch and when corrected and code working it then complained about the duplicate key.



jdraw said:
Is FinishWork a Date datatype?
It seems there's been a change somewhere since I first saw this post.

Originally, you posted
Quote:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

Now we are seeing a datatype mismatch???

Dates must be surrounded with octothorpe/hash (#)
Click to expand...
 
10-4 Gasman. Seems my cart jumped in front of my horse...
 
same advice as the others.
better sync the textbox with the date in the table.
 

Attachments

Great, thank you!
How come that now trying to open up thr original database, I do not see neither the tables, not the relationships not the queries..:confused:
Seeing one table only "MSysCompactError" with a list of errors..
 
how come!?
post the db. maybe we can recover the deleted tables!
 
You must log in or register to reply here.

Similar threads

N
Solved How to update a currentstock control in the subform after a requery from the parent form
Replies
16
Views
784
Mark_
M
T
Solved It takes too long to download data from server
Replies
8
Views
281
GPGeorge
GPGeorge
T
Can someone please assist with the VBA code Below - Error Message 3075
Replies
16
Views
1,047
CJ_London
C
S
Data type mismatch in criteria expression
Replies
5
Views
2,836
The_Doc_Man
The_Doc_Man
N
Solved How to remove this error "the execution this software stopped due to run time" error in MS Access VBA
Replies
11
Views
796
Minty
Minty

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom