Using Conditional Formatting

[radguy]

I would like to use Conditional Formatting to indicate when a certain quantity is either above or below a particular range. I know that you can do this for a report in Microsoft Access for an entire column but I would like to have each cell in that column to have it's on unique range. Is this possible in Microsoft Access?



Ex..

I have a table where I list all of the Tool Types that will be included in my database. In the table I also include the range of each tool that I would like to be stored in Inventory. I do not want the quantity of each tool to go above or below these numbers.



I created a Query that counts the number of tools that are in Inventory.


It get this number by looking at another table titled tool identification and counts the number of times each tool location says storage.



For the report, I want the number of tools that are in storage to appear in one column next to the tool type. If the quantity of tools fall below the range the cell will turn red. If the quantity of tools are between the range the cell will turn green. If the quantity of tools fall above the range then the cell turns orange.

Can I do this in Microsoft Access?

[June7]

Use Conditional Formatting. With form or report in design, select a textbox and then click Conditional Formatting on the ribbon Format tab.

[radguy]

Use Conditional Formatting. With form or report in design, select a textbox and then click Conditional Formatting on the ribbon Format tab.

When I do it this way it applies the conditions to the entire column. I want to assign each individual tool (row) it's own separate condition. Ex. Blade 1 Lower bound = 3 & Upper Bound =4 , 245 Insert Lower bound = 4 & Upper bound = 6

[June7]

Okay, so you want dynamic parameters in the condition. Best approach would be fields in the table that define the bounds then reference those fields in the Conditional Formatting expression.

[radguy]

Okay, so you want dynamic parameters in the condition. Best approach would be fields in the table that define the bounds then reference those fields in the Conditional Formatting expression.

I do have fields in the table that define the bounds. If you look at my original post, I have a table with the upper and lower bounds assigned to each of the tools. I have tried to do what you suggested but it is still not working.

Any other suggestions?

[June7]

Exactly how did you attempt? Did you include that table in the report RecordSource? Or did you use DLookup expression?

[radguy]

How would I use the DLookUp expression? Do I type in the DLookup expression into the conditional formatting expression and make a separate one for the upper and lower bound?





Here is what my table with the tool range looks like:

[June7]

Choose Expression Is from the dropdown.

Try:

Expression Is: [CountToolLocation] BETWEEN DLookup("[Lower Range]", "[Tool Range]", "[Tool Type]='" & [Tool Type] & "'") AND DLookup("[Upper Range]", "[Tool Range]", "[Tool Type]='" & [Tool Type] & "'")

That assumes the tool type field on the report actually has the descriptive text and not ID has value. Otherwise:

Expression Is: [CountToolLocation] BETWEEN DLookup("[Lower Range]", "[Tool Range]", "[ID]=" & [ID]) AND DLookup("[Upper Range]", "[Tool Range]", "[ID]=" & [ID])

[radguy]

Choose Expression Is from the dropdown.

Try:

Expression Is: [CountToolLocation] BETWEEN DLookup("[Lower Range]", "[Tool Range]", "[Tool Type]='" & [Tool Type] & "'") AND DLookup("[Upper Range]", "[Tool Range]", "[Tool Type]='" & [Tool Type] & "'")

That assumes the tool type field on the report actually has the descriptive text and not ID has value. Otherwise:

Expression Is: [CountToolLocation] BETWEEN DLookup("[Lower Range]", "[Tool Range]", "[ID]=" & [ID]) AND DLookup("[Upper Range]", "[Tool Range]", "[ID]=" & [ID])

Neither of the two are working for me. I copied and pasted exactly what you typed. Is there something that I should change?

[June7]

Is [CountToolLocation] a field in the report RecordSource? If not, what is name of the textbox that has the count value?

Try:

Expression Is: [CountToolLocation] >= DLookup("[Lower Range]", "[Tool Range]", "[Tool Type]='" & [Tool Type] & "'") AND [CountToolLocation]<= DLookup("[Upper Range]", "[Tool Range]", "[Tool Type]='" & [Tool Type] & "'")

If that doesn't work, want to provide db for analysis? Follow instructions at bottom of my post.

[radguy]

I have attached the database as a Zip File. There were a few things that I was having trouble with if you don't mind helping me out.

1. The original problem. I am having trouble creating a report that turns the cell a particular color if the the quantity of tools in storage is above or below a certain number. If it is above it will turn the cell orange. If it is below it will turn the cell red. If it falls within the upper and lower bound it turns the cell green.

2. If I fill out, save, print, and close a form I am having trouble getting a blank form to auto populate. When I open up the form again the previous form that I already filled out pops up. Do you know how to change that?

3. I am having trouble generating a report that looks like this:


I would like the report to list the type of tools on the side and the total rebuilt time, total returned, total rebuild hours, total hours waiting to be rebuilt, and total hours until used on CCAM. NOT days below target quantity. I have created queries that grabs all that info but I can't seem to put it in a report.

YOUR HELP IS GREATLY APPRECIATED!

[June7]

I don't understand the count query. Why are you doing a count on a table (Tool Identification) with unique records? How can the count ever be more than 1 for each tool type? The Conditional Formatting should work if you use your actual field names. I misunderstood the conditions. Set the textbox with green BackColor. Then set two formatting conditions.

Field Value is:
Less Than: DLookUp("[Lower Range]","[Tool Range]","[Tool Type]='" & [Tool Type] & "'")
Greater Than: DLookUp("[Upper Range]","[Tool Range]","[Tool Type]='" & [Tool Type] & "'")

However, the DLookup fails because of data type mismatch. The Tool Type field in Tool Range table is number, but the Tool Type field on report is text. (See comment and link at end of post).

What is the [Removed Number on Dial] field for? Why is it text type? Did you notice that there is a space in front of 12 in the CCAM 4 record? Are you aware that numbers saved as text will not sort numerically? They sort by alpha rules which means 10 will sort before 2. Need placeholder 0: 01, 02, ... 10, 11, ...

You have some long value lists as combobox RowSource. Will these lists change periodically? Consider a table. The tool types in the value list don't seem to be same as in the tables.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

Also advise not to build lookups in table http://access.mvps.org/access/lookupfields.htm

[radguy]

I don't understand the count query. Why are you doing a count on a table (Tool Identification) with unique records? How can the count ever be more than 1 for each tool type? The Conditional Formatting should work if you use your actual field names. I misunderstood the conditions. Set the textbox with green BackColor. Then set two formatting conditions.

Field Value is:
Less Than: DLookUp("[Lower Range]","[Tool Range]","[Tool Type]='" & [Tool Type] & "'")
Greater Than: DLookUp("[Upper Range]","[Tool Range]","[Tool Type]='" & [Tool Type] & "'")

However, the DLookup fails because of data type mismatch. The Tool Type field in Tool Range table is number, but the Tool Type field on report is text. (See comment and link at end of post).

What is the [Removed Number on Dial] field for? Why is it text type? Did you notice that there is a space in front of 12 in the CCAM 4 record? Are you aware that numbers saved as text will not sort numerically? They sort by alpha rules which means 10 will sort before 2. Need placeholder 0: 01, 02, ... 10, 11, ...

You have some long value lists as combobox RowSource. Will these lists change periodically? Consider a table. The tool types in the value list don't seem to be same as in the tables.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

Also advise not to build lookups in table http://access.mvps.org/access/lookupfields.htm

What edits do I need to make to ensure the DLookup does not fail?

I am also using the count query to count the number of ToolTypes that are in storage. It count the number of times each Tool Location for each ToolType says storage.

[radguy]

Should I create a separate table for the ToolType and reference that throughout the entire database? Because it does appear in three different tables?

[June7]

You need to get your related primary and foreign key fields the same data type. Cannot join a number field to a text field. You are saving a number ID in [Tool Type] field of [Tool Range] but saving the actual descriptive text in [Tool Type] field of [Tool Identification].

The DLookup fails because the criteria tries to apply text parameter from the report to the number [Tool Type] field in [Tool Range].

So yes, a ToolType table is probably good idea.