hello ladies and gents as it says on the tin i have a problem .Total newbie to access and have bluffed my way to this point . i have a report that calculates time differece and is displayed as 1:00 for one hour i need to multiply this by an hourly rate .....sounds simple enough but i cant get it to work any ideas remember im a total dunce when it comes to this


many thanks

In the query that the roport is based on
select an empty column and type this in the criteria section
NewRate: [yourtime differencefield] * 1.5

(1.5 being the hourly rate for example)

then your report create a text box and type Newrate in it's control source

Cheers for that smart but im having a problems still the box on the form is no problem .....the query on the other hand is I have tried putting
NewRate: [yourtime differencefield] * 1.5 in the critera box but it gives me a syntax problem so i put Newrate in the field box Which then changes it to Expr:[Newrate} and put [elapsed]*1.5 in the critera .....when i open the form or the query it prompts me for newrate and for elapsed .

what am i doing wrong here ???? ahhhhhhh ;-)

Below is some VB code you can cut and paste in a module.

Function ElapsedTime(Interval)
Dim x
x = Int(CSng(Interval * 24 * 60))

ElapsedTime = x
End Function


This can be put in a query to define a new field as the difference between two times. elapsedtime calls the above function and calculates the difference between field1 and field2. You can then take the new field (TimeDiff) and multiply it times your rate.

TimeDiff:elapsedtime([field1]-[field2])

ohhh I have absolutley no idea how to use VB code at all , im lucky i have got this far LOL

Put the code in the field section of the query not the criteria section
NewRate: [elapsed]*1.5

well i think i have got some of that sorted ........when i open the report it prompts me for elapsed is that right ...?

No. Elapsedtime is a function that should be called from your query. In your query you should create a field called TimeDiff. The calculation of the field is the function elapsedtime with the values being your two fields that you are trying to calculate the difference on.

If you are being prompted for elapsed time, something is wrong.

TimeDiff:elapsedtime([field1]-[field2])

hey cool i think you have managed to sort it for me many thanks so now i have multiple costs per day for each customer how can i get it to total it up and put each clients records on a single sheet. or am i pushing my luck here a little :D

Great...
Are you trying to total it on the report or in a query ?

If you want to total it in a query, in design mode, click on the epson in the toolbar (looks like a fance E) This will give you the group by options for each field. On the fields that you want totaled, select sum.

If you want to total on the reports,in design, right click in the upper left corner of the report in design, mode and choose sort/grouping.

Select to sort and total by customer. This will create header and footer records, and you can choose to hide the detail if you want.

Ever had one of those days ?? i think i am having a weeks worth :)
After giving my boss an update on "progress" he now informs me that it does not have to add time just fractions of time 0.25 0.50 etc .. so this is good news ......apart from i cant get it to total, but somehow by fluke i have got it to put each clients records on its own seperate sheet .......anyone want to have a go at totalling the hours for the month on the report for me ......??

I have tried attaching it but when its a .zip its 423kb and .rar is not allowed so i can email it if someone wants a go