MeadInKent
| Feedback | Excel conditional formatting |

Displaying Access Query results with conditional formatting

Access tables and queries are not designed to retain formatting features such as fonts and colours.

In Excel spreadsheets it is possible to display a set of records and highlight extraordinary or important values with different font colours, background colours or borders. It is possible to do something similar in Access, but only by creating a form to contain the query results.

The following example highlights the lengths of time taken by various customers to pay their invoices.

The new forms wizard was used to create a Tabular autoform, based on a 'Bookings' query.

Bookings-daystopay ox
USERGROUP_NAME INVOICE_DATE DATE_INVPAID INVOICE_AMOUNT Days_To_Pay
CAPED CRUSADERS CLUB 21 Jul 05 05 Aug 05 70 15
CHELSFIELD GIRL GUIDES 21 Jul 05 25 Jul 05 27 4
ST MARTINS CHURCH CHOIR 02 Aug 05 08 Sep 05 41 37
HIGH FLYERS ATHLETICS 02 Aug 05 20 Aug 05 23 18
ST MARTINS CHURCH CHOIR 02 Aug 05 07 Aug 05 50 5
SUBSTANTIAL THEATRE GROUP 12 Aug 05 123
ST MARTINS CHURCH CHOIR 08 Sep 05 20 Sep 05 329 12
HIGH FLYERS ATHLETICS 08 Sep 05 10 Sep 05 24 2
MR AND MRS JONES 08 Sep 05 26
HIGH FLYERS ATHLETICS 08 Sep 05 10 Sep 05 25 2
ST MARTINS CHURCH CHOIR 29 Sep 05 28 Oct 05 184 29
SUBSTANTIAL THEATRE GROUP 29 Sep 05 12 Oct 05 65 13
CAPED CRUSADERS CLUB 10 Oct 05 11 Nov 05 211 32
CHELSFIELD GIRL GUIDES 23 Oct 05 11 Nov 05 21 19

The following conditional formats were applied by selecting the 'Days to Pay' field (in design mode) and then the Form Design Tools ribbon option Format | Control Formatting | Conditional Formatting. It will highlight good transactions where the customer paid within 7 days, and bad ones where it took over 28 days.

Access dialog box for conditional formatting of data in a form based on field value

The same process and condition formula (but with different formatting) was then applied to the Date Paid and Amount fields. The condition refers to another field and therefore requires an Expression rather than a Field Value. It will highlight where no payment has yet been made and therefore the 'Days to Pay' field is empty (IsNull).

Access dialog box for conditional formatting of data in a form based on an expression

The formatting is displayed in both Form view and also Datasheet view. If you want the form to open in Datasheet view by default, set the appropriate form Properties: Default view ...... Datasheet view

If you use the Command Button Wizard to generate the necessary code for opening a form, it will always open in the Form view regardless of the form properties which may have been set to Datasheet view. To fix this it is necessary to edit the VBA code after the wizard has completed. Select the Click Event property, open the procedure and edit the code as follows.

Original code Modified code
Private Sub BtnOpenBookingsForm_Click()
On Error GoTo Err_BtnOpenBookingsForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Bookings-DaysToPay"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_BtnOpenBookingsForm_Click:
Exit Sub

Err_BtnOpenBookingsForm_Click:
MsgBox Err.Description
Resume Exit_BtnOpenBookingsForm_Click

End Sub
Private Sub BtnOpenBookingsForm_Click()
On Error GoTo Err_BtnOpenBookingsForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Bookings-DaysToPay"
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria

Exit_BtnOpenBookingsForm_Click:
Exit Sub

Err_BtnOpenBookingsForm_Click:
MsgBox Err.Description
Resume Exit_BtnOpenBookingsForm_Click

End Sub

The open form parameter acFormDS will cause it to always open as a datasheet.


file: access-condformat.htm Page last updated Mar14 MeadInKent.co.uk 2014