Data Type mismatch error on a Time Calculation

[charles w via AccessMonster.com]

Hello,
I am trying to make a table that takes a client's Date of birth and the
date/time of arrival and calculate an age. I have successfully used a
function that calculates this data (from seperate tables) and posts the
client age in a new table. I have used this function MANY times without
incident. Now, I receive the data type mismatch error every time I try this
well used function. The tables, module, or fields have not been altered.

In an effort to solve this problem, I have downloaded the latest MDAC files
with no success. I'm stumped. Any help would be welcome!

Here is the VBA for the module....

Function AGE(VarBirthdate As Date, VarAdate As Date) As Integer
Dim VarAge As Variant
If IsNull(VarBirthdate) Then AGE = 0: Exit Function
If Not IsDate(VarBirthdate) Then AGE = 0: Exit Function
If Not IsDate(VarAdate) Then AGE = 0: Exit Function
VarAge = DateDiff("yyyy", VarBirthdate, VarAdate)
If DateSerial(Year(VarAdate), Month(VarAdate), Day(VarAdate)) < DateSerial
(Year(VarAdate), Month(VarBirthdate), Day(VarBirthdate)) Then
VarAge = VarAge - 1
End If
AGE = Val(VarAge)
End Function


Here is the SQL for the query:

SELECT EMSTAT_ARCHIVE_ACUITY.DESCRIP, EMSTAT_ARCHIVE_CHART.CHRTNO, AGE(
[EMSTAT_ARCHIVE_PATIENT]![DOB],[EMSTAT_ARCHIVE_CHART]![ARRVDATE]) AS AGEPT,
EMSTAT_ARCHIVE_CHART.ARRVDATE, EMSTAT_ARCHIVE_CHART.DSCHDATE INTO [tblAcuity-
Pedi]
FROM EMSTAT_ARCHIVE_PATIENT INNER JOIN (EMSTAT_ARCHIVE_CHART INNER JOIN
EMSTAT_ARCHIVE_ACUITY ON EMSTAT_ARCHIVE_CHART.ACUITY = EMSTAT_ARCHIVE_ACUITY.
CODE) ON EMSTAT_ARCHIVE_PATIENT.PTNTID = EMSTAT_ARCHIVE_CHART.PTNTID
WHERE (((EMSTAT_ARCHIVE_CHART.ARRVDATE) Between #1/1/2003# And #7/1/2003#))
GROUP BY EMSTAT_ARCHIVE_ACUITY.DESCRIP, EMSTAT_ARCHIVE_CHART.CHRTNO, AGE(
[EMSTAT_ARCHIVE_PATIENT]![DOB],[EMSTAT_ARCHIVE_CHART]![ARRVDATE]),
EMSTAT_ARCHIVE_CHART.ARRVDATE, EMSTAT_ARCHIVE_CHART.DSCHDATE
HAVING (((AGE([EMSTAT_ARCHIVE_PATIENT]![DOB],[EMSTAT_ARCHIVE_CHART]![ARRVDATE]
))<22));

PS: DOB is in a Short date format, ARRVDATE is in "mm/dd/yyyy hh:mm"

Thanks agian!

 

[Jeff Boyce]

Charles

By all means, first make a backup copy!

It sounds like one of your References may have gone MISSING. Open a code
module, click Tools | References and see if any are prefixed with MISSING.
Note which one(s), uncheck it/them, save and close, then reopen again and
re-check the box(es). Save, then uses Debug | Compile... and see if it
works.

Regards

Jeff Boyce
<Access MVP>