conditional formatting (1 Viewer)

[lead 27]

Hello

On my Database I have used Conditional Formattion to change the colour of the qty to highlight when the qty is getting low however its not as good as I would hope as there are different type of definition of quantity (D of Q) e.g. Each (single items such as a screw) and Roll (such as cord and tape)

Conditional formatting doesnt work well because if I have it as less than 10 the qty is in red, its ok for the each items however not the roll as 10 rolls of tape is more than we would need.

I am just wondering if anyone knows what the code would be to change the colour depending on the qty and the D of Q

All i have got so far is:
If me.qty <10 and DofQ = EA then

Now im stuck for the change colour part

Any help would be great

 

[Jules48]

Hello

On my Database I have used Conditional Formattion to change the colour of the qty to highlight when the qty is getting low however its not as good as I would hope as there are different type of definition of quantity (D of Q) e.g. Each (single items such as a screw) and Roll (such as cord and tape)

Conditional formatting doesnt work well because if I have it as less than 10 the qty is in red, its ok for the each items however not the roll as 10 rolls of tape is more than we would need.

I am just wondering if anyone knows what the code would be to change the colour depending on the qty and the D of Q

All i have got so far is:
If me.qty <10 and DofQ = EA then

Now im stuck for the change colour part

Any help would be great

Hi lead 27,

How many different DofQ types do you have? If it's 3 or fewer you can use conditional formatting from the "Format" menu by adding extra conditions - see "ConForm.jpg" attached.

If you have more than 3 then I guess you'll need a series of If...Then...Else statements behind the qty field in your form (or CASE statements).
If the latter is the case let me know and I'll have another think about it.

Regards,

Jules

 

[lead 27]

Hi
I only have 3 at the min so I will try that but it may increase so I will keep trying the code, I have some ideas

Thanks for your reply

 

[lead 27]

Just gone up to 4 so will need to have a think about the code as I also want 3 colours (a red, amber, green system)

 

[lead 27]

OK I have wrote a code that works! However it is very long

If Me.QTY <= 10 And Me.DofQ = "EA" Then
Me.QTY.ForeColor = 255
End If
If Me.QTY > 10 And Me.DofQ = "EA" Then
Me.QTY.ForeColor = 33023
End If
If Me.QTY > 20 And Me.DofQ = "EA" Then
Me.QTY.ForeColor = 32768
End If
If Me.QTY < 2 And Me.DofQ = "RL" Then
Me.QTY.ForeColor = 255
End If
If Me.QTY = 2 And Me.DofQ = "RL" Then
Me.QTY.ForeColor = 33023
End If
If Me.QTY > 2 And Me.DofQ = "RL" Then
Me.QTY.ForeColor = 32768
End If
If Me.QTY < 2 And Me.DofQ = "BOX" Then
Me.QTY.ForeColor = 255
End If
If Me.QTY = 2 And Me.DofQ = "BOX" Then
Me.QTY.ForeColor = 33023
End If
If Me.QTY > 2 And Me.DofQ = "BOX" Then
Me.QTY.ForeColor = 32768
End If
If Me.QTY <= 5 And Me.DofQ = "MTR" Then
Me.QTY.ForeColor = 255
End If
If Me.QTY > 5 And Me.DofQ = "MTR" Then
Me.QTY.ForeColor = 33023
End If
If Me.QTY > 10 And Me.DofQ = "MTR" Then
Me.QTY.ForeColor = 32768
End If

As you can see. I am just wondering if this matters (will it slow it down?)? It works fine at the moment but the database isnt being used (no information is in it)

I am happy with it just wondered if anyone knew a way of making it smaller or improving it

Thanks

 

[lead 27]

ARHHHH!

Just tested it again and I have noticed that it changes the colour for the every record not just the one I want! (So if Item 1 has 12 and the qty should be green if I move through the fields they will all be green instead of the colour that correspond with the qty)

I have it set on "on update" could this be the problem???

 

[Len Boorman]

Think of it this way

Minimum qty is a field you have
Actual quanity is a field you have

Use a calculated field to find difference

If Minimum - Actual is less that 0 conditional format to Red

other situations could also cause different colour

This will work regardless of quantities because you are only looking a variance of Minimum to Actual

Len

 

[Rabbie]

I have had a try at making this a little more efficient. See what you think

If ME.dofQ = "EA" Then 
   If Me.QTY <= 10  Then
      Me.QTY.ForeColor = 255
   Elseif Me.QTY > 10  and me.Qty <=  20 then
      Me.QTY.ForeColor = 33023
   Else 
       Me.QTY.ForeColor = 32768
   End If
Elseif  Me.DofQ = "RL" Then
   If  Me.QTY < 2  Then
      Me.QTY.ForeColor = 255
   ElseIf Me.QTY = 2  then
      Me.QTY.ForeColor = 33023
   Else 
      Me.QTY.ForeColor = 32768
   End If
Elseif Me.DofQ = "BOX" Then
   if Me.QTY < 2 
      Me.QTY.ForeColor = 255
   Elseif Me.QTY = 2 then
      Me.QTY.ForeColor = 33023
    Else
      Me.QTY.ForeColor = 32768
   End If
Elseif  Me.DofQ = "MTR" Then
   If Me.QTY <= 5  then
      Me.QTY.ForeColor = 255
   ElseIf Me.QTY > 5 and Me.qty <= 10 Then
      Me.QTY.ForeColor = 33023
   else
     Me.QTY.ForeColor = 32768
   End If
End If

HTH

 

[lead 27]

Hi
Thanks for that it works fine, just trying to sort out my other problem.
Whats the best way to make a calculated field?

 

[Rabbie]

It is always best to start a new question in a new thread. It makes it easier for other people to find the solution.

For calculated values either calculate them in a query or in the Form/report that you need them for. It is not best practice to store calculated values in a table.

 

[lead 27]

Ok but think I have it sorted now. I will us the min qty and calculated field method.

Will let you know if it work so fingers crossed

Thanks

 

[lead 27]

Ok have got it to work perfectly and it also gave me an idea how i could improve another part of the database so thanks for your help.

Thanks

 

[Len Boorman]

Now before you rush off and do wonderful things in another area I suspect that you have based the conditional format of the variance of Min to Actual. No problem there at all but think on...

What if the variance is plus 100 but it is an item that you us 000,s of. Being down to 100 could be very critical and you format may not pick this up.

So now that you have learnt how to calculate the variance try calculation your variance as a percentage of the Min quantity.

Everything negative is formatted red but now you can be a bit more proactive and cover items where the consumption varies widely.

My example of being within 100 of something where the min is 1000 has a variance of 10%... the same as an item where Min is 10 and actual 11.

Sometimes pure numbers do not give you the scope you need when you can only set 3 conditional formats.

Just a thought

L

 

[Cosmos75]

Maybe this might help?
- 'Conditional Formatting' of controls in a continuous form