Time & Billing
Timesheets
Employee Time Clock
Total Time Spent
Time as a Duration

I have attempted to fix this for hours, I get #Name?, and even when I add additional text boxes for the start time, finish time, billable hours, and redue those formulas, I still get #Name?

I did do a new independent form that has beginning time, end time, and billable time, the form does calculate.

My form that does not work is connect to a table timeentrytbl.

Any suggestions?

The #Name? error usually means Access can't find one of the field or control names you're referencing in your calculation. It's a pretty common issue - most of the time it's just a small typo or a field name that isn't spelled exactly the same as it is in your table or form.

Since your independent form calculates correctly, but the one linked to your timeentrytbl table does not, double-check that the control names on your main form match the field names exactly, both in the table and on the form. Remember, if your form is bound to a table, make sure your textboxes are bound to the correct fields, or that you're referencing the correct control names in your formula. Sometimes the control on the form might be named "Text1" or something generic, even if it's showing the [StartTime] field.

To calculate the number of hours (and minutes) between two times, you can use a formula like this in the Control Source of your [Staffhours] textbox:

=DateDiff("n",[StartTime],[FinishTime])/60

That gives you the total hours as a decimal, for example, 2.5 for 2 hours 30 minutes. If you want the result formatted as "2:30" (hh:mm), you can use:

=Format(Int(DateDiff("n",[StartTime],[FinishTime])/60),"00") & ":" & Format(DateDiff("n",[StartTime],[FinishTime]) Mod 60,"00")

If you're still getting #Name? after checking all your field/control names, try this: select your [StartTime] and [FinishTime] controls on the form, look at their names in the property sheet, and make sure that's exactly what you're using in the formulas.

Also, check that your form's Record Source actually includes [StartTime] and [FinishTime] fields from your timeentrytbl table. If those fields aren't included in the record source, you'll get errors when trying to reference them.

If none of this works, a screenshot of your form in design view with the property sheet open could help spot the problem. Let me know if you need more help tracking this down.

Kevin & Captain,

Thanks for the follow-up. I have several questions as I forecast the future possibility and they are:
1. Should each staff have their own table to accumulate work history for clients?
2. Should each client have a separate table for services and work performed for them?
3. Should I have a separate table for staff cost?
4. Should I have a separate table for fees billed to clients?
5. How can I make the fees charged to clients or cost of staff be either the amount pulled from the respective table (e.g., staff cost, fees billed, etc.), or manually entering a different amount when the need arises?

Thanks for any feedback!

Excellent questions, Joseph. The short answer is no, you generally do not want a separate table for each staff member or each client. In relational database design, you usually create one Staff table to store all employees, one Client table to store all clients, and one TimeEntry table to record the work performed. Each time entry links to one staff member and one client.

For example, your TimeEntry table might contain fields like TimeEntryID, StaffID, ClientID, StartTime, FinishTime, HoursWorked, HourlyCost, and BillingRate. The StaffID points to the employee who did the work, and the ClientID points to the customer for whom the work was performed. This lets you store thousands of time records without creating a new table for every person or client.

Yes, it is a good idea to store staff costs and client billing rates in separate tables, or at least as fields in your Staff and Client tables. For example, your Staff table could include a CostPerHour field, and your Client table could include a DefaultBillingRate field. That way, when you create a new time entry, you can automatically pull in the current rates.

In most accounting and billing systems, however, you should copy those rates into the TimeEntry record when the work is entered. This preserves historical accuracy. If an employee's pay rate or a client's billing rate changes next year, old time entries will still show the original rates that were in effect when the work was done. This is similar to how you copy a product's price into an order at the moment that the order was placed.

To allow for exceptions, simply populate the HourlyCost and BillingRate fields automatically when the record is created, but let the user edit them manually if needed. That gives you the best of both worlds: automatic defaults with the flexibility to override them on a case-by-case basis.

So your structure might look like this:

Staff
- StaffID
- StaffName
- CostPerHour

Client
- ClientID
- ClientName
- DefaultBillingRate

TimeEntry
- TimeEntryID
- StaffID
- ClientID
- StartTime
- FinishTime
- HoursWorked
- HourlyCost
- BillingRate

This design is scalable, flexible, and follows proper normalization principles. It also makes reporting much easier later on.