1,105,375 Community Members

Access suddenly has Data Type Mismatch in Criteria Expression

Member Avatar
mel01
Newbie Poster
16 posts since Jun 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi,

Have been using the same database for years without any trouble. Suddenly we are getting the Data Type Mismatch in Criteria Expression when we attempt to run a VB function.

I am not familiar with VB so much, but have found where the error is happening:

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click
 Dim i As Integer
 Dim a As String
 i = 0
 Do Until i > 2
  a = InputBox("Please Enter your ClientId" & vbCrLf & vbCrLf & _
    "  ( which is your surname followed by" & vbCrLf & _
    "   your security number, eg: Smith123 )", _
    "ClientId?", a, 2000, 2000)
  If a = "" Then Exit Sub
  If (DCount("ClientID", "Client Info records", _
           "ucase$(ClientId)='" & Trim$(UCase$(a)) & "'") > 0) Then i = 16
  i = i + 1
 Loop
 If i < 16 Then
  MsgBox "ClientId Rejected!" & vbCrLf & vbCrLf & _
   "If you have forgotten your ClientId," & vbCrLf & _
   "please see front-desk staff who can Help!" & vbCrLf & vbCrLf & _
   "(Please avoid making a second new ClientId," & vbCrLf & _
   "  as this upsets our record-keeping!)", vbCritical, "ClientId?"
  Exit Sub
 End If
  
    Dim stDocName As String
    Dim stLinkCriteria As String
 a = UCase$(a)
    stDocName = "ClientIDprompt"
    DoCmd.OpenForm stDocName, , , "ucase$(ClientId)='" & a & "'" ', , , stLinkCriteria

Exit_Command1_Click:
    Exit Sub

Err_Command1_Click:
    MsgBox Err.Description
    Resume Exit_Command1_Click
    
End Sub

As I have stated this code worked fine for years and now suddenly we are getting this error.

Thanks in advance for any assistance.

mel01

Member Avatar
Hangfire
Junior Poster in Training
63 posts since Mar 2009
Reputation Points: 4 [?]
Q&As Helped to Solve: 12 [?]
Skill Endorsements: 0 [?]
 
0
 

Is it debugging at the openform stage?

Member Avatar
mel01
Newbie Poster
16 posts since Jun 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Is it debugging at the openform stage?

Hi Hangfire,

To be honest, I really don't know.

I do know that the culprit is this statement:

If (DCount("ClientID", "Client Info records", _
           "ucase$(ClientId)='" & Trim$(UCase$(a)) & "'") > 0) Then i = 16  If (DCount("ClientID", "Client Info records", _
           "ucase$(ClientId)='" & Trim$(UCase$(a)) & "'") > 0) Then i = 16

Clients are entering a code like: smith1234
The client codes in the file are all of this format and look correct.

Again, this db has worked for many years without problem. We upgraded to Access 2007 from 2003 in October 2009 and the database continued to function correctly until just last week.

Thanks again for your time and assistance.

Puzzled,
mel01

Member Avatar
mel01
Newbie Poster
16 posts since Jun 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Is it debugging at the openform stage?

Hi Hangfire,

To be honest, I really don't know.

I do know that the culprit is this statement:

If (DCount("ClientID", "Client Info records", _
           "ucase$(ClientId)='" & Trim$(UCase$(a)) & "'") > 0) Then i = 16

Clients are entering a code like: smith1234
The client codes in the file are all of this format and look correct.

Again, this db has worked for many years without problem. We upgraded to Access 2007 from 2003 in October 2009 and the database continued to function correctly until just last week.

Thanks again for your time and assistance.

Puzzled,
mel01

Member Avatar
Hangfire
Junior Poster in Training
63 posts since Mar 2009
Reputation Points: 4 [?]
Q&As Helped to Solve: 12 [?]
Skill Endorsements: 0 [?]
 
0
 

That's a surprise, the main culprit for data mismatch is a date that has been entered incorrectly.

Is "Client Info records" a query or a table?

Member Avatar
mel01
Newbie Poster
16 posts since Jun 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

That's a surprise, the main culprit for data mismatch is a date that has been entered incorrectly.

Is "Client Info records" a query or a table?

HI Hangfire,

Client info records is a table.

mel01

Member Avatar
Hangfire
Junior Poster in Training
63 posts since Mar 2009
Reputation Points: 4 [?]
Q&As Helped to Solve: 12 [?]
Skill Endorsements: 0 [?]
 
0
 

Open the table, check each field by sorting them A-Z then Z-A and look for any dodgy or blank entries. Start with any date type fields you have.

Question Answered as of 4 Years Ago by Hangfire
Member Avatar
mel01
Newbie Poster
16 posts since Jun 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Open the table, check each field by sorting them A-Z then Z-A and look for any dodgy or blank entries. Start with any date type fields you have.

Hangfire,

Thanks for your time and efforts. You have resolved our issue (there was a single record with a blank clientID field).

Hoping to be able to return the favour one day.

Best,
mel01

Member Avatar
Hangfire
Junior Poster in Training
63 posts since Mar 2009
Reputation Points: 4 [?]
Q&As Helped to Solve: 12 [?]
Skill Endorsements: 0 [?]
 
0
 

Cool, you've no idea how many times i've had to do that.

Harvinj
Newbie Poster
1 post since Apr 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Unverified Member
 
0
 

Have been using this inherited database for about 1 year without any trouble. Suddenly we are getting the Data Type Mismatch in Criteria Expression when we attempt to open our database. And we have to click OK two times, before we can get any work completed. ANY Ideas.. (Someone developed it about 6 years ago, and we have been updating our data yearly)

Member Avatar
Hangfire
Junior Poster in Training
63 posts since Mar 2009
Reputation Points: 4 [?]
Q&As Helped to Solve: 12 [?]
Skill Endorsements: 0 [?]
 
0
 

Only to re-iterate what I said 3 years ago...wow time flys!

Open the table, check each field by sorting them A-Z then Z-A and look for any dodgy or blank entries. Start with any date type fields you have.

9 times out of 10, its a dodgy date

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: