Calculating minutes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mary - 07 Dec 2005 22:02 GMT

Hi there,

I have a small database which keeps track of downtimes for our manufacturing
machines. The user will enter a date and time when the machine went down,
and when the machine is back up the user will enter this data as well.

I am now working on different ways to look at the data and I'm using the
following expression to calculate the time a machine has been out of
commission:

DateDiff("n",[downdate]+[downtime],[update]+[uptime])

This works well. The problem occurs when the machine is still down at the
time the report is run. Obviously there is no UpDate and UpTime noted yet,
so the calculation returns nothing.

Is there a way to tell the expression to use the current date and time if no
Update and Uptime has been recorded? Maybe some kind of IF statement?

Thanks,

Mary

Reply to this Message

fredg - 07 Dec 2005 22:23 GMT

> Hi there,
>
[quoted text clipped - 18 lines]
>
> Mary

=IIf(IsNull([Update]),DateDiff("n",[downdate]+[downtime],Now()),
DateDiff("n",[downdate]+[downtime],[update]+[uptime]))

Why are you using 2 fields to track date and time when one field using
Now() will do both?

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Mary - 07 Dec 2005 22:34 GMT

Thanks. It works...

I'm using two fields because the user needs to enter a date and a time when
the machine went down and then back up again. The user might enter this
data days after the event happened, and since Now() records current
date/time I didn't think I could use it.

Thanks!

Helen

> > Hi there,
> >
[quoted text clipped - 28 lines]
> Please respond only to this newsgroup.
> I do not reply to personal e-mail

Reply to this Message

Douglas J. Steele - 07 Dec 2005 22:57 GMT

It's still better to combine date and time into a single field. If you
really need just the date, or just the time, you can use the DateValue or
TimeValue function.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks. It works...
>
[quoted text clipped - 46 lines]
>> Please respond only to this newsgroup.
>> I do not reply to personal e-mail

Reply to this Message

Mary - 07 Dec 2005 23:08 GMT

I'm unsure how I would do it... Since the user need to pick a date and the
time the machine went down, don't I have to use two fields? I have a little
calendar on my form and then also a drop down to pick a time... Are you
suggesting that I should combine the two fields after the user has entered
the info and store the data in one field?

Thanks,

Helen

> It's still better to combine date and time into a single field. If you
> really need just the date, or just the time, you can use the DateValue or
[quoted text clipped - 55 lines]
> >> Please respond only to this newsgroup.
> >> I do not reply to personal e-mail

Reply to this Message

John Vinson - 08 Dec 2005 00:36 GMT

>Since the user need to pick a date and the
>time the machine went down, don't I have to use two fields?

No. If it's a textbox, the user can type 11/15/2005 11:30am into it
and it will work just fine.

>I have a little
>calendar on my form and then also a drop down to pick a time... Are you
>suggesting that I should combine the two fields after the user has entered
>the info and store the data in one field?

Yep. Just have a textbox (which can be invisible) bound to the
UpDateTime field; in the AfterUpdate event of your calendar control
and time combo box, set its value to the sum of the date and the time.

Access stores date/time data as a double float number, a count of days
and fractions of a day (times) since midnight, December 30, 1899.

John W. Vinson[MVP]

Reply to this Message

Mary - 08 Dec 2005 20:10 GMT

Ah. I get it! I will fix.

Thanks!

Mary

> >Since the user need to pick a date and the
> >time the machine went down, don't I have to use two fields?
[quoted text clipped - 15 lines]
>
> John W. Vinson[MVP]

Reply to this Message