Run-time error '3464': Data type mismatch in criteria expression (1 Viewer)

H

Herwin

Guest
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

AWF VIP
Local time
Today, 06:04
Joined
May 3, 2004
Messages
1,409
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
 
H

Herwin

Guest
This didn't work, either.
 

WayneRyan

AWF VIP
Local time
Today, 13:04
Joined
Nov 19, 2002
Messages
7,122
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

New member
Local time
Today, 09:04
Joined
Apr 22, 2008
Messages
3
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

AWF VIP
Local time
Today, 06:04
Joined
May 3, 2004
Messages
1,409
Try changing:
Code:
rs.FindFirst "[seqno] = " & Str(Me![Combo89])
...to
Code:
rs.FindFirst "[seqno] = '" & Str(Me![Combo89]) & "'"
 

maverickfx

New member
Local time
Today, 09:04
Joined
Apr 22, 2008
Messages
3
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

New member
Local time
Today, 09:04
Joined
Apr 22, 2008
Messages
3
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

New member
Local time
Tomorrow, 00:04
Joined
Aug 3, 2009
Messages
1
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.:mad:
 

DCrake

Remembered
Local time
Today, 13:04
Joined
Jun 8, 2005
Messages
8,632
Remember the golden rule

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

David
 

mquinn

New member
Local time
Today, 09:04
Joined
Aug 13, 2009
Messages
1
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

New member
Local time
Today, 09:04
Joined
Dec 2, 2009
Messages
7
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

Registered User.
Local time
Today, 14:04
Joined
Jan 21, 2009
Messages
1,623
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

New member
Local time
Today, 09:04
Joined
Dec 2, 2009
Messages
7
this does not work getting error 3144 when i mke the change
 

Dragonsfly

New member
Local time
Today, 13:04
Joined
Feb 4, 2010
Messages
1
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
 
Last edited:

tucker61

Registered User.
Local time
Today, 06:04
Joined
Jan 13, 2008
Messages
321
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
 

jbeever

New member
Local time
Today, 06:04
Joined
Jul 9, 2010
Messages
1
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

New member
Local time
Today, 13:04
Joined
May 12, 2013
Messages
1
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.:mad:

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 :)
 

Users who are viewing this thread

Top Bottom