Data type mismatch in criteria expression error (2 Viewers)

J

Jonny

Registered User.
Local time
Today, 02:30
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:
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 00:30
Joined
Sep 21, 2011
Messages
14,350
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:
jdraw

jdraw

Super Moderator
Staff member
Local time
Yesterday, 19:30
Joined
Jan 23, 2006
Messages
15,383
I agree with Gasman re quotes on string/text datatype.
You start by saying FinishDate, but you are referencing FinishWork in the query???
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 00:30
Joined
Sep 21, 2011
Messages
14,350
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
 
J

Jonny

Registered User.
Local time
Today, 02:30
Joined
Aug 12, 2005
Messages
144
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
 
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 18:30
Joined
Feb 28, 2001
Messages
27,218
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.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 00:30
Joined
Sep 21, 2011
Messages
14,350
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...
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Yesterday, 19:30
Joined
Jan 23, 2006
Messages
15,383
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.
 
J

Jonny

Registered User.
Local time
Today, 02:30
Joined
Aug 12, 2005
Messages
144
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;"
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Yesterday, 19:30
Joined
Jan 23, 2006
Messages
15,383
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 (#)
 
J

Jonny

Registered User.
Local time
Today, 02:30
Joined
Aug 12, 2005
Messages
144
Sorry , here is it..
 

Attachments

  • test21.accdb
    604 KB · Views: 97
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 00:30
Joined
Sep 21, 2011
Messages
14,350
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...
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Yesterday, 19:30
Joined
Jan 23, 2006
Messages
15,383
10-4 Gasman. Seems my cart jumped in front of my horse...
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:30
Joined
May 7, 2009
Messages
19,247
same advice as the others.
better sync the textbox with the date in the table.
 

Attachments

  • test21.zip
    37.2 KB · Views: 107
J

Jonny

Registered User.
Local time
Today, 02:30
Joined
Aug 12, 2005
Messages
144
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..
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:30
Joined
May 7, 2009
Messages
19,247
how come!?
post the db. maybe we can recover the deleted tables!
 
You must log in or register to reply here.

Similar threads

S
Data type mismatch in criteria expression
Replies
5
Views
1,137
The_Doc_Man
The_Doc_Man
L
"Data type mismatch in criteria expression" Error When Trying To Pass A Variable
Replies
9
Views
633
lhooker
L
R
data type mismatch at query, that should supply data to form
2
Replies
23
Views
742
ReginaF
R
B
Update main form text boxes as subform is filled in? Also, subforms not linking?
Replies
4
Views
460
gemma-the-husky
gemma-the-husky
L
Solved Saving a report to a specified folder
Replies
3
Views
280
Gasman
Gasman

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Top Bottom