Run-time error '3464': Data type mismatch in criteria expression

Herwin · Mar 2, 2005

Hallo,
I'm getting the "data type mismatch in criteria expression" error on following piece of silly code. Anybody knows why?

Private Sub Delete_ConsignmentH()
Set Dbase = Application.CurrentDb

sSQL = "DELETE * FROM consignmenth "
sSQL = sSQL & "WHERE (((DateSerial(Left([CONSIGNMENTH]![DESPATCH DATE],4), "
sSQL = sSQL & "Mid([CONSIGNMENTH]![DESPATCH DATE],5,2),"
sSQL = sSQL & "Mid([CONSIGNMENTH]![DESPATCH DATE],7,2))) < Date()- " & DaysOld & "))"
Dbase.Execute sSQL
sAddevent "delete consignmentH klaar"
End Sub

Thanks in advance for your help.

ByteMyzer · Mar 2, 2005

Try:

Code:

Private Sub Delete_ConsignmentH()
Set Dbase = Application.CurrentDb

sSQL = "DELETE * FROM consignmenth "
sSQL = sSQL & "WHERE (((DateSerial([b]CInt([/b]Left([CONSIGNMENTH]![DESPATCH DATE],4)[b])[/b], "
sSQL = sSQL & "[b]CInt([/b]Mid([CONSIGNMENTH]![DESPATCH DATE],5,2)[b])[/b],"
sSQL = sSQL & "[b]CInt([/b]Mid([CONSIGNMENTH]![DESPATCH DATE],7,2)[b])[/b])) < Date()- " & DaysOld & "))"
Dbase.Execute sSQL
sAddevent "delete consignmentH klaar"
End Sub

Herwin · Mar 7, 2005

This didn't work, either.

WayneRyan · Mar 7, 2005

Herwin,

You don't say what [DESPATCH DATE] is. That would really help.

Code:

DoCmd.RunSQL "DELETE * " & _
             "FROM consignmenth " & _
             "WHERE CDate([DESPATCH DATE]) < DateDiff("d", Date(), DaysOld)
sAddevent "delete consignmentH klaar"
End Sub

Wayne

maverickfx · Apr 22, 2008

Issue with Type MisMatch

Hi,

I am trying to run the below query to allow for an alphanumeric string, but I keep getting run-time errors or type mismatches. Either 13 is I type in an alphanumeric character, or I get 3464 if I type in only a numeric string.

Any thoughts with what I am doing wrong with the below?

Private Sub Combo89_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[seqno] = " & Str(Me![Combo89])
Me.Bookmark = rs.Bookmark
End Sub

ByteMyzer · Apr 22, 2008

Try changing:

Code:

rs.FindFirst "[seqno] = " & Str(Me![Combo89])

...to

Code:

rs.FindFirst "[seqno] = '" & Str(Me![Combo89]) & "'"

maverickfx · Apr 22, 2008

Thank you for the quick reply.

I updated my code, and when I try to type an alphanumeric value in my field in access I now get the error Run-Time error '13': Type Mismatch

I have been basically teaching myself for vb, so I apologize for any 'noob' questions.

maverickfx · Apr 22, 2008

Here is my code for everything.

When I enter in a numeric or alphanumeric value for Combo89 I get Run-Time Error '3077'

I now have:

Private Sub Combo89_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[seqno] = '" & CStr(Me![Combo89])
Me.Bookmark = rs.Bookmark
End Sub

Option Compare Database

Private Sub Combo76_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[seqno] = " & Str(Me![Combo76])
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Close_Master_Click()
On Error GoTo Err_Close_Master_Click

DoCmd.Close

Exit_Close_Master_Click:
Exit Sub

Err_Close_Master_Click:
MsgBox Err.Description
Resume Exit_Close_Master_Click

End Sub
Private Sub Combo85_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[clearer_description] = '" & Me![Combo85] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo87_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[client_seqno] = '" & Me![Combo87] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo89_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[seqno] = '" & CStr(Me![Combo89])
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo91_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[instrument_description] = '" & Me![Combo91] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo93_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[quantity] = " & Str(Me![Combo93])
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Backup_Click()
On Error GoTo Err_Backup_Click

Dim stDocName As String

stDocName = "Create Backup Table"
DoCmd.RunMacro stDocName

Exit_Backup_Click:
Exit Sub

Err_Backup_Click:
MsgBox Err.Description
Resume Exit_Backup_Click

End Sub
Private Sub Command104_Click()
On Error GoTo Err_Command104_Click

Dim stDocName As String

stDocName = "Search by Oberon ID"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command104_Click:
Exit Sub

Err_Command104_Click:
MsgBox Err.Description
Resume Exit_Command104_Click

End Sub
Private Sub Command105_Click()
On Error GoTo Err_Command105_Click

Dim stDocName As String

stDocName = "SEARCH FOR SEQNO BY QTY"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command105_Click:
Exit Sub

Err_Command105_Click:
MsgBox Err.Description
Resume Exit_Command105_Click

End Sub
Private Sub Command107_Click()
On Error GoTo Err_Command107_Click

Dim stDocName As String

stDocName = "Check Unsigned and Terminating"
DoCmd.RunMacro stDocName

Exit_Command107_Click:
Exit Sub

Err_Command107_Click:
MsgBox Err.Description
Resume Exit_Command107_Click

End Sub

sean_92 · Aug 3, 2009

I am having the same problem.
Here is my code that the Debug has identified to be the problem.

If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then
lngMyEmpID = Me.cboEmployee.Value

i think the problem has something to do with the fact that i had IngEmpID as AutoNumber and changed it to Text.
If possible i would like the data type to be set as text. So if some one could please help.

Sean.

DCrake · Aug 3, 2009

Remember the golden rule

Text needs wrapping in quotes
Dates need hashes either side
numerics need neither hashes nor quotes

David

mquinn · Aug 13, 2009

Change your code from

If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then
lngMyEmpID = Me.cboEmployee.Value

TO

If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]= ' " & Me.cboEmployee.Value & " ' ") Then
lngMyEmpID = Me.cboEmployee.Value

ronmola · Dec 2, 2009

Please Help. I am teaching myself on how to right this code and have come into a road block. I have taken this database over from somebody who has left my company. I receive the runtime erroe of 3464 when I try to update my table. Here is the code. The line in red is where I think the problem is because if I remove it the form updates the table. The problem with that is it updates all rows not the individual row I want to change.

Private Sub cmdSubmit_Click()
Dim valCheckChange As Boolean
Dim sql As String, valSQL As String
valCheckChange = CheckChange
valSQL = "update CARs set "
valSQL = valSQL & "StructureType = '" & Me.StructureType & "', "

valSQL = valSQL & "StructureNumber = '" & Me.StructureNumber & "', "
valSQL = valSQL & "LocationType = '" & Me.LocationType & "', "
If Len(Trim(Me.Sector)) > 0 Or Not IsNull(Me.Sector) Then
valSQL = valSQL & "Sector = " & Me.Sector & ", "
Else
valSQL = valSQL & "Sector = NULL, "
End If
If Len(Trim(Me.FrontOfAddress)) > 0 Or Not IsNull(Me.FrontOfAddress) Then
valSQL = valSQL & "FrontOfAddress = '" & Me.FrontOfAddress & "', "
Else
valSQL = valSQL & "FrontOfAddress = NULL, "
End If
If Len(Trim(Me.Onstreet)) > 0 Or Not IsNull(Me.Onstreet) Then
valSQL = valSQL & "Onstreet = '" & Me.Onstreet & "', "
Else
valSQL = valSQL & "Onstreet = NULL, "
End If
If Len(Trim(Me.CrossStreet1)) > 0 Or Not IsNull(Me.CrossStreet1) Then
valSQL = valSQL & "CrossStreet1 = '" & Me.CrossStreet1 & "', "
Else
valSQL = valSQL & "CrossStreet1 = NULL, "
End If
If Len(Trim(Me.CrossStreet2)) > 0 Or Not IsNull(Me.CrossStreet2) Then
valSQL = valSQL & "CrossStreet2 = '" & Me.CrossStreet2 & "', "
Else
valSQL = valSQL & "CrossStreet2 = NULL, "
End If
If Len(Trim(Me.FacilityType)) > 0 Or Not IsNull(Me.FacilityType) Then
valSQL = valSQL & "FacilityType = '" & Me.FacilityType & "', "
Else
valSQL = valSQL & "FacilityType = NULL, "
End If
If Len(Trim(Me.Condition)) > 0 Or Not IsNull(Me.Condition) Then
valSQL = valSQL & "Condition = '" & Me.Condition & "', "
Else
valSQL = valSQL & "Condition = NULL, "
End If
If Len(Trim(Me.Inspector)) > 0 Or Not IsNull(Me.Inspector) Then
valSQL = valSQL & "Inspector = '" & Me.Inspector & "', "
Else
valSQL = valSQL & "Inspector = NULL, "
End If
If Len(Trim(Me.InspectionDate)) > 0 Or Not IsNull(Me.InspectionDate) Then
valSQL = valSQL & "InspectionDate = #" & Me.InspectionDate & "#, "
Else
valSQL = valSQL & "InspectionDate = NULL, "
End If
If Len(Trim(Me.Protest)) > 0 Or Not IsNull(Me.Protest) Then
valSQL = valSQL & "Protest = '" & Me.Protest & "', "
Else
valSQL = valSQL & "Protest = NULL, "
End If
If Len(Trim(Me.OpeningTicket)) > 0 Or Not IsNull(Me.OpeningTicket) Then
valSQL = valSQL & "OpeningTicket = '" & Me.OpeningTicket & "', "
Else
valSQL = valSQL & "OpeningTicket = NULL, "
End If
If Len(Trim(Me.PermitApplication)) > 0 Or Not IsNull(Me.PermitApplication) Then
valSQL = valSQL & "PermitApplication = " & Me.PermitApplication & ", "
Else
valSQL = valSQL & "PermitApplication = NULL, "
End If
If Len(Trim(Me.PermitAppDate)) > 0 Or Not IsNull(Me.PermitAppDate) Then
valSQL = valSQL & "PermitAppDate = #" & Me.PermitAppDate & "#, "
Else
valSQL = valSQL & "PermitAppDate = NULL, "
End If
If Len(Trim(Me.PermitNumber)) > 0 Or Not IsNull(Me.PermitNumber) Then
valSQL = valSQL & "PermitNumber = " & Me.PermitNumber & ", "
Else
valSQL = valSQL & "PermitNumber = NULL, "
End If
If Len(Trim(Me.PermitExpiration)) > 0 Or Not IsNull(Me.PermitExpiration) Then
valSQL = valSQL & "PermitExpiration = #" & Me.PermitExpiration & "#, "
Else
valSQL = valSQL & "PermitExpiration = NULL, "
End If
If Len(Trim(Me.PermitStips)) > 0 Or Not IsNull(Me.PermitStips) Then
valSQL = valSQL & "PermitStips = '" & Me.PermitStips & "', "
Else
valSQL = valSQL & "PermitStips = NULL, "
End If
If Len(Trim(Me.Contractor)) > 0 Or Not IsNull(Me.Contractor) Then
valSQL = valSQL & "Contractor = '" & Me.Contractor & "', "
Else
valSQL = valSQL & "Contractor = NULL, "
End If
If Len(Trim(Me.ContractorDate)) > 0 Or Not IsNull(Me.ContractorDate) Then
valSQL = valSQL & "ContractorDate = #" & Me.ContractorDate & "#, "
Else
valSQL = valSQL & "ContractorDate = NULL, "
End If
If Len(Trim(Me.PavingContractor)) > 0 Or Not IsNull(Me.PavingContractor) Then
valSQL = valSQL & "PavingContractor = '" & Me.PavingContractor & "', "
Else
valSQL = valSQL & "PavingContractor = NULL, "
End If
If Len(Trim(Me.PavingContractorDate)) > 0 Or Not IsNull(Me.PavingContractorDate) Then
valSQL = valSQL & "PavingContractorDate = #" & Me.PavingContractorDate & "#, "
Else
valSQL = valSQL & "PavingContractorDate = NULL, "
End If
If Len(Trim(Me.CoverType)) > 0 Or Not IsNull(Me.CoverType) Then
valSQL = valSQL & "CoverType = '" & Me.CoverType & "', "
Else
valSQL = valSQL & "CoverType = NULL, "
End If
If Len(Trim(Me.NumOfCovers)) > 0 Or Not IsNull(Me.NumOfCovers) Then
valSQL = valSQL & "NumOfCovers = " & Me.NumOfCovers & ", "
Else
valSQL = valSQL & "NumOfCovers = NULL, "
End If
If Len(Trim(Me.VentedCover)) > 0 Or Not IsNull(Me.VentedCover) Then
valSQL = valSQL & "VentedCover = '" & Me.VentedCover & "', "
Else
valSQL = valSQL & "VentedCover = NULL, "
End If
If Len(Trim(Me.ParkingDayRestriction)) > 0 Or Not IsNull(Me.ParkingDayRestriction) Then
valSQL = valSQL & "ParkingDayRestriction = '" & Me.ParkingDayRestriction & "', "
Else
valSQL = valSQL & "ParkingDayRestriction = NULL, "
End If
If Len(Trim(Me.ParkingTimeRestriction)) > 0 Or Not IsNull(Me.ParkingTimeRestriction) Then
valSQL = valSQL & "ParkingTimeRestriction = '" & Me.ParkingTimeRestriction & "', "
Else
valSQL = valSQL & "ParkingTimeRestriction = NULL, "
End If
If Len(Trim(Me.AccountNumber)) > 0 Or Not IsNull(Me.AccountNumber) Then
valSQL = valSQL & "AccountNumber = '" & Me.AccountNumber & "', "
Else
valSQL = valSQL & "AccountNumber = NULL, "
End If
If Len(Trim(Me.ProblemDate)) > 0 Or Not IsNull(Me.ProblemDate) Then
valSQL = valSQL & "ProblemDate = #" & Me.ProblemDate & "#, "
Else
valSQL = valSQL & "ProblemDate = NULL, "
End If
If Len(Trim(Me.CompletedDate)) > 0 Or Not IsNull(Me.CompletedDate) Then
valSQL = valSQL & "CompletedDate = #" & Me.CompletedDate & "#, "
Else
valSQL = valSQL & "CompletedDate = NULL, "
End If
If Len(Trim(Me.Comments)) > 0 Or Not IsNull(Me.Comments) Then
valSQL = valSQL & "Comments = '" & Me.Comments & "', "
Else
valSQL = valSQL & "Comments = NULL, "
End If

valSQL = valSQL & "Completed = " & valCheckChange & ", "

valSQL = valSQL & "last_update = #" & Now() & "#, "
valSQL = valSQL & "last_user = '" & sGetUserName & "', "
valSQL = valSQL & "last_machine = '" & sGetComputerName & "' "

valSQL = valSQL & "where CarNumber = " & Me.CarNumber

Debug.Print valSQL
sql = valSQL
DoCmd.RunSQL sql

Call ClearData
End Sub

JANR · Dec 2, 2009

valSQL = valSQL & "where CarNumber = " & Me.CarNumber

This marked in blue indicate that this is defined as a number, but suspect this to be a text variabel instead. Try to enclose it in quotes.

Code:

[COLOR=#ff0000]valSQL = valSQL & "where CarNumber = [COLOR=blue]'"[/COLOR] & Me.CarNumber [COLOR=blue]&[/COLOR] [COLOR=blue]'[/COLOR][/COLOR][COLOR=blue]"[/COLOR]

JR

ronmola · Dec 3, 2009

this does not work getting error 3144 when i mke the change

Dragonsfly · Feb 4, 2010

I've done an advanced access course, but ofcourse that didn't explain to me how to set up a reminder. Someone gave me the following code that I have adjusted to fit my database, but it keeps coming up with a run-time error '3464' Data type mismatch in criteria expression:

Private Sub Form_Load()

'On Load of the switchboard check Courses Passed table for any expired courses dates

Dim intStore As Integer

'Count of expired course dates that are past the Expiry Date intStore = DCount("[Course ID]", "[Courses Passed]", "[Expiry Date]<=Now()AND[Date Attended]=0")

'If count of Expiry Date and Date Attended is zero display switchboard
'Else display message box detailing amount of expired course dates
'and give the user the option as to whether to view these or not.
If intStore = 0 Then
Exit Sub
Else
If MsgBox("There are" & intStore & "Expiry Date" & "Date Attended" & _
vbCrLf & vbCrLf & "Would you like to see these now?", _
vbYesNo, "You Have Expired Course Dates...") = vbYes Then
DoCmd.Minimize
DoCmd.OpenForm "frmReminders", acNormal
Else
Exit Sub
End If
End If
End Sub

The sentences in red are the ones that the system keeps telling me about.

Can someone, anyone, please help me? I'm really getting desperate... Nothing I've tried has worked so far.

Thanks,
Petra

tucker61 · Feb 9, 2010

DCrake said:
Remember the golden rule

Text needs wrapping in quotes
Dates need hashes either side
numerics need neither hashes nor quotes

David

I wish i had read this post 3 hours ago

Thanks
Tucker61

Khalid_Afridi · Feb 9, 2010

DCrake said:
Remember the golden rule

Text needs wrapping in quotes
Dates need hashes either side
numerics need neither hashes nor quotes

David

Very Golden quotes

jbeever · Jul 9, 2010

There is a record that Access does not under stand with the 3463 error. I recieved the same error when trying to sort on a form. My sort field was blank. I never rec'd this type of error in 2003 access (mdb). But I received the error in 2007 Access (mdb) i did not convert it to accdb yet because not all of my users have 2007.

tkc108 · May 12, 2013

sean_92 said:
I am having the same problem.
Here is my code that the Debug has identified to be the problem.

If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then
lngMyEmpID = Me.cboEmployee.Value

i think the problem has something to do with the fact that i had IngEmpID as AutoNumber and changed it to Text.
If possible i would like the data type to be set as text. So if some one could please help.

Sean.

Hi,

I had a very similar problem but managed to solve it. in my tables on top of the autonumber userID field i had Login (text filed). I simply changed:

Code:

"[userID]='" & Me.txtUser.Value & "'"

to

Code:

"[Login]='" & Me.txtUser.Value & "'"

and it worked beautifully

Run-time error '3464': Data type mismatch in criteria expression

Herwin

Guest

AWF VIP

Herwin

Guest

AWF VIP

New member

AWF VIP

New member

New member

New member

Remembered

New member

New member

Registered User.

New member

New member

Registered User.

Registered User.

New member

New member

Similar threads

Users who are viewing this thread