Others

Add Tax to Invoices in the Microsoft Access Time and Billing Template

Muhammad TalhaMuhammad Talha Nov 13, 2026 869 views

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.

Add Tax to Invoices in the Microsoft Access Time and Billing Template
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.
Before you start

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.

Solution 1Recommended

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.

1
Create a tax-rate setting

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.

2
Load the rate into a temporary variable

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.

3
Identify the taxable labor subtotal

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.

4
Add the tax calculation to a query

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.

5
Display tax and the updated invoice total

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).

6
Verify the invoice calculation

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.

Calculate invoice tax using one rate stored in application settings
Rounding and rate changes: Access Round uses banker’s rounding, which may differ from the rounding required for your invoices. Confirm your applicable rounding rules. A global rate also changes the calculated tax when older invoices are reopened.
Free Microsoft Office alternative

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.

Calculate tax on billable labor with spreadsheet formulas.Work with Microsoft Excel .xlsx and .xls spreadsheet formats.Use a familiar spreadsheet interface to build printable invoices.Move exported Access billing data into a spreadsheet-based workflow.
microsoft office alternative - wps office

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.