Conditional Formatting

[Gismo]

Hi All,
not sure how to approach this one.

is it possible to use conditional formatting to format a control to:

If control = 0 then format to Stock
If control = -* then format to Shortage

 

[CJ_London]

what do you mean by format? 'Stock' is not a format

and are you mixing datatypes? 0 is numeric -* is text

 

[Gismo]

if a value in a control is 0, i want to display Stock in the control and not the actual value

 

[CJ_London]

use the formart property - for numbers there are 4 options, positive, negation, zero and null, separated by a semi colon.

don't know what other values you have but perhaps something like this

0.00;0.00;[red]"Stock"

 

[Gismo]

use the formart property - for numbers there are 4 options, positive, negation, zero and null, separated by a semi colon.

don't know what other values you have but perhaps something like this

0.00;0.00;[red]"Stock"

please could you explain how to do this.
do i still use conditional formatting - field value is?

 

[MajP]

You can do something like this. I do not think you can use the format property to handle 0. I have two textboxes on top of each other to make this work.

 

[CJ_London]

please could you explain how to do this.

one of the control properties is format - just copy paste the code I provided into it

do i still use conditional formatting - field value is?

No

@MajP I do not think you can use the format property to handle 0 - yes, you can.

 

[CJ_London]

then use this if I have more options such as stock or shortage or if i need to add more?

As I've said there are 4 states for numbers, so assuming your shortage is indicated by a negative value then use

0.00;[red]"Shortage";[red]"Stock"


note the [red] is not necessary - but just acts as a warning. Google 'access format property' to find out more

 

[MajP]

@MajP I do not think you can use the format property to handle 0 - yes, you can

I see it is both zero and null, not just null. Thanks.

 

[Gismo]

You can do something like this. I do not think you can use the format property to handle 0. I have two textboxes on top of each other to make this work.

i get an operand without an operator error

#=IIf([FieldValue]=0,"Stock",[FieldValue])#

# =IIf([Shortage]>0,"Shortage",[Shortage]) #

 

[CJ_London]

here are some things you can do with the format property

use a textbox as a resizeable and colourful checkbox

For whatever reason, checkbox controls cannot be resized. This suggestion addresses that issue and also enables the use of colour. To create, put the following in the format property of a textbox and set the control source to a boolean value 1. set format property -...

www.access-programmers.co.uk

 

[Gismo]

one of the control properties is format - just copy paste the code I provided into it
No

@MajP I do not think you can use the format property to handle 0 - yes, you can.

[0].[00;red]"Status";[red"Stock"]

how do it for >0 vlaues?

 

[Gismo]

[0].[00;red]"Status";[red"Stock"]

View attachment 83998

how do it for >0 vlaues?

i dont understand the code but not sure if this will work, i need to reference the 2 status controls to 2 different controls in the form

 

[Gismo]

this is what I have tried

# =iff([OTR5C QRY]![Stock Avilable]>[OTR5C QRY]![Reqmt Qty1],"Stock") #

# =iff([OTR5C QRY]![Stock Avilable]<[OTR5C QRY]![Reqmt Qty1],"Shortage") #

but i get a #Name? error

 

[CJ_London]

I've no idea where you are going with my suggestion - the code you are using a) seems to be in the wrong place and b) is nothing like what I provided.


this
[0].[00;red]"Status";[red"Stock"]

should look like this

0.00;[red]"Status";[red]"Stock"

Suggest you go with MajP's suggestion since I suspect I do not understand what it is you actually require - you seem to be wanting to reference other fields or controls

 

[Gismo]

I've no idea where you are going with my suggestion - the code you are using a) seems to be in the wrong place and b) is nothing like what I provided.


this
[0].[00;red]"Status";[red"Stock"]

should look like this

0.00;[red]"Status";[red]"Stock"

Suggest you go with MajP's suggestion since I suspect I do not understand what it is you actually require - you seem to be wanting to reference other fields or controls

yes, i need to compare other fields to the 2 different scenarios

thank you

 

[Gasman]

I believe the o/p is wanting to change the data in the control. Nothing to do with Format, though that is mentioned?

if a value in a control is 0, i want to display Stock in the control and not the actual value

 

[CJ_London]

if a value in a control is 0, i want to display Stock in the control and not the actual value

which is what you would use the format property for - solution already provided

 

[Gismo]

this is what I have tried

# =iff([OTR5C QRY]![Stock Avilable]>[OTR5C QRY]![Reqmt Qty1],"Stock") #

# =iff([OTR5C QRY]![Stock Avilable]<[OTR5C QRY]![Reqmt Qty1],"Shortage") #

but i get a #Name? error

above is what I tried

i need to display on a form "Stock" if my available stock is more than my required stock
if the required stock is more than available stock "Shortage" must be displayed

 

[Gasman]

which is what you would use the format property for - solution already provided

My apologies CJ_London. Works great