Question Time billing form (1 Viewer)

[Energy]

Hello everyone,

Just have a general query that I couldn't place in the relevant thread, because I don't know Access well enough.

What I am trying to do, is create a time billing form to track time spent on different tasks, with the following items in a neat form, in this format:

1) Client
2) Employee
3) Rate/hour
4) Date
5) StartTime
6) EndTime
7) BillableHours (*)

I am able to make a form that correctly works lets the user enter records for 1-6, with the user pressing crtl+shft+; to give the correct starttime, and then at the end of the task, the user can press the same and register the end of the task.

My problem is that I can't work out how to calculate 7) as one needs to input some VBA code that converts date/time to numeric values, allowing you to calculate time difference. However, I simply don't know; a) the proper 'code' that will calculate the time different between 5) and 6). I saw that it was perhaps the dateDiff command? And b) where I would even put that code into, i.e. the exact location where one goes to and types the code into... Is it a query? Do you set it to a Calculated value in the table editor? :banghead:

Please excuse my general ignorance of Access and coding. Quite new to this
Best,

 

[burrina]

Perhaps this will help. Used in a Query

GrandTotal: DateDiff("n",[StartTime],[EndTime])/60

On a form:

TimeWorked=Nz(Sum([GrandTotal]),0)

HTH

 

[Energy]

Hi,

Thanks for the advice, unfortunately I don't know how to input that code into the query. When I place it into the Criteria row and run the query, I get the error, "The expression you entered has an invalid . (dot) or ! operator or invalid parentheses. You may have entered an invalid identifier or typed parentheses following the Null constant."

Perhaps this will help. Used in a Query



On a form:

HTH

 

[Energy]

Hi.

I've just tried using this instead in the criteria row,

DateDiff("n",[StartTime],[EndTime])/60

This returns a query output table with just a single column populated with one date. The rest of the columns with start/end and billablehours are blank.

Any ideas?

Hi,

Thanks for the advice, unfortunately I don't know how to input that code into the query. When I place it into the Criteria row and run the query, I get the error, "The expression you entered has an invalid . (dot) or ! operator or invalid parentheses. You may have entered an invalid identifier or typed parentheses following the Null constant."

 

[burrina]

Here is a screenshot.

HTH

 

[burrina]

Can you post a screenshot of your qry?

 

[Energy]

Hi Burrina, thanks.

I cannot post links because I am new here and only have 4 posts total.

I followed your screenshot, I was putting your code into the Criteria row, when it should have been in the top field row, as in your example.

The query is now running, but the output is not correct for the Grandtotal

Would you mind if I emailed you the screenshots?
Will try PM you shortly. (Can't find a PM option to send you a message with my email?)
Best,

Can you post a screenshot of your qry?

 

[Energy]

Hi Burrina,

I seem to have your input on Grandtotal in the query working properly!!! Thanks so much. I now have a total number of hours worked on each task that's getting inputted into the table from the form. Brilliant! I just now to multiply total time (Grandtotal) by the hourly rate to give total cost of each task.

I am presuming that I use another query to do this multiplication? Am going to work on this now.

Thanks again

Hi Burrina, thanks.

I cannot post links because I am new here and only have 4 posts total.

I followed your screenshot, I was putting your code into the Criteria row, when it should have been in the top field row, as in your example.

The query is now running, but the output is not correct for the Grandtotal

Would you mind if I emailed you the screenshots?
Will try PM you shortly. (Can't find a PM option to send you a message with my email?)
Best,