Error 3464 Data Type mismatch in criteria expression

[charleswoods via AccessMonster.com]

First of all, this function has worked for years... After returning from
vacation, I find that this function which is used to determine a person's age
at the time they visit our hospital, doesn't work anymore. I have racked my
brain trying to find an answer to why this has stopped working. The
application which feeds the tables queried will not allow different data
types to be entered into the tables.

Function Age(VarBirthdate As Date, VarAdate As Date) As Integer

Dim VarAge As Variant

If IsNull(VarBirthdate) 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 a sample query that returns this error:
SELECT EMSTAT_ARCHIVE_CHART.CHRTNO, Age([EMSTAT_ARCHIVE_PATIENT]![DOB],
[EMSTAT_ARCHIVE_CHART]![ARRVDATE]) AS AGEPT, EMSTAT_ARCHIVE_CHART.ARRVDATE
INTO tblFC2006
FROM ((EMSTAT_ARCHIVE_PATIENT INNER JOIN EMSTAT_ARCHIVE_CHART ON
EMSTAT_ARCHIVE_PATIENT.PTNTID = EMSTAT_ARCHIVE_CHART.PTNTID) INNER JOIN
(EMSTAT_ARCHIVE_OI_HEADER INNER JOIN EMSTAT_ARCHIVE_OI_DETAIL ON
EMSTAT_ARCHIVE_OI_HEADER.OI_HEADER_ID = EMSTAT_ARCHIVE_OI_DETAIL.OI_HEADER_ID)
ON EMSTAT_ARCHIVE_CHART.CHRTNO = EMSTAT_ARCHIVE_OI_HEADER.CHARTNO) INNER JOIN
EMSTAT_ARCHIVE_LOCATION ON EMSTAT_ARCHIVE_OI_DETAIL.VALUE =
EMSTAT_ARCHIVE_LOCATION.LOCATID
WHERE (((EMSTAT_ARCHIVE_OI_HEADER.OD_HEADER_ID)="ADMINCHGROOM") AND (
(EMSTAT_ARCHIVE_OI_DETAIL.OD_DETAIL_ID)="ROOM"))
GROUP BY EMSTAT_ARCHIVE_CHART.CHRTNO, Age([EMSTAT_ARCHIVE_PATIENT]![DOB],
[EMSTAT_ARCHIVE_CHART]![ARRVDATE]), EMSTAT_ARCHIVE_CHART.ARRVDATE
HAVING (((EMSTAT_ARCHIVE_CHART.ARRVDATE) Between #1/1/2006# And #1/5/2006#));

This query will work fine if I remove the function.

Any help is greatly appreciated!

 

[John Spencer]

Well the function will not accept nulls as arguments since the arguments are
declared as dates. So if any of the fields you are passing to it contain a
null value (or a non-date value) then the function will error. You can try
changing the declaration line to.

Function Age (varBirthDate as Variant, varADate as Variant) as Integer

Or you can ensure that the query always passes dates to the function.

Function Age(VarBirthdate, VarAdate) As Integer
Dim VarAge As Variant

'I would actually return Null but you've defined the return value as integer
If IsDate(varBirthdate) = False then Age=0 : Exit Function
If IsDate(varADate) = False 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 = VarAge 'This should be all you need here.
' Age = Val(VarAge)

End Function

--
John Spencer
Access MVP 2002-2005, 2007

First of all, this function has worked for years... After returning from
vacation, I find that this function which is used to determine a person's
age
at the time they visit our hospital, doesn't work anymore. I have racked
my
brain trying to find an answer to why this has stopped working. The
application which feeds the tables queried will not allow different data
types to be entered into the tables.

Function Age(VarBirthdate As Date, VarAdate As Date) As Integer

Dim VarAge As Variant

If IsNull(VarBirthdate) 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 a sample query that returns this error:
SELECT EMSTAT_ARCHIVE_CHART.CHRTNO, Age([EMSTAT_ARCHIVE_PATIENT]![DOB],
[EMSTAT_ARCHIVE_CHART]![ARRVDATE]) AS AGEPT, EMSTAT_ARCHIVE_CHART.ARRVDATE
INTO tblFC2006
FROM ((EMSTAT_ARCHIVE_PATIENT INNER JOIN EMSTAT_ARCHIVE_CHART ON
EMSTAT_ARCHIVE_PATIENT.PTNTID = EMSTAT_ARCHIVE_CHART.PTNTID) INNER JOIN
(EMSTAT_ARCHIVE_OI_HEADER INNER JOIN EMSTAT_ARCHIVE_OI_DETAIL ON
EMSTAT_ARCHIVE_OI_HEADER.OI_HEADER_ID =
EMSTAT_ARCHIVE_OI_DETAIL.OI_HEADER_ID)
ON EMSTAT_ARCHIVE_CHART.CHRTNO = EMSTAT_ARCHIVE_OI_HEADER.CHARTNO) INNER
JOIN
EMSTAT_ARCHIVE_LOCATION ON EMSTAT_ARCHIVE_OI_DETAIL.VALUE =
EMSTAT_ARCHIVE_LOCATION.LOCATID
WHERE (((EMSTAT_ARCHIVE_OI_HEADER.OD_HEADER_ID)="ADMINCHGROOM") AND (
(EMSTAT_ARCHIVE_OI_DETAIL.OD_DETAIL_ID)="ROOM"))
GROUP BY EMSTAT_ARCHIVE_CHART.CHRTNO, Age([EMSTAT_ARCHIVE_PATIENT]![DOB],
[EMSTAT_ARCHIVE_CHART]![ARRVDATE]), EMSTAT_ARCHIVE_CHART.ARRVDATE
HAVING (((EMSTAT_ARCHIVE_CHART.ARRVDATE) Between #1/1/2006# And
#1/5/2006#));

This query will work fine if I remove the function.

Any help is greatly appreciated!

 

[charleswoods via AccessMonster.com]

Perfect! Both solutions work well. Thanks so much for the help!!!!

Well the function will not accept nulls as arguments since the arguments are
declared as dates. So if any of the fields you are passing to it contain a
null value (or a non-date value) then the function will error. You can try
changing the declaration line to.

Function Age (varBirthDate as Variant, varADate as Variant) as Integer

Or you can ensure that the query always passes dates to the function.

Function Age(VarBirthdate, VarAdate) As Integer
Dim VarAge As Variant

'I would actually return Null but you've defined the return value as integer
If IsDate(varBirthdate) = False then Age=0 : Exit Function
If IsDate(varADate) = False 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 = VarAge 'This should be all you need here.
' Age = Val(VarAge)

End Function

First of all, this function has worked for years... After returning from
vacation, I find that this function which is used to determine a person's

[quoted text clipped - 44 lines]

Any help is greatly appreciated!