Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rating: Thread Rating: 27 votes, 4.96 average. Display Modes
Old 03-02-2005, 02:03 AM   #1
Herwin
Guest
 
Posts: n/a
Unhappy Run-time error '3464': Data type mismatch in criteria expression

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.

  Reply With Quote
Old 03-02-2005, 03:58 PM   #2
ByteMyzer
AWF VIP
 
Join Date: May 2004
Location: United States
Posts: 1,377
Thanks: 0
Thanked 44 Times in 39 Posts
ByteMyzer is a jewel in the rough ByteMyzer is a jewel in the rough ByteMyzer is a jewel in the rough
Try:
Code:
Private Sub Delete_ConsignmentH()
Set Dbase = Application.CurrentDb

sSQL = "DELETE * FROM consignmenth "
sSQL = sSQL & "WHERE (((DateSerial(CInt(Left([CONSIGNMENTH]![DESPATCH DATE],4)), "
sSQL = sSQL & "CInt(Mid([CONSIGNMENTH]![DESPATCH DATE],5,2)),"
sSQL = sSQL & "CInt(Mid([CONSIGNMENTH]![DESPATCH DATE],7,2)))) < Date()- " & DaysOld & "))"
Dbase.Execute sSQL
sAddevent "delete consignmentH klaar"
End Sub
ByteMyzer is offline   Reply With Quote
Old 03-07-2005, 12:30 AM   #3
Herwin
Guest
 
Posts: n/a
This didn't work, either.

  Reply With Quote
Old 03-07-2005, 10:44 AM   #4
WayneRyan
AWF VIP
 
Join Date: Nov 2002
Location: Camarillo, CA
Posts: 7,076
Thanks: 6
Thanked 55 Times in 53 Posts
WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough
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
WayneRyan is offline   Reply With Quote
Old 04-22-2008, 08:16 AM   #5
maverickfx
Registered User
 
Join Date: Apr 2008
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
maverickfx is on a distinguished road
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
maverickfx is offline   Reply With Quote
Old 04-22-2008, 08:22 AM   #6
ByteMyzer
AWF VIP
 
Join Date: May 2004
Location: United States
Posts: 1,377
Thanks: 0
Thanked 44 Times in 39 Posts
ByteMyzer is a jewel in the rough ByteMyzer is a jewel in the rough ByteMyzer is a jewel in the rough
Try changing:
Code:
rs.FindFirst "[seqno] = " & Str(Me![Combo89])
...to
Code:
rs.FindFirst "[seqno] = '" & Str(Me![Combo89]) & "'"
ByteMyzer is offline   Reply With Quote
Old 04-22-2008, 08:36 AM   #7
maverickfx
Registered User
 
Join Date: Apr 2008
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
maverickfx is on a distinguished road
Post

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 is offline   Reply With Quote
Old 04-22-2008, 09:10 AM   #8
maverickfx
Registered User
 
Join Date: Apr 2008
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
maverickfx is on a distinguished road
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
maverickfx is offline   Reply With Quote
Old 08-03-2009, 03:43 AM   #9
sean_92
Registered User
 
Join Date: Aug 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
sean_92 is on a distinguished road
Exclamation Re: Run-time error '3464': Data type mismatch in criteria expression

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.
sean_92 is offline   Reply With Quote
Old 08-03-2009, 04:10 AM   #10
DCrake
Administrator
 
DCrake's Avatar
 
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanks: 8
Thanked 312 Times in 208 Posts
DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light
Send a message via Skype™ to DCrake
Re: Run-time error '3464': Data type mismatch in criteria expression

Remember the golden rule

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

David
__________________
David Crake


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
The Home of Simple Software Solutions.

O/S Windows XP (SP3) & Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)

Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
DCrake is offline   Reply With Quote
The Following User Says Thank You to DCrake For This Useful Post:
The John Rambo (06-30-2011)
Old 08-13-2009, 12:20 PM   #11
mquinn
Registered User
 
Join Date: Aug 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
mquinn is on a distinguished road
Re: Run-time error '3464': Data type mismatch in criteria expression

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
mquinn is offline   Reply With Quote
Old 12-02-2009, 10:15 AM   #12
ronmola
Registered User
 
Join Date: Dec 2009
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
ronmola is on a distinguished road
Re: Run-time error '3464': Data type mismatch in criteria expression

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
ronmola is offline   Reply With Quote
Old 12-02-2009, 12:48 PM   #13
JANR
Newly Registered User
 
Join Date: Jan 2009
Location: Norway
Posts: 1,623
Thanks: 7
Thanked 165 Times in 158 Posts
JANR has a spectacular aura about JANR has a spectacular aura about
Re: Run-time error '3464': Data type mismatch in criteria expression

Quote:
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:
valSQL = valSQL & "where CarNumber = '" & Me.CarNumber & '"
JR
JANR is offline   Reply With Quote
Old 12-03-2009, 07:25 AM   #14
ronmola
Registered User
 
Join Date: Dec 2009
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
ronmola is on a distinguished road
Re: Run-time error '3464': Data type mismatch in criteria expression

this does not work getting error 3144 when i mke the change
ronmola is offline   Reply With Quote
Old 02-04-2010, 05:53 AM   #15
Dragonsfly
Registered User
 
Join Date: Feb 2010
Location: Bolton, UK
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Dragonsfly is on a distinguished road
Exclamation Re: Run-time error '3464': Data type mismatch in criteria expression

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 by Dragonsfly; 02-04-2010 at 07:29 AM.
Dragonsfly is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 04:56 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World