Add Tax to Invoices in the Microsoft Access Time and Billing Template
Question details
Add a single tax rate to the Microsoft Access Time and Billing template, calculate tax on billable hours, and display the tax amount and updated total on invoice reports.

- Product
- Microsoft Access Time and Billing template
- Device & OS
- not provided
- Scenario
- An existing time-and-billing database needs invoice tax calculations that apply to labor charges rather than expenses.
- Observed behavior
- The desired invoice shows the billable-hours subtotal, tax calculated using one rate, and a total that includes the tax.
Open the database in Access and identify the query and report or subreport that supply the billable-hours subtotal. Confirm that expenses are excluded from the taxable subtotal and decide whether older invoices must retain their original tax rate.
Calculate invoice tax using one rate stored in application settings
Use this approach when every invoice uses the same current tax rate and historical rate preservation is not required.
The object and field names below are examples for objects you create or adapt; existing names vary by template version. Calculate tax from the monetary subtotal of billable hours, not from the number of hours or a total that includes expenses.
In Access, select Create > Table Design and create a SystemSettings table with a numeric TaxPercent field. Save one settings record and enter the rate as a percentage, such as 8.25 for 8.25%. Keep this table to one settings record so the lookup returns the intended rate.
Select Create > Macro and add the SetTempVar action. Set Name to TaxRate and Expression to Nz(DLookUp("TaxPercent","SystemSettings"),0)/100. Save the macro and run it before opening the invoice report, or call it from your existing application startup macro. This converts 8.25 into 0.0825 for calculations.
In the Navigation Pane, right-click the invoice query and select Design View. Locate or create an invoice-level subtotal that sums only billable labor charges, and name that output BillableHoursSubtotal. Exclude nonbillable hours and expenses. Ensure the subtotal query returns one row per invoice.
Select Create > Query Design and add the saved invoice subtotal query as the source. Include its invoice identifier and subtotal fields, then add this calculated field in a blank column: TaxAmount: Round(Nz([BillableHoursSubtotal],0)*[TempVars]![TaxRate],2). Save the new query. Using the saved subtotal query as a source avoids calculating tax repeatedly on joined detail rows.
Right-click the invoice report or relevant subreport and select Design View. Set its Record Source to the query containing TaxAmount, retaining the fields needed by the existing layout. Add a text box with Control Source set to TaxAmount and Format set to Currency. Update the total text box to add the labor subtotal, any nontaxable expenses, and TaxAmount. For example, if those fields exist: =Nz([BillableHoursSubtotal],0)+Nz([ExpensesSubtotal],0)+Nz([TaxAmount],0).
Run the tax-rate macro, then open the invoice in Print Preview. Test a labor subtotal of $100 at 8.25%: tax should be $8.25. If the invoice also includes $20 in nontaxable expenses, the total should be $128.25. Confirm that the report does not add the tax twice.

Store the applied tax rate with each invoice to preserve historical amounts
Use this approach if issued invoices must remain accurate after the default tax rate changes.
Use WPS Spreadsheets for a spreadsheet-based invoice workflow
WPS Office is a free, lightweight alternative for creating spreadsheet invoices with labor subtotals, tax formulas, and printable totals. Its familiar interface and Excel format compatibility make migration straightforward for spreadsheet workflows. It does not open or modify Access databases, queries, or reports; the Access template changes must be made in Microsoft Access.

Frequently Asked Questions
How do I tax billable hours without taxing expenses?
In the invoice query’s Design View, calculate a separate subtotal containing only billable labor charges. Multiply that subtotal by the tax rate, then add expenses separately when calculating the invoice total. Do not multiply a combined labor-and-expenses total by the rate.
Should I enter 8.25 or 0.0825 for an 8.25% tax rate?
Either convention works if you use it consistently. In the settings example, enter 8.25 in TaxPercent and divide by 100 when initializing TaxRate. For the invoice-specific AppliedTaxRate field, store 0.0825 and multiply the labor subtotal directly by that value.
Why does the invoice show zero tax or fail to calculate it?
Check that SystemSettings contains the intended numeric TaxPercent value, then run the macro containing SetTempVar before opening the report. Also open the calculation query in Datasheet View to confirm that BillableHoursSubtotal and TaxAmount contain the expected values. The example expression treats a missing settings value as zero.
Will changing the global tax rate change old invoices?
Yes, if the reports recalculate tax using the current setting. To retain historical rates, save AppliedTaxRate with each invoice and use that field in the invoice query. Preserving finalized invoice amounts also prevents later edits to billing data from changing issued totals.
Can WPS Office edit the Access Time and Billing template?
No. WPS Office is not an Access database editor and cannot modify .accdb tables, macros, queries, or reports. You can export billing data from Access through External Data > Excel and use the exported workbook in WPS Spreadsheets for a separate invoice workflow.




