Mysterious "Data type mismatch in criteria expression" error

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

Guest

I'm using Access XP.

I am trying to produce a query that lists students in our database who are
first or second year students in our program. To that end I have produced a
function that returns an integer indicating what year of the program a
student is in.

The function is simple and seems to be working properly from the immediate
window:

Public Function GetStudentYear(ByVal GradYearString As String) As Integer
GetStudentYear = (GetAcademicYear() - CInt(GradYearString)) + 2
End Function

Yes, the database stores their expected year of graduation rather than when
they came in, and as a string rather than a number. I did not design the
database and I'm not allowed to change it; I'm just trying to work with it.

As I said, this function seems to be producing good data. I can include it
in the query without problem, except when I attempt to add criteria. If I
make a field:

Student Year: GetStudentYear([AGradDate])

And put a number in for a criteria, the computer throws a "Data type
mismatch in criteria expression. (Error 3464)" at me. I do not see any
reasonable cause for this. I have stepped through the routine and the error
doesn't occur during the operation of the VBA code, but after the function
exits. There is nothing unusual about the point where the error occurs, it
is the same string value "2006" that is returned as a integer 2 as many were
before.

Can anyone give me any idea where to look for a problem?
 
Michael Suttkus said:
I'm using Access XP.

I am trying to produce a query that lists students in our database
who are first or second year students in our program. To that end I
have produced a function that returns an integer indicating what year
of the program a student is in.

The function is simple and seems to be working properly from the
immediate window:

Public Function GetStudentYear(ByVal GradYearString As String) As
Integer GetStudentYear = (GetAcademicYear() -
CInt(GradYearString)) + 2
End Function

Yes, the database stores their expected year of graduation rather
than when they came in, and as a string rather than a number. I did
not design the database and I'm not allowed to change it; I'm just
trying to work with it.

As I said, this function seems to be producing good data. I can
include it in the query without problem, except when I attempt to add
criteria. If I make a field:

Student Year: GetStudentYear([AGradDate])

And put a number in for a criteria, the computer throws a "Data type
mismatch in criteria expression. (Error 3464)" at me. I do not see
any reasonable cause for this. I have stepped through the routine
and the error doesn't occur during the operation of the VBA code, but
after the function exits. There is nothing unusual about the point
where the error occurs, it is the same string value "2006" that is
returned as a integer 2 as many were before.

Can anyone give me any idea where to look for a problem?
Click to expand...

Is [AGradDate] a query parameter, or is there a query parameter that you
don't show, which you use to "put a number in for a criteria"? If
you're putting in a number where it needs to be interpreted as a string,
you may need to explicitly specify the parameter type. You can do that
in the query designer by clicking the Query -> Parameters... menu
option, and filling in the Query Parameters dialog.
 
I did not follow all of your explaination but one thing comes to mind is that
the results might be a string instead of value. Try adding Val( xxxxx ) to
it. Can't hurt.
 
Dirk Goldgar said:
Is [AGradDate] a query parameter, or is there a query parameter that you
don't show, which you use to "put a number in for a criteria"?
Click to expand...

No, it's a field in the table, not a parameter.
 
The function works well through several iterations, so it's not that the
function is generally producing an improper type.

KARL DEWEY said:
I did not follow all of your explaination but one thing comes to mind is that
the results might be a string instead of value. Try adding Val( xxxxx ) to
it. Can't hurt.

Michael Suttkus said:
I'm using Access XP.

I am trying to produce a query that lists students in our database who are
first or second year students in our program. To that end I have produced a
function that returns an integer indicating what year of the program a
student is in.

The function is simple and seems to be working properly from the immediate
window:

Public Function GetStudentYear(ByVal GradYearString As String) As Integer
GetStudentYear = (GetAcademicYear() - CInt(GradYearString)) + 2
End Function

Yes, the database stores their expected year of graduation rather than when
they came in, and as a string rather than a number. I did not design the
database and I'm not allowed to change it; I'm just trying to work with it.

As I said, this function seems to be producing good data. I can include it
in the query without problem, except when I attempt to add criteria. If I
make a field:

Student Year: GetStudentYear([AGradDate])

And put a number in for a criteria, the computer throws a "Data type
mismatch in criteria expression. (Error 3464)" at me. I do not see any
reasonable cause for this. I have stepped through the routine and the error
doesn't occur during the operation of the VBA code, but after the function
exits. There is nothing unusual about the point where the error occurs, it
is the same string value "2006" that is returned as a integer 2 as many were
before.

Can anyone give me any idea where to look for a problem?
Click to expand...
Click to expand...
 
Michael Suttkus said:
Dirk Goldgar said:
Is [AGradDate] a query parameter, or is there a query parameter that
you don't show, which you use to "put a number in for a criteria"?
Click to expand...

No, it's a field in the table, not a parameter.
Click to expand...

Then where do you "put a number in for a criteria"? Please post the SQL
of the whole query.
 
First thing I would do is to check the data.
1) Is AGradDate ever NULL? If so, you will throw an error.
2) Is AGradDate ever larger than 32K (as in a data entry error)? The CInt
will cause an error.
 
Dirk Goldgar said:
Then where do you "put a number in for a criteria"? Please post the SQL
of the whole query.
Click to expand...

I put the cursor in the criteria blank under the expression field and type
"1 or 2".

But, actually, I've just found out where the problem was coming from and
repaired it. It was a bit of unexpected data in the underlying table that
had been added since the last time I vetted it (neat timing, given we're
talking minutes!).

Of course, if I ran things, the table wouldn't let you enter bad data, but I
don't run things. :-)
 
Michael Suttkus said:
I put the cursor in the criteria blank under the expression field and
type "1 or 2".

But, actually, I've just found out where the problem was coming from
and repaired it. It was a bit of unexpected data in the underlying
table that had been added since the last time I vetted it (neat
timing, given we're talking minutes!).
Ah.

Of course, if I ran things, the table wouldn't let you enter bad
data, but I don't run things. :-)
Click to expand...

Pity, that. You may have revise your expression or your function to
allow for the possibility of their being bad or missing data in the
field.
 
Back
Top