| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 22873 | 2002-08-01 09:15:00 | MS Access Calculation SOS | T:-) (1297) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 67349 | 2002-08-01 09:15:00 | Can anyone help with a calculation in MS Access. I have created a query and I need to calculate a time field and a currency field to get an answer in a currency format. eg. 8:15 (8 hrs 15 min) x $5. For some reason I keep getting $1.72 instead of $40.75? TIA :-) | T:-) (1297) | ||
| 67350 | 2002-08-01 09:31:00 | Can you convert the time to minutes only ie 8:15 = 495 minutes Capt Jimbo |
Capt Jimbo (17) | ||
| 67351 | 2002-08-01 10:44:00 | Hi, I presume you are multiplying these fields in a form/report? If so, you probably have the following type of formula = [time] * [currency] The trouble is, these fields are of different data types so thats why your getting weird results. You need to convert the time to a number so Access can multiply it. try this which equals 40.75 ... =Val(Val([time]) & "." & Mid([time],4,2))*[currency] Theres probably a proper function for converting times to numbers but buggered if I can find it in the help. cheers Parry |
parry (27) | ||
| 67352 | 2002-08-01 12:33:00 | Date/Time is represented as a floating point number - the date part is the whole number to the left of the decimal and time the part to the right of the decimal. So 0:0 hrs = 0.0000, and 23:59 hrs = 0.9999999. So to get a 'time value' from a time variable, you need to multiply by 24. For example in your query: TotalCost: [Timefield]*24*[RateField] The $1.72 you get is 1/24th of the correct value! |
wuppo (41) | ||
| 67353 | 2002-08-01 21:51:00 | Excellent Wuppo, learned something new. Also, the amount you get will be just a raw number so to turn this into currency use the ccur function. eg: totalcost: CCur([time]*24*[amount]) |
parry (27) | ||
| 1 | |||||