data type mismatch in report criteria expression

[Guest]

I am using Access 2003.

The database records have DOB and Age as given by the client. I am looking
for errors in the data and want to list all records where the DOB and Age
don’t agree. I have written a VBA function to calculate the age. I want to
filter for the records where the difference between the calculated age and
given age is not 0. The filter
results in a ‘Data type mismatch in criteria expression’.

Notes:
tblClientList.RecNum is Number
tblClientList.ApptDate is Date/Time
tblClientList.DOB is Date/Time
tblClientList.Age is Number

Function funcGetAge() is defined as follows in a VBA Module:
Public Function funcGetAge(dtmBD As Date, Optional dtmDate As Date = 0) As
Integer
Dim intAge As Integer
If dtmDate = 0 Then
' Did the caller pass in a date? If not, use
' the current date.
dtmDate = Date
End If
intAge = DateDiff("yyyy", dtmBD, dtmDate)
If dtmDate < DateSerial(Year(dtmDate), Month(dtmBD), Day(dtmBD)) Then
intAge = intAge - 1
End If
funcGetAge = intAge
End Function

The following SQL results in a “Data type mismatch in criteria expressionâ€
error when previewed.
SELECT tblClientList.RecNum,
tblClientList.ApptDate,
tblClientList.DOB,
tblClientList.Age,
funcGetAge(tblClientList!DOB,tblClientList!ApptDate) AS CalcAge,

funcGetAge(tblClientList!DOB,tblClientList!ApptDate)-tblClientList!Age AS
CalcDiff
FROM tblClientList
WHERE ((
(tblClientList.DOB) Is Not Null)
AND ((tblClientList.Age) Is Not Null)
AND
((funcGetAge(tblClientList!DOB,tblClientList!ApptDate)-tblClientList!Age)<>0
));

I haven’t been able to figure out where the mismatch is occurring.

 

[Allen Browne]

Cam, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

The suggestion is to typecast any calculated field, so:
CInt(funcGetAge(tblClientList!DOB,tblClientList!ApptDate))
- tblClientList!Age AS CalcDiff

Of course, the Age field probably should not exist in the table anyway.

 

[Guest]

I tried the suggestion :

SELECT tblClientList.RecNum,
tblClientList.ApptDate,
tblClientList.DOB, tblClientList.Age,
CInt(funcGetAge(tblClientList!DOB,tblClientList!ApptDate)) AS
CalcAge,

CInt(funcGetAge(tblClientList!DOB,tblClientList!ApptDate)-tblClientList!Age)
AS CalcDiff
FROM tblClientList
WHERE (((tblClientList.DOB) Is Not Null) AND ((tblClientList.Age) Is Not
Null) AND
((CInt(funcGetAge(tblClientList!DOB,tblClientList!ApptDate)-tblClientList!Age))<>0));

But I got the same data type mismatch error.

I realize the data collected is redundant but, as it is collected, I'm
looking for records where there is a data entry error.

 

[Allen Browne]

Two issues:

1. The CInt() needs to go around the first expression (the function call
part). Otherwise, if JET does not understand that's a number, it will not be
able to subtract the Age field.

2. Your function expects variables of type Date. Although the WHERE clause
excludes Nulls, the function call in the WHERE clause means that the
function is called to decide which records to include, so it could be
receiving a Null value, which will generate an error. To solve this issue,
the function must accept a Variant, and then test if it IsDate(). Try the
function in this link instead:
http://allenbrowne.com/func-08.html

Try:
WHERE CInt(Age(tblClientList!DOB, tblClientList!ApptDate))
<> tblClientList!Age;

Testing if they are different will exclude the nulls, and avoid the
subtraction.

 

[Guest]

Thanks,

I used your Age Function but modified it to set the Age to 0 when tthe age
or DOB was null. For my query that works as I'm eliminating those values in
the final result. I can refine the function more later. I also want to
skip records where the age difference is plus or minus 1. My final SQL that
is working is:
SELECT tblClientList.RecNum, tblClientList.ApptDate, tblClientList.DOB,
tblClientList.Age, CInt(Age(tblClientList!DOB,tblClientList!ApptDate)) AS
CalcAge,
CInt(Age(tblClientList!DOB,tblClientList!ApptDate)-tblClientList!Age) AS
CalcDiff
FROM tblClientList
WHERE (((tblClientList.DOB) Is Not Null) AND ((tblClientList.Age) Is Not
Null) AND
((CInt(Age([tblClientList]![DOB],[tblClientList]![ApptDate])))<>[tblClientList]![Age]
And
(CInt(Age([tblClientList]![DOB],[tblClientList]![ApptDate])))<>[tblClientList]![Age]-1
And
(CInt(Age([tblClientList]![DOB],[tblClientList]![ApptDate])))<>[tblClientList]![Age]+1))
ORDER BY
CInt(Age(tblClientList!DOB,tblClientList!ApptDate)-tblClientList!Age) DESC;

Thanks for your help.