Access suddenly has Data Type Mismatch in Criteria Expression

Question

mel01 0 Newbie Poster

16 Years Ago

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

.net:vb.net microsoft:access

3 Contributors
10 Replies
3K Views
3 Years Discussion Span
Latest Post 13 Years Ago Latest Post by Hangfire

Dani AI

Generated 11 Months Ago

Good catch by and confirmation from : a single blank ClientID can indeed trigger this. In Access, the error message "Data type mismatch in criteria expression" often bubbles up when your WHERE-like criteria applies a text function to a Null, or when you compare a Null to a quoted string. One subtle gotcha here is using the VBA string function variants with a trailing $ (like UCase$). Those expect a non-Null string and can fail if any row has Null or zero-length text. Also, in most Access databases, text comparisons are case-insensitive by default, so you usually do not need to force UCase at all.

A safer pattern is to guard against Null and zero-length values, bracket the table name (spaces), and escape any apostrophes the user might type. For example:

Dim where As String
where = "ClientID Is Not Null AND ClientID <> '' AND ClientID='" & _
        Replace(Trim$(a), "'", "''") & "'"

If DCount("*", "[Client Info records]", where) > 0 Then
    DoCmd.OpenForm "ClientIDprompt", , , where
Else
    MsgBox "ClientId Rejected!"
End If

If you do want case-folding, use UCase() (not UCase$) and wrap the field with Nz, e.g. UCase(Nz([ClientID], ''))=UCase('SMITH123').

To prevent a recurrence (for too): in table design, set ClientID Required = Yes, Allow Zero Length = No, give it a unique index, and add a validation rule like Is Not Null And <> ''. On the data-entry form, validate in BeforeUpdate and block blanks early. Finally, prefer a saved parameter query or a parameterized DAO QueryDef over string-built criteria when possible; it is more robust and easier to test.

All 10 Replies

Hangfire 4 Junior Poster in Training

16 Years Ago

Is it debugging at the openform stage?

Hangfire 4 Junior Poster in Training

16 Years Ago

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?

Hangfire 4 Junior Poster in Training

16 Years Ago

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.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

mel01 0 Newbie Poster · Answer 1 · 2010-02-04T23:40:38+00:00

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 0 Newbie Poster · Answer 2 · 2010-02-04T23:41:17+00:00

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

mel01 0 Newbie Poster · Answer 3 · 2010-02-05T00:45:10+00:00

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

mel01 0 Newbie Poster · Answer 4 · 2010-02-05T01:22:04+00:00

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 4 Junior Poster in Training · Answer 5 · 2010-02-05T03:33:26+00:00

Hangfire 4 Junior Poster in Training

16 Years Ago

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

Harvinj 0 Newbie Poster · Answer 6 · 2013-04-03T12:25:29+00:00

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 4 Junior Poster in Training · Answer 7 · 2013-04-03T13:39:09+00:00

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

Access suddenly has Data Type Mismatch in Criteria Expression

Dani AI

Recommended Answers Collapse Answers

All 10 Replies

Recommended Answers