Calculate time

[funkygoorilla]

Hi Everyone, hoping to get some help.
I have three fields, Time Received, Time Arrived, and Time Cleared. I need to be able to calculate the total amount of time in minutes. The fields are in military time.
For example,
Time Received 1300
Time Arrived 1305
Time Cleared 1330


A total of thirty minutes right? So, how can I formulate Access to do that for me?
Any help would be appreciated.
Im using Access 2010.

FunkyG

[Bob Fitz]

Take a look at the DateDiff() function in the help files

[rpeare]

are your fields stored as time fields or are they stored as text or numeric?

If they're time fields bob fitz's suggestion is the right place to look. If they're being stored as text or numeric you will likely have to run a conversion first for instance with the cdate function to change your string to a date then do the datediff function on it.

[funkygoorilla]

Hi, thanks for replying. They are currently stored as a numeric field. I want the end user to enter the information as numbers. Can you provide an example of the cdate function?

[rpeare]

well, what happens if the time is 0030 and it's a number field it would just show as 30?

what if it's 5 minutes after midnight would it just show as 5?

[funkygoorilla]

Ok, so I did some researching and I found a formula that I think could help me achieve this.
Here it is;
=(IF[TimeClearedResult]-[TimeReceivedResult]<0,[TimeClearedResult]+24*0.041666667-[TimeReceivedResult],[TimeClearedResult]-[TimeReceivedResult])*1440
It works in an excel spreadsheet, so does anyone think this could work in VB?
Thanks, FunkyG

[rpeare]

if it works in excel it should work in access but you never did answer my previous question :P

At any rate you should be more clear in your bracketing with formulas this part of the formula:

[TimeClearedResult]+24*0.041666667-[TimeReceivedResult]

Is going to perform 24*.041666667 first

Then it will likely perform the addition second then the subtraction because the addition appears first

What you likely want is this:

([TimeClearedResult]+(24*0.041666667))-[TimeReceivedResult]

Where you're adding time to your cleared result to get a positive result though you could just as easily have

([TimeClearedResult]+1)-[TimeReceivedResult]

Unless I've misinterpreted your bracketing altogether

[funkygoorilla]

OIk, I have the formula in the Control Source, but now my form is displaying the #NAME? error. I dont get why it is doing so.

This is the formula I have;
[Total Time=IF(TimeClearedResult]-[TimeReceivedResult]<0,([TimeClearedResult+(24*0.4666667))-[TimeReceivedResult],[TimeClearedResult]-[TimeReceivedResult]*1440]

Total Time is the name of the field I need the result stored in once the calculation is done.

Thanks for your help.
FunkyG

[rpeare]

=IF(TimeClearedResult]-[TimeReceivedResult]<0,([TimeClearedResult+(24*0.4666667))-[TimeReceivedResult],[TimeClearedResult]-[TimeReceivedResult]*1440

is all you need, if the formula only appears in a text box on a form.

[funkygoorilla]

Hmm, ok. Let me give that a try. For some reason when I type the formula in to the control source, it doesn't save it. Would you advise using a unbound field, or a field from the property field pick list.?

[rpeare]

you can't have a formula as the control source and have it named after a field in your record source.

For instance if you have a field in your record source called MUFFINS and you name a second (unbound) textbox MUFFINS with a formula in it things are going to break. If you are going to use a formula as control source it has to be unbound.

[funkygoorilla]

OK, so if I need the result of this calculation to go into a table, do I still put it in an unbound control? I dont know why, but I am severely confused.
Thanks, FunkyG

[rpeare]

no, you never store a calculated value. There's no reason to, any time you want to show it on a report or query or form you can perform the calculation at the time the record is displayed. Storing calculated values is a really bad idea unless you have absolutely no other choice and I have only found it necessary in 1 or 2 instances when dealing with very large datasets

[funkygoorilla]

Ok, so I did some research and you are correct. Storing calculated values in a table is not recommended. With that put to bed, how do I get this formula to work? This is what I currently have;
=(IIf([TimeClearedResult]-[TimeReceivedResult]<0,[TimeClearedResult]+24*0.041666667-[TimeReceivedResult],[TimeClearedResult]-[TimeReceivedResult]))*1440

It does calculate, but somewhere in here I believe the brackets are wrong or something. I tried your suggestion earlier but that didn't work either. Any ideas?
Thanks! FunkyG.

[rpeare]

What is wrong with it, is it giving you an unexpected value or is it just giving you something like #error? in the text box it's in?

Can you give an example of your data and what you expect your results to be. I'm assuming you want it to be in a number of minutes but you never did answer my question earlier about how your data is stored. For instance is 15 minutes after midnight stored as 0015 or is it stored as 15? is the field text or numeric? or is it set up as a time field?