Conditional formatting for visual layout

[captike]

I have what may be a strange need that I could use some help with. I have a database that keeps track of air packs from a fire department. It maintains their service and repair history as well as what truck each pack is assigned to. Each year we have to retrieve every pack and they get tested for proper operation and to make sure they are still working within their design specs (we call that PM or preventative maintenance). I currently have two tables that I'm using to track them. Table 1 (tblEquipment) has the pack's serial number, county property number, unit their assigned to, an auto-number (EquipmentID), and a couple other identifying parameters. Table 2 (tblMaintenance) tracks the service and any repairs made to each pack and consists of its own auto-number (MaintenanceID), EquipmentID (foreign key), the date the service was done, a description of the service performed, the service type (PM, repair needed, repair completed), and a note field, along with the name of the person entering the service. All of that is working great. The issue I need help with is a report for the yearly PM. I'd like to have a report that we can use visually to see what packs have been completed and what packs we still need to find and test. I've made a button that we can click on the pack's page that inserts a record into the maintenance table that basically says the testing was complete for that pack for the current year. I'd like a report that shows the packs grouped by the truck they're assigned to and if the pack still needs to be tested it would be red and if it has been tested, it would turn green. I've attached a paint image to this post that gives an idea of what I'm looking for. The part that is hanging me up is that I know you can't conditionally format a label, and I don't necessarily need any fields on this report. It basically just needs some text that changes color based on records in the Maintenance table. Is this something that can be done? If so, I'd love to hear how. Thanks in advance.

[Welshgasman]

Damn, that block of text is hard to read.

You can colour the font in the OnFormat event of the section?
So test what you use to determine what is not complete and format accordingly.

Alternatively have a textbox for the data and CF that?

[Micron]

For sure, use textboxes for data, not labels, and cf those.

[captike]

Damn, that block of text is hard to read.



You can colour the font in the OnFormat event of the section?
So test what you use to determine what is not complete and format accordingly.

Alternatively have a textbox for the data and CF that?

What is hard to read? The red and green color?

For sure, use textboxes for data, not labels, and cf those.

Yeah, I tried to play around with that yesterday when I realized you couldn't CF the labels. My hangup is I'm not sure how to say link this textbox with this record in the Equipment table and use a condition in another table. Do I use the tblEquipment as the control source for the report? If you could walk me through the steps I should take, I would certainly appreciate it. Thank you again.

[CJ_London]

My hangup is I'm not sure how to say link this textbox with this record in the Equipment table and use a condition in another table

hard to say without knowing what your table structure is but usual way would be to include the other table in your recordsource, joining on equipmentID? or use a dlookup?

You are showing what you want as a 'finishing' position, but we don't know your 'starting' position (table structure/relationships, perhaps some example data to illustrate the requirement

[captike]

Fair enough...I've attached screenshots of both tables. There is no sensitive data in either. So in the equipment table is the assignment of each pack (Unit and Position). In the maintenance table, there is EquipmentID which is the foreign key to the other table. So in my report, if a given pack has the description of "Annual Service Complete" AND the current years date, it would turn green. If it doesn't have both of those things, it would be red. I don't want it to find last years service, only this (the current year) year's. I already have a query and report that only finds this years packs that are complete, but it's in a list form (I've attached it as well), I would like a visual indication so we don't have to go down the list of all completed packs every time we finish one.

[Welshgasman]

What is hard to read?

This is what I meant?

I have what may be a strange need that I could use some help with. I have a database that keeps track of air packs from a fire department. It maintains their service and repair history as well as what truck each pack is assigned to. Each year we have to retrieve every pack and they get tested for proper operation and to make sure they are still working within their design specs (we call that PM or preventative maintenance). I currently have two tables that I'm using to track them. Table 1 (tblEquipment) has the pack's serial number, county property number, unit their assigned to, an auto-number (EquipmentID), and a couple other identifying parameters. Table 2 (tblMaintenance) tracks the service and any repairs made to each pack and consists of its own auto-number (MaintenanceID), EquipmentID (foreign key), the date the service was done, a description of the service performed, the service type (PM, repair needed, repair completed), and a note field, along with the name of the person entering the service. All of that is working great. The issue I need help with is a report for the yearly PM. I'd like to have a report that we can use visually to see what packs have been completed and what packs we still need to find and test. I've made a button that we can click on the pack's page that inserts a record into the maintenance table that basically says the testing was complete for that pack for the current year. I'd like a report that shows the packs grouped by the truck they're assigned to and if the pack still needs to be tested it would be red and if it has been tested, it would turn green. I've attached a paint image to this post that gives an idea of what I'm looking for. The part that is hanging me up is that I know you can't conditionally format a label, and I don't necessarily need any fields on this report. It basically just needs some text that changes color based on records in the Maintenance table. Is this something that can be done? If so, I'd love to hear how. Thanks in advance.

[Welshgasman]

I would likely set a flag (or even the colour required) with an Iif() function to check for 'Annual Service Complete' and use that in the report?

[CJ_London]

so referencing equipmentID=3 - how do you determine servicing is complete? leaving aside March 2022 records (assume they weren't there), its servicing was completed in March 2021 how do you determine that this is not a 'valid' completion? Is there a service interval for example? perhaps 3 months?, a year? 2 years?

Now looking at the 2022 records - it is complete, but there is another record, maintenanceID=15 with the same date. Are these to be ignored?

finally how are you picking up the fact it is complete? because it says so in the description field? (bad name for a field by the way, it is a reserved word). If so, what if there is a typo?

[captike]

so referencing equipmentID=3 - how do you determine servicing is complete? leaving aside March 2022 records (assume they weren't there), its servicing was completed in March 2021 how do you determine that this is not a 'valid' completion? Is there a service interval for example? perhaps 3 months?, a year? 2 years?

Now looking at the 2022 records - it is complete, but there is another record, maintenanceID=15 with the same date. Are these to be ignored?

finally how are you picking up the fact it is complete? because it says so in the description field? (bad name for a field by the way, it is a reserved word). If so, what if there is a typo?

Hey Ajax, thanks for your help. The service is complete when each EquipmentID has the words Annual Service Complete for the current year. The service interval is yearly. So this report (or probably a form, since I don't need to print it) is only used while we are in the process of servicing the packs. Once all of the packs are serviced (turn green), I don't need to see the status of last years servicing. If I need to see when a particular pack was serviced I can go into that pack's record.

As for the second question, yes. For the purposes of this request, MaintenanceID=15 should be ignored. I'm only interested in the unique EquipmentID's that have the description that says "Annual Service Complete" AND has the current year. That is how I'm determining that the service was complete which would make it turn green. I don't want EquipmentID=3 to turn green simply based on last year's service that was completed a year ago.

I'm certainly not committed to having it done this way. If you know of a better way to accomplish this, I'm open to suggestions. I went down this path for mostly ease of use. When a pack is completed service, I wanted an easy/quick way for the user to mark it. As it is now, I have a button that when pressed, it enters a record into the Maintenance table that enters the data. If I can retain the ease of use, I'd gladly change the method.

Thank you so much again for your continued help. I appreciate it very much and I'll look into the Description field. I hadn't realized that was a reserved word. I hope my response makes sense, but if not, please let me know and I'll try to explain it further.

[CJ_London]

I'm only interested in the unique EquipmentID's that have the description that says "Annual Service Complete" AND has the current year.

To clarify, 'serviced' today means there is a record in the maintenance table for a particular piece of equipment with the description 'Annual Service Complete' and and for 2022 the Maintenance date is a date in 2022. So on 1st January 2022 there will be no equipment currently serviced.

You don't mean?

I'm only interested in the unique EquipmentID's that have the description that says "Annual Service Complete" AND a Maintenance date within 12 months of today

If you do, then if today is 28th March 2022 and a particular piece of equipment was last serviced on 29th March 2021 then for your purposes you treat this as serviced?

[captike]

To clarify, 'serviced' today means there is a record in the maintenance table for a particular piece of equipment with the description 'Annual Service Complete' and and for 2022 the Maintenance date is a date in 2022. So on 1st January 2022 there will be no equipment currently serviced.

This one is correct....with the exception that on January 1st, 2023 there would be no equipment that would be currently serviced. My guess is you meant that though. The key is calendar year, not a years time from the last service date.

[CJ_London]

try this

SELECT Unit, Position, M.equipmentID is not null as Serviced
FROM tblEquipment LEFT JOIN (SELECT DISTINCT EquipmentID FROM tblMaintenance  WHERE year(MaintenanceDate)=year(date()) AND Description='Annual Service Complete') as M

[captike]

Ok, thanks. A couple questions...one, is this line going in the expression builder of the Conditional Format window or somewhere else? And two, is there supposed to be more code or does it stop right after the 'M'?

[CJ_London]

No, the recordsource of your form/report which is only showing Unit and position. If you need other fields, then add them to the query.

Then for conditionally formatting the position control the conditional format would be be something like

Expression is......[serviced]=true

and set the font colour to red

on the control, set the font colour to green (no need to set it through conditional formatting)