Sum in Query

[Noreene Patrick]

Simple for you, but not for me

I have a form that includes picker name, login time, logout time

When I run a query on this info from the table, i want a calculated field to subtract login time from logout time (military time) and give me the total in minutes.

such as: TotalTime: =sum("[logintime]" - "[logouttime]")

I get an answer but I can tell it is not correct and also I dont know how to turn it into minutes

I appreciate any help.

Thanks, Noreene

 

[Mile-O]

Re: Simple for you, but not for me

I dont know how to turn it into minutes

Divide hours by 60.

As for your problem; are these times that occur over different days?

 

[Noreene Patrick]

Dividing by 60 was my first choice, but for some reason it just didnt calculate correctly. Did my equation look correct to you?

And yes, these will occur more than once a day, every day...When a picker swipes in to start his picking process, he will login his starttime and then at lunch, end of day, change into another dept, etc, he will logout of the picking process. I can calculate totaltime for each day to find his percentage... IF I can get this calculation to WORK!!!!!

Thanks, Noreene

 

[Mile-O]

The other thing is...are you just storing time in and time out as times only:

i.e.

TimeIn: 09:02
TimeOut: 17:01


or with a full date.

i.e.

TimeIn: 23/12/03 09:02:00
TimeOut: 23/12/03 17:01:00

I'd go with the latter as Date/Time fields with no date entered by the user (but a time) still put a date in. That way, also, you can use the DateDiff() function.

 

[Noreene Patrick]

Yes, I am storing data as times only.

I have a date field that is formatted as date()

And now that I think about it, there is a shift that will cross from one day to the next so maybe my times should be with date...

So, if I use this type of format (with date), what would my calculation look like then?

And could I allow the user to still input just the times only and it still calculate correctly?

Thanks,

 

[Mile-O]

Two textboxes, unbound. txtMyDate and txtMyTime

Another textbox (hidden) bound to the table:

The code on update:

Me.txtFullDate = CDate(Me.txtMyDate) + CDate(Me.txtMyTime)


That expression for each In and Out

And then the DateDiff() function (detailed in the help files) will give you the difference between the dates in years or days or hours or minutes or seconds etc.

 

[Noreene Patrick]

Thanks Mile-O-Phile

You have been a lot of help!!!

Thanks, Noreene