Access suddenly has Data Type Mismatch in Criteria Expression

[mel01]

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

[Hangfire]

Is it debugging at the openform stage?

[mel01]

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

[mel01]

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

[Hangfire]

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?

[mel01]

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

[Hangfire]

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.

[mel01]

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

[Hangfire]

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

[Harvinj]

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)

[Hangfire]

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