Nz result is not as expected

[srburk]

I am attempting to determine the total amount of time that a physician (ATT) spends in a patient exam room. The physician goes in an out of the room multiple times.

I use a datediff command to count the number of minutes between the IN and OUT.
For each datediff, I use the Nz function to give me a default of zero if the physician did not enter the room.

All results are expected by doing the following:

ATTIN1 4:19 pm
ATTOUT1 4:30 pm
ATT1: =Nz(DateDiff("n",[ATTIN1],[ATTOUT1]),0)
ATT1 results (in minutes) is 11

ATTIN2 5:08 pm
ATTOUT2 5:10 pm
ATT2: =Nz(DateDiff("n",[ATTIN2],[ATTOUT2]),0)
ATT2 results (in minutes) is 2

ATTIN3 5:10 pm
ATTOUT3 5:28 pm
ATT3: =Nz(DateDiff("n",[ATTIN3],[ATTOUT3]),0)
ATT3 results (in minutes) is 18

ATTIN4
ATTOUT4
ATT4: =Nz(DateDiff("n",[ATTIN4],[ATTOUT4]),0)
ATT4 results (in minutes) is 0

ATTIN5
ATTOUT5
ATT5: =Nz(DateDiff("n",[ATTIN5],[ATTOUT5]),0)
ATT5 results (in minutes) is 0


The problem arises when I attempt to add the individual totals (ATT1 + ATT2 + ATT3 + ATT 4 + ATT5)

ATTT: [ATT1]+[ATT2]+[ATT3]+[ATT4]+[ATT5]
The result should be 31
Instead, I get 1121800 which is the 11 and the 2 and the 18 and the 0 and the 0 all stuck together.

Any ideas on a fix?

 

[sfreeman@co.mer]

Apparently, you have the values formated/typed as text.

Try:

SELECT
Val([ATT1])+Val([ATT2])+Val([ATT3])+Val([ATT4]) AS ATTT

FROM
tblATT;

 

[srburk]

PERFECT! That works! Thank you very much!