Calculating minutes

[Mary]

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

 

[fredg]

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

=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?

 

[Mary]

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,

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

=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?

 

[Douglas J. Steele]

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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,

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

=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?

 

[Mary]

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
TimeValue function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

On Wed, 7 Dec 2005 14:02:03 -0800, Mary wrote:

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

=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?

 

[John Vinson]

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]

 

[Mary]

Ah. I get it! I will fix.

Thanks!

Mary

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


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]