MS Access data conversion - Data Type mismatch error on a Time Calculation

Author

Data Type mismatch error on a Time Calculation

charles w via AccessMonster.com

2005-08-22, 8:25 pm

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(VarA
date), 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_ACUIT
Y.DESCRIP, EMSTAT_ARCHIVE_CHART
.CHRTNO, AGE(
& #91;EMSTAT_ARCHIVE_P
ATIENT]![DOB],& #91;EMSTAT_ARCHIVE_C
HART]![ARRVDATE]) AS AGEPT,
EMSTAT_ARCHIVE_CHART
.ARRVDATE, EMSTAT_ARCHIVE_CHART
.DSCHDATE INTO [tblAcuity-
Pedi]
FROM EMSTAT_ARCHIVE_PATIE
NT INNER JOIN (EMSTAT_ARCHIVE_CHAR
T INNER JOIN
EMSTAT_ARCHIVE_ACUIT
Y ON EMSTAT_ARCHIVE_CHART
.ACUITY = EMSTAT_ARCHIVE_ACUIT
Y.
CODE) ON EMSTAT_ARCHIVE_PATIE
NT.PTNTID = EMSTAT_ARCHIVE_CHART
.PTNTID
WHERE (((EMSTAT_ARCHIVE_CH
ART.ARRVDATE) Between #1/1/2003# And #7/1/2003#))
GROUP BY EMSTAT_ARCHIVE_ACUIT
Y.DESCRIP, EMSTAT_ARCHIVE_CHART
.CHRTNO, AGE(
& #91;EMSTAT_ARCHIVE_P
ATIENT]![DOB],& #91;EMSTAT_ARCHIVE_C
HART]![ARRVDATE]),
EMSTAT_ARCHIVE_CHART
.ARRVDATE, EMSTAT_ARCHIVE_CHART
.DSCHDATE
HAVING (((AGE(& #91;EMSTAT_ARCHIVE_P
ATIENT]![DOB],& #91;EMSTAT_ARCHIVE_C
HART]![ARRVDATE]
))<22));

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

Thanks agian!

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...ersion/200508/1

Jeff Boyce

2005-08-28, 11:24 am

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>

"charles w via webservertalk.com" <forum@webservertalk.com> wrote in message
news:533C88FEF2765@w
ebservertalk.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(VarA
date), 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_ACUIT
Y.DESCRIP, EMSTAT_ARCHIVE_CHART
.CHRTNO, AGE(
> & #91;EMSTAT_ARCHIVE_P
ATIENT]![DOB],& #91;EMSTAT_ARCHIVE_C
HART]![ARRVDATE]) AS
AGEPT,
> EMSTAT_ARCHIVE_CHART
.ARRVDATE, EMSTAT_ARCHIVE_CHART
.DSCHDATE INTO
[tblAcuity-
> Pedi]
> FROM EMSTAT_ARCHIVE_PATIE
NT INNER JOIN (EMSTAT_ARCHIVE_CHAR
T INNER JOIN
> EMSTAT_ARCHIVE_ACUIT
Y ON EMSTAT_ARCHIVE_CHART
.ACUITY =
EMSTAT_ARCHIVE_ACUIT
Y.
> CODE) ON EMSTAT_ARCHIVE_PATIE
NT.PTNTID = EMSTAT_ARCHIVE_CHART
.PTNTID
> WHERE (((EMSTAT_ARCHIVE_CH
ART.ARRVDATE) Between #1/1/2003# And
#7/1/ 2003#))
> GROUP BY EMSTAT_ARCHIVE_ACUIT
Y.DESCRIP, EMSTAT_ARCHIVE_CHART
.CHRTNO, AGE(
> & #91;EMSTAT_ARCHIVE_P
ATIENT]![DOB],& #91;EMSTAT_ARCHIVE_C
HART]![ARRVDATE]),
> EMSTAT_ARCHIVE_CHART
.ARRVDATE, EMSTAT_ARCHIVE_CHART
.DSCHDATE
> HAVING
(((AGE(& #91;EMSTAT_ARCHIVE_P
ATIENT]![DOB],& #91;EMSTAT_ARCHIVE_C
HART]!& #91;ARRVDATE]
> ))<22));
>
> PS: DOB is in a Short date format, ARRVDATE is in "mm/dd/yyyy hh:mm"
>
> Thanks agian!
>
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...ersion/200508/1

Sponsored Links