Data Type Mismatch in Criteria Expression

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to print only the current record on a One to Many form where
tblClasses is the Parent table. The Primary Key is ClassNo which is a TEXT
field.

Every time I click the button to print preview the current record, I receive
the following error message:

Data Type Mismatch in Criteria Expression
---------------------------------------------------------------------------------------
Here's the code behind the command button.
Note: I've tried using a period and ! before ClassNo and it had no effect.

Private Sub cmdTestRpt_Click()
On Error GoTo Err_cmdTestRpt_Click

Dim strDocName As String
Dim strWhere As String
strDocName = "rptClassesTEST"
strWhere = "[ClassNo]=" & Me.ClassNo
DoCmd.OpenReport strDocName, acViewPreview, , strWhere

Exit_cmdTestRpt_Click:
Exit Sub

Err_cmdTestRpt_Click:
MsgBox Err.Description
Resume Exit_cmdTestRpt_Click

End Sub
 
If ClassNo is a text field, your criteria needs to be changed from:
strWhere = "[ClassNo]=" & Me.ClassNo
To
strWhere = "[ClassNo]= '" & Me.ClassNo & "'"
 
Hello Klatuu:

That change worked! Would you be kind enough to explain the differences
between what I wrote and you did? For example, you placed a single quote mark
just prior to the double quote after the first equal sign. Why?

And then you put a "'" after &Me.ClassNo. Once again, could you please
explain why?

Thanks,
Robert
 
All criteria that goes to the database goes through Jet. Jet determines the
data type it is being sent to compare by how it is delimited. There are 3
types of delimiters.
Numeric Values - No delimiters
Text Values - Enclosed in single or double quotes
Date Values - Enclosed in Pound Signs

Assume Me.ClassNo = ABCD
What you were sendint to Jet
strWhere = "[ClassNo]=" & Me.ClassNo
translates to
[ClassNo] =ABCD

What I wrote
strWhere = "[ClassNo]= '" & Me.ClassNo & "'"
translates to
[ClassNo] = 'ABCD'
 
Hi Klatuu:

Thanks for the explanation, but how do you know when to use single or double
quotes? I noticed you used both.

How do you know where to put the quotes, such as before or after the equal
sign?

Robert
 
The quotes need to go around the value being passed.
Double qoutes are only required if it is possilbe there may be a single
quote in the data. For example, if you had a person in the database named
O'Reilly it would create a problem. The reason some of us try to get away
using the single quotes where we can is because to use double qoutes you have
to preceed each double qoute with a double quote for the double quote to
become part of the string. Confused by that last sentence? Well, thats why
I use single quotes :)
 
Hi Klatuu:

Thanks for the explanatio. I understand the need for quotes, but where they
go seems puzzling and counter inuitive to me. For example,

strWhere = "[ClassNo]= '" & Me.ClassNo & "'"

I would instinctively write the above as follows:

strWherr = "[ClassNo]" = & "Me.ClassNo" &

Why is there a '"" after the 2nd equal sign?

Why is there a "'" after Me.ClassNo?

Thanks,
Robert
 
Robert T said:
Hi Klatuu:

Thanks for the explanatio. I understand the need for quotes, but where they
go seems puzzling and counter inuitive to me. For example,
Click to expand...

We will use ABC123 as the value of the form's ClassNo text box for examples
below
strWhere = "[ClassNo]= '" & Me.ClassNo & "'"
Click to expand...

The result here would be:
[ClassNo] = 'ABC123'
I would instinctively write the above as follows:

strWherr = "[ClassNo]" = & "Me.ClassNo" &
Click to expand...

The result here would be an error because of the & at the end, however,
removing the trailing &, You would be evaluating the field [ClassNo] to see
if it is equal to the string "Me.ClassNo", not the value of the text box.
This would return a value of False.
Why is there a '"" after the 2nd equal sign?
Click to expand...

It is not '"", it is '" and is the opening qoute to enclose the value in
Me.ClassNo in single quotes
Why is there a "'" after Me.ClassNo?
Click to expand...
It is the closing quote to enclose the value in Me.ClassNo in single quotes
 
Hi Klatuu:

[it is '" and is the opening qoute to enclose the value in
Me.ClassNo in single quotes]

I'm still confused. If '" is the opening quote to enclose Me.ClassNo then
where is the closing quote for strWhere ="[ClassNo]= ?

Thanks,
Robert
 
I looked back at the previous posts, but I don't find strWhere ="[ClassNo]=
anywhere in your previous posts.
Let's see if I can explain it.
quotes delimit explicit string values. The results of this line:
strWhere = "[ClassNo] = "
Would be
[ClassNo]
Adding the single quote
strWhere = "[ClassNo] = '"
would make it
[ClassNo] = '
You could also write it
strWhere = "[ClassNo] = ""
which would result in
[ClassNo] = "

If you add
strWhere = "[ClassNo] = '" & "Me.ClassNo'"
you would get
[ClassNo ] = 'Me.ClassNo'

Because Me.ClassNo is enclosed in quotes. Remeber, enclosing anything in
quotes makes it a literal value. If you want to include the value contained
in Me.ClassNo in your string, it must be outside the quotes, but the string
passed as criteria must have that value enclosed in quotes because the table
field is text.

strWhere = "[ClassNo] = '" & Me.ClassNo & "'"
returns
[ClassNo] = 'ABC123'

You can also write it this way to use double quotes in the string
strWhere = "[ClassNo] = """ & Me.ClassNo & """
and get
[ClassNo] = "ABC123"

Play around with it in the immediate window.
 
Hi Klatuu:

First, I will try to clarify something I wrote in my previous post.

Second, although I still don't get it, I truly appreciate you taking the
time to send me an in depth explanation. I'm going to work with what you sent
to see if I can figure it out.

Clarification:
--------------
You wrote:

[I looked back at the previous posts, but I don't find strWhere ="[ClassNo]=
anywhere in your previous posts.]

It wasn't there. I was responding to your statement that the '" after the
2nd equal sign below is actually the open quote for Me.ClassNo. If that's the
opening quote for Me.ClassNo, I was asking you where the closing quote for
"[ClassNo]= is located. I thought the '" after the 2nd equal sign was
the closing quote for [ClassNo] but you said it was actually the opening
quote for what follows. Does my question make sense?

strWhere = "[ClassNo] = '" & "Me.ClassNo'"

Thanks,
Robert
 
okay, I guess I get too literal sometimes.

This is incorret.
strWhere = "[ClassNo] = '" & "Me.ClassNo'"

It will return
[ClassNo] = 'Me.ClassNo'

Should be
strWhere = "[ClassNo] = '" & Me.ClassNo & "'"
^ ^
l l
Opening Quote Closing Quote
 
Brilliantly simple! :) Thanks. I'd been pulling my hair out over that.

Klatuu said:
If ClassNo is a text field, your criteria needs to be changed from:
strWhere = "[ClassNo]=" & Me.ClassNo
To
strWhere = "[ClassNo]= '" & Me.ClassNo & "'"

Robert T said:
I'm trying to print only the current record on a One to Many form where
tblClasses is the Parent table. The Primary Key is ClassNo which is a TEXT
field.

Every time I click the button to print preview the current record, I receive
the following error message:

Data Type Mismatch in Criteria Expression
---------------------------------------------------------------------------------------
Here's the code behind the command button.
Note: I've tried using a period and ! before ClassNo and it had no effect.

Private Sub cmdTestRpt_Click()
On Error GoTo Err_cmdTestRpt_Click

Dim strDocName As String
Dim strWhere As String
strDocName = "rptClassesTEST"
strWhere = "[ClassNo]=" & Me.ClassNo
DoCmd.OpenReport strDocName, acViewPreview, , strWhere

Exit_cmdTestRpt_Click:
Exit Sub

Err_cmdTestRpt_Click:
MsgBox Err.Description
Resume Exit_cmdTestRpt_Click

End Sub
Click to expand...
Click to expand...
 
Back
Top