Please help quickly!!

[Noreene Patrick]

I have looked in the forum for an answer to this question and I KNOW you have answered it many times...but, I am so weary with this db that everytime I try one of the answers I dont get what I need. I feel it is something to do with my format. But, here goes. (and I have a deadliine that is breathing down my neck!!!)

On my form, I need one field to calculate number of minutes expected for the job to complete i.e. tcards (47) * cbominutes (4) / #associates= Expected. This should give me minutes. My field is formatted short time.

Next field, should be actual (that is the only field I can get to work correctly). DateStarted (13:51)short time, DateEnded (14:51) short time. Correct answer 1:00 This is ok

Last field is Difference. And that should be difference of Expected - Actual.
It gives wrong answers.

But, I get crazy answers. For instance when I try to calculate Expected, I take tcards*cbominutes/#associates (47*4 / 3) should be 62.66 minutes. One time when I change associates it gives the correct answer but if I change associates again I get a crazy number, also the next record calculates 22:00 or something really weird.

What am I doing? PLEASE PLEASE HELP
I will send a copy db or anything you need.

 

[Noreene Patrick]

Okay, I got it narrowed down to this:

I put my calculation for Expected in an unbound field..It comes out exactly right. But, my unbound field is Fixed and my Expected field is short time. I dont know how to get the results from the fixed format to short time format.

Can someone help me, PLEASE!! That may solve my problem

 

[CEH]

Can't you simply go to the properties of the unbound and change "Format" to "Short Time"?

 

[Noreene Patrick]

Ive tried that and I always get some crazy number...I may need to use VBA code but it would be so complicated for my knowledge that I wouldnt know how to begin.

Thanks, Noreene

 

[PeterF]

Date/time fields in Access (and Excel) are not stored in a way you can do simple math functions to it. To make reliable calculations on time fields you have to convert them to for example minutes with following functions:

'To add 33 minutes to the current time
dateadd("n",33,time())

'To calculate the difference between a time field and the current time in minutes
datediff("n",timefield,time())

'Your calculation should look like this to store a time:
Dateadd("n", (tcards*cbominutes/#associates),0) 'which would result in 1:02

 

[Noreene Patrick]

PeterF

I used your calculation for Dateadd("n",(tcards*cbominutes/#associates),0) for the expected calculation and it works great.

However, when I tried to get actual to work correctly by using datediff("n",timeps,timepe) it gives me 0.00. (This should calculate timestarted-timeended). It works if I dont use datediff. But, I was trying to get it into same format as Expected so I could calculate Difference.

Difference of course being Expected - Actual

All my fields on form and table are short time. If I try using another format such as fixed it gives crazy answers even though the calculation works with short time

 

[PeterF]

I can't tell why the datediff function isn't working for you without knowing what fields are bound to a table and what not.

Some possible reasons why it's not working:

The format function returns a string, calculating with strings can give unexpected behavior. To be sure you're calculating with time use the timevalue function.
In Your case datediff("n",timevalue(timeps),timevalue(timepe)).

One of your fields is a calculated value and although displayed correctly actually is a integer, again the timevalue function should fix this problem.

Some backgrounds on how date/time is stored can be found here.

 

[Noreene Patrick]

The only fields that I bound were the TimePS and TimePE(time started and time ended). Expected, Actual and Difference are unbound.

On Actual field:
In Your case datediff("n",timevalue(timeps),timevalue(timepe)).
This returned 00.00.

On Expected field, I tried =dateadd("n",(tcards*cbominutes/#associates),0))and I got the correct answer...

But, on the Actual, the only way I can get a correct answer is just simply
=[timeps]-[timepe]. And I havent even tried to calculate Difference yet

 

[Noreene Patrick]

Since I can get both Expected and Actual to calculate correctly but in different data types, how would I get the actual field to calculate in hours and fractions for minutes like the expected field does? I am hoping that if I can overcome that obstacle, then the Difference field will subtract correctly.

I saw in one of the posts something about the Mid function to change minutes to an integer...Have NO idea how to do that. Any help would be most appreciated.

Another guy set up a similar db to mine, but he never got the fields to calculate...now I am weary trying to get mine done so I can say "I AM FINISHED WITH THIS DB FOREVER!!!!". I never realized what I would be up against.

Thank you for your help.