Subtracting two date/time fields

[Liz_I3]

Hi
working in access 2016 In my table I have a Date/Time field formatted as m/d/yyyy h:mm
In a query I created 2new field called Time In and Time Out here is how I did this Time In: IIf([IN]="IN",[Message Date/Time],"") and Time Out: IIf([OUT]="OUT",[Message Date/Time],"")
I then wanted to subtract the Time In from the Time Out I keep getting errors which I believe is because my 2 fields are now text. How can I keep them as a Date/Time field

Thanks
L

 

[Micron]

If IN isn't IN, then you're making the value an empty string (zls). Same for Out.
zls can't be used in a mathematical expression so it doesn't matter if you try to subtract from it, or subtract it from something else. You'll need another approach; perhaps a numeric or date value for the False part of IIF. If you can't figure it out, sample data might help to figure out something.

 

[Liz_I3]

Hi
I still cannot get it to subtract my 2 date/time fields (Subtracting [FirstOfTime In] from [LastOfTime Out] This is what I have been trying; If either of the rows are null the first part of my formula works but where they both have dates I get #ERROR

TS: IIf(IsNull([LastOfTime Out]),"",IIf(IsNull([FirstOfTime In]),'",[LastOfTime OUT]-[FirstOfTime In]))
and this
TS: IIf(IsNull([LastOfTime Out]),"",IIf(IsNull([FirstOfTime In]),"",Nz([LastOfTime OUT]-[FirstOfTime In],0)))

Both Date\Time fields look like this
13/01/2020 8:53:38 AM Some are null


Thanks for you help
L

 

[xenou]

I think it is a mistake to have a formula that returns a string value ("") or a date value. That's two different datatypes mixed together in one column, which is not good for database work.

It would be enough to just write:
[LastOfTime OUT]-[FirstOfTime In]

If either field was null this would return null, otherwise it will return the difference.

 

[Liz_I3]

Thanks I am getting there slowly
I am now at least getting an answer. but I don't think it is correct.

The goal is to subtract the last time an employee swiped through a door from the first time they swiped in an get the total number of hours/minutes they were in that room.

LastOfTime Out = 1/13/2020 5:25:16 PM minus FirstOfTime In = 1/13/2020 6:39:10 AM is giving me an result of 0.448680555498868

I was looking more for more like 11 hours and 10 minutes approx.

Thanks for taking the time to help

L

 

[Micron]

Isn't that just under 11 hours, not over? Your result is the fractional part of the day. You'll have to do math on it - like multiply by 24. If you Use Int function on the calc, you can get the hours. To get the minutes, I think you'll have to multiply the remainder by 60. If you just round up the fractional part, no one will realize that the digits represent the fraction of a day and not minutes themselves.

 

[xenou]

^ correct. Result is is 44.868056 percent of a day. This result is measured in day. You can convert it to minutes or hours or seconds as you require. Or even to a "time" or "date" (which it essentially is already).

 

[Micron]

Maybe this is what you need:
int(.448680555498868*24) & ":" & format(.448680555498868, "nn:ss")

Result should be 10:46:06
Substitute .44.. part with your calculation

 

[Liz_I3]

Thank you very much Micron for you help, I believe that is working perfectly. I will work with it and post back, but on my initial try it looks good

L