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