use date in calculation 2

[phingers]

Access2000 report. I have a field [time_taken]which is formatted as hours and mins. e.g. 01:15 I need to be able to use the number of minutes to divide [qty] by. i.e. [qty]/[time_taken], 150/75 = 2. At the moment this returns 2880, I guess because of the formatting. Any ideas how I can do this? Destined for Greatness or Madness

 

[PaulBricker]

Format your time as minutes only and then do the calculation. See if that works.

Paul

 

[SarahG]

If Paul's solution doesn't work try using the DatePart function in your formula - this will return only the portion of the date you're interested in:

[qty]/DatePart("n",[time_taken])

"n" means minutes.

 

[phingers]

Paul, I only have Short Date as an option, which makes it hrs:mins format. Do you know how to format it as only minutes?


Sarah,

This worked sort of. It returns a value of 10, which is 150/15 (the minutes part of the time). I need it to be divided by 75 mins, or to include the hour part aswell. Is there a way to do this?
Destined for Greatness or Madness

 

[SarahG]

Oops .. missed that.

Try >>

[qty]/((datepart("h",time_taken) * 60) + datepart("n",time_taken&quot)

Note: if the time_taken is likely to be over 24 hours, then you would have to add in
datepart("d",time_taken) * 24 to the start of this.

It's the same principle as before. The first value you're sending to the datepart function is the date interval, so by putting in "h", datepart returns hours. Similarly "s" returns seconds, "m" returns months etc

 

[AccessAce]

If I understand correctly, you are trying to get something like the quantity per minute? If usch is the case, convert you hour:minute value to just minutes. Use the datepart function to extract the numbers you need (ex. 01:15 = 1x60+15 = 75). Then perform your calculation [qty]/[NumofMinutes] (ex. 150/75=2). This should work for you.

 

[phingers]

Thanks guys, it works a treat! Destined for Greatness or Madness