Time Calculation

[ScotStuart]

Hi
I have a table where I have staff record received time and time to process
emails. Is it possible to have Access 2003 calculate in the same table (
Form) the lapsed time?

e.g. Fields: Time in , Time complete Time taken.

Thanks in advance

 

[ScotStuart]

Actually, I have calculated the time in Hours and minutes in a report, but if
the email is received one day at 6pm and cleared the next day at 12pm it
shows a time of -6 hours. Any ideas how I can get it to show the correct
18hrs? I have date received and date cleared fields as well as time in and
time returned.

Thanks

 

[Klatuu]

As Time Taken is a calculated value, it should not be stored in a table, but
calculated as you need it. Storing calculated values violates some basic
database design rules. It also takes longer, wastes disc space, and can
easily become incorrect.

As to your calculation problem, it is likely your calculation formula is
incorrect.
Post back with your calculation and lets see if we can improve it.

 

[ScotStuart]

Hi

Thanks for helping me.

The calculation is in the query which is SLA: DateDiff("n",[TIME],[TIME RTND])

The Report translates the minutes into hours and minutes no problem.

However, I suspect I have to take into consideration the Date Received and
Date Returned fields somehow.

 

[Klatuu]

I tried to duplicate your -6 results, but I get 18 hours using your
calculation, which is correct. Perhaps the error is where it is calculating
in the report.

I don't know how the other fields would be involved in this.
--
Dave Hargis, Microsoft Access MVP

Hi

Thanks for helping me.

The calculation is in the query which is SLA: DateDiff("n",[TIME],[TIME RTND])

The Report translates the minutes into hours and minutes no problem.

However, I suspect I have to take into consideration the Date Received and
Date Returned fields somehow.

As Time Taken is a calculated value, it should not be stored in a table, but
calculated as you need it. Storing calculated values violates some basic
database design rules. It also takes longer, wastes disc space, and can
easily become incorrect.

As to your calculation problem, it is likely your calculation formula is
incorrect.
Post back with your calculation and lets see if we can improve it.

 

[ScotStuart]

Sorry but I have tried again and it is still showing me -6

Query entry is this SLA: DateDiff("n",[TIME],[TIME RTND])
This calculates the minutes.

Report Text Box is : =[SLA]\60 & Format([SLA] Mod 60,"\:00"

However the fields I have and the entries in them are as follows:
Date Recd Time Date Retnd Time Retnd
15/10/07 18:00 16/10/07 12:00 Answer I get is -6:00

I tried to duplicate your -6 results, but I get 18 hours using your
calculation, which is correct. Perhaps the error is where it is calculating
in the report.

I don't know how the other fields would be involved in this.
--
Dave Hargis, Microsoft Access MVP

Hi

Thanks for helping me.

The calculation is in the query which is SLA: DateDiff("n",[TIME],[TIME RTND])

The Report translates the minutes into hours and minutes no problem.

However, I suspect I have to take into consideration the Date Received and
Date Returned fields somehow.

As Time Taken is a calculated value, it should not be stored in a table, but
calculated as you need it. Storing calculated values violates some basic
database design rules. It also takes longer, wastes disc space, and can
easily become incorrect.

As to your calculation problem, it is likely your calculation formula is
incorrect.
Post back with your calculation and lets see if we can improve it.
--
Dave Hargis, Microsoft Access MVP


:

Actually, I have calculated the time in Hours and minutes in a report, but if
the email is received one day at 6pm and cleared the next day at 12pm it
shows a time of -6 hours. Any ideas how I can get it to show the correct
18hrs? I have date received and date cleared fields as well as time in and
time returned.

Thanks

:

Hi
I have a table where I have staff record received time and time to process
emails. Is it possible to have Access 2003 calculate in the same table (
Form) the lapsed time?

e.g. Fields: Time in , Time complete Time taken.

Thanks in advance

 

[Klatuu]

Both time fields need to include the data. That is why you get the -6.
--
Dave Hargis, Microsoft Access MVP

Sorry but I have tried again and it is still showing me -6

Query entry is this SLA: DateDiff("n",[TIME],[TIME RTND])
This calculates the minutes.

Report Text Box is : =[SLA]\60 & Format([SLA] Mod 60,"\:00"

However the fields I have and the entries in them are as follows:
Date Recd Time Date Retnd Time Retnd
15/10/07 18:00 16/10/07 12:00 Answer I get is -6:00

I tried to duplicate your -6 results, but I get 18 hours using your
calculation, which is correct. Perhaps the error is where it is calculating
in the report.

I don't know how the other fields would be involved in this.
--
Dave Hargis, Microsoft Access MVP

Hi

Thanks for helping me.

The calculation is in the query which is SLA: DateDiff("n",[TIME],[TIME RTND])

The Report translates the minutes into hours and minutes no problem.

However, I suspect I have to take into consideration the Date Received and
Date Returned fields somehow.




:

As Time Taken is a calculated value, it should not be stored in a table, but
calculated as you need it. Storing calculated values violates some basic
database design rules. It also takes longer, wastes disc space, and can
easily become incorrect.

As to your calculation problem, it is likely your calculation formula is
incorrect.
Post back with your calculation and lets see if we can improve it.
--
Dave Hargis, Microsoft Access MVP


:

Actually, I have calculated the time in Hours and minutes in a report, but if
the email is received one day at 6pm and cleared the next day at 12pm it
shows a time of -6 hours. Any ideas how I can get it to show the correct
18hrs? I have date received and date cleared fields as well as time in and
time returned.

Thanks

:

Hi
I have a table where I have staff record received time and time to process
emails. Is it possible to have Access 2003 calculate in the same table (
Form) the lapsed time?

e.g. Fields: Time in , Time complete Time taken.

Thanks in advance

 

[ScotStuart]

Thanks, but what data are you referring to? Do I need to add the dates
somewhere
into a calculation?

Both time fields need to include the data. That is why you get the -6.
--
Dave Hargis, Microsoft Access MVP

Sorry but I have tried again and it is still showing me -6

Query entry is this SLA: DateDiff("n",[TIME],[TIME RTND])
This calculates the minutes.

Report Text Box is : =[SLA]\60 & Format([SLA] Mod 60,"\:00"

However the fields I have and the entries in them are as follows:
Date Recd Time Date Retnd Time Retnd
15/10/07 18:00 16/10/07 12:00 Answer I get is -6:00

I tried to duplicate your -6 results, but I get 18 hours using your
calculation, which is correct. Perhaps the error is where it is calculating
in the report.

I don't know how the other fields would be involved in this.
--
Dave Hargis, Microsoft Access MVP


:

Hi

Thanks for helping me.

The calculation is in the query which is SLA: DateDiff("n",[TIME],[TIME RTND])

The Report translates the minutes into hours and minutes no problem.

However, I suspect I have to take into consideration the Date Received and
Date Returned fields somehow.




:

As Time Taken is a calculated value, it should not be stored in a table, but
calculated as you need it. Storing calculated values violates some basic
database design rules. It also takes longer, wastes disc space, and can
easily become incorrect.

As to your calculation problem, it is likely your calculation formula is
incorrect.
Post back with your calculation and lets see if we can improve it.
--
Dave Hargis, Microsoft Access MVP


:

Actually, I have calculated the time in Hours and minutes in a report, but if
the email is received one day at 6pm and cleared the next day at 12pm it
shows a time of -6 hours. Any ideas how I can get it to show the correct
18hrs? I have date received and date cleared fields as well as time in and
time returned.

Thanks

:

Hi
I have a table where I have staff record received time and time to process
emails. Is it possible to have Access 2003 calculate in the same table (
Form) the lapsed time?

e.g. Fields: Time in , Time complete Time taken.

Thanks in advance

 

[Klatuu]

Your [TIME] and [TIME RTND] fields should include the date.
--
Dave Hargis, Microsoft Access MVP

Thanks, but what data are you referring to? Do I need to add the dates
somewhere
into a calculation?

Both time fields need to include the data. That is why you get the -6.
--
Dave Hargis, Microsoft Access MVP

Sorry but I have tried again and it is still showing me -6

Query entry is this SLA: DateDiff("n",[TIME],[TIME RTND])
This calculates the minutes.

Report Text Box is : =[SLA]\60 & Format([SLA] Mod 60,"\:00"

However the fields I have and the entries in them are as follows:
Date Recd Time Date Retnd Time Retnd
15/10/07 18:00 16/10/07 12:00 Answer I get is -6:00

:

I tried to duplicate your -6 results, but I get 18 hours using your
calculation, which is correct. Perhaps the error is where it is calculating
in the report.

I don't know how the other fields would be involved in this.
--
Dave Hargis, Microsoft Access MVP


:

Hi

Thanks for helping me.

The calculation is in the query which is SLA: DateDiff("n",[TIME],[TIME RTND])

The Report translates the minutes into hours and minutes no problem.

However, I suspect I have to take into consideration the Date Received and
Date Returned fields somehow.




:

As Time Taken is a calculated value, it should not be stored in a table, but
calculated as you need it. Storing calculated values violates some basic
database design rules. It also takes longer, wastes disc space, and can
easily become incorrect.

As to your calculation problem, it is likely your calculation formula is
incorrect.
Post back with your calculation and lets see if we can improve it.
--
Dave Hargis, Microsoft Access MVP


:

Actually, I have calculated the time in Hours and minutes in a report, but if
the email is received one day at 6pm and cleared the next day at 12pm it
shows a time of -6 hours. Any ideas how I can get it to show the correct
18hrs? I have date received and date cleared fields as well as time in and
time returned.

Thanks

:

Hi
I have a table where I have staff record received time and time to process
emails. Is it possible to have Access 2003 calculate in the same table (
Form) the lapsed time?

e.g. Fields: Time in , Time complete Time taken.

Thanks in advance

 

[ScotStuart]

Hi Again

Sorry I appear to have lost it. I don't understand what you mean. I have
amended query to SLA: DateDiff("n",[DATE RECVD, TIME],[DATE RETND,TIME RTND])
but this doesn't change the -6. In fact it doesn't do anything.

Your [TIME] and [TIME RTND] fields should include the date.
--
Dave Hargis, Microsoft Access MVP

Thanks, but what data are you referring to? Do I need to add the dates
somewhere
into a calculation?

Both time fields need to include the data. That is why you get the -6.
--
Dave Hargis, Microsoft Access MVP


:

Sorry but I have tried again and it is still showing me -6

Query entry is this SLA: DateDiff("n",[TIME],[TIME RTND])
This calculates the minutes.

Report Text Box is : =[SLA]\60 & Format([SLA] Mod 60,"\:00"

However the fields I have and the entries in them are as follows:
Date Recd Time Date Retnd Time Retnd
15/10/07 18:00 16/10/07 12:00 Answer I get is -6:00

:

I tried to duplicate your -6 results, but I get 18 hours using your
calculation, which is correct. Perhaps the error is where it is calculating
in the report.

I don't know how the other fields would be involved in this.
--
Dave Hargis, Microsoft Access MVP


:

Hi

Thanks for helping me.

The calculation is in the query which is SLA: DateDiff("n",[TIME],[TIME RTND])

The Report translates the minutes into hours and minutes no problem.

However, I suspect I have to take into consideration the Date Received and
Date Returned fields somehow.




:

As Time Taken is a calculated value, it should not be stored in a table, but
calculated as you need it. Storing calculated values violates some basic
database design rules. It also takes longer, wastes disc space, and can
easily become incorrect.

As to your calculation problem, it is likely your calculation formula is
incorrect.
Post back with your calculation and lets see if we can improve it.
--
Dave Hargis, Microsoft Access MVP


:

Actually, I have calculated the time in Hours and minutes in a report, but if
the email is received one day at 6pm and cleared the next day at 12pm it
shows a time of -6 hours. Any ideas how I can get it to show the correct
18hrs? I have date received and date cleared fields as well as time in and
time returned.

Thanks

:

Hi
I have a table where I have staff record received time and time to process
emails. Is it possible to have Access 2003 calculate in the same table (
Form) the lapsed time?

e.g. Fields: Time in , Time complete Time taken.

Thanks in advance

 

[Beetle]

I think what Dave is trying to say is that if those two fields are of a
Date/Time data type, then the calculation should automatically take the date
into account (even if only the time is displayed in the control).

 

[ScotStuart]

Unfortunately it doen't. I have the calulations there for both the query and
report, but have no idea how to get them to work to calculate the hours
incuding the day.

 

[Beetle]

Hi
I have a table where I have staff record received time and time to process
emails. Is it possible to have Access 2003 calculate in the same table (
Form) the lapsed time?

e.g. Fields: Time in , Time complete Time taken.

Thanks in advance

You shouldn't be storing calculated values in your table. Do the
calculation in a query or in an unbound control on a form or report.

 

[Beetle]

You could try setting the values programatically instead of relying on user
input. For example, you could use the OnCurrent event of your form to set the
Time In value;

Private Sub Form_Current()

If Me.NewRecord Then

Me![TimeIn] = Now()

End If

This will automatically insert the current date and time into that field
whenever they go to a new record. Depending on what else is going on in your
form, you may want to add some other code to prevent users from inadvertantly
creating new records that shouldn't be there.

Then you could have a command button called "Complete" (or something) that
would set the value of Time Complete and Elapsed Time;

Private Sub btnComplete_Click()

Me![TimeComplete] = Now()
Me![ElapsedTime] = code for calculation goes here

You may want to disable the command button once the Time Complete field has
a value using the OnCurrent event;

If Not IsNull (Me![TimeComplete]) Then

Me.btnComplete.Enabled = False

Else

Me.btnComplete.Enabled = True

End If

You also might want to lock the controls (Time In, Time Complete, and
Elapsed Time) so users can't inadvertantly change the values

If you use this method, you could eliminate the other date fields unless you
need them for some other purpose.

HTH