Data Type Errors When Calculating

[Joanne]

I have a query where the "Admit Date" is subtracted from
the "Discharge Date" to give me the length of stay (LOS).
In my report, there is a field that shows the LOS. My
problem is that I would like to total all of the LOS
entries in the footer. I get an error that says I am
using the wrong data type. It thinks that my LOS field is
a date & not a number.

Does anyone know how to get around this?

 

[John Vinson]

It thinks that my LOS field is
a date & not a number.

That's because it IS. A Date is stored as a Double Float number, a
count of days and fractions of a day since midnight, December 30,
1899. If you subtract two dates you get a date - e.g. #11-Aug-2003# -
#1-Aug-2003# is actually January 9, 1900!

Instead of subtracting, use DateDiff:

LOS: DateDiff("d", [Admit Date], [Discharge Date])

This will give an integer number of days.

 

[JL]

You are right about that LOS is probably is a Date field.
Try to use "DiffDate" to do your date calculation.