Conditoinal Formatting codes

[Ian120]

Hi all, I am trying to make a conditional formatting that the field should be highlighted in red when the number entered is not an integer. I know this requires some codes so I tried something from my Excel experience. I tried to input the codes into the conditional formatting cells for Field Value is not equal to: "IsInterger([field])" , didnt work. "IsNumber([field])" didn't work as well. The system gives me no error message though.



To take a step further, is there a way to highlight the text box when the number entered is not a multiple of a certain value or zero? Like if I want the value to be 1.5, then the text box will be highlighted when the input is not 0, 1.5, 3, 4.5 etc.

Thanks guys! Really appreciated!

[Bob Fitz]

I tried to input the codes into the conditional formatting cells for Field Value is not equal to: "IsInterger([field])" , didnt work. "IsNumber([field])" didn't work as well.

Try: Int([Field])<>[Field]

[June7]

And for the other requirement try:
[Field] Mod 1.5 <> 0

[Ian120]

I am not sure why but now both fields turn red when input is non-zero.

[CJ_London]

probably a typo - perhaps show your code?

and your field type and any formatting of the field. If a double then it is quite possible what you see as 1 is actually stored as 1.00000001

[Ian120]

Hi all, thanks for being patient with me. The pictures below show the settings I used. I may be misunderstanding something from the earlier replies. Not sure. On the other hand, the fields are double with auto decimal places. I am using double since I want to input decimals, the default (I forgot which one) did not allow decimals.

[CJ_London]

it's almost certainly your use of a double - the auto setting affects what you see, not the underlying value. Leave the format property blank, see what values you really have

if that is the case try

int([stc18_h])<>round([stc18_h],1)

perhaps change the 1 to a 2 or 3 in necessary

note that if stc18_h can be negative then int effectively rounds down e.g. in the immediate window
?int(-1.2)
-2

but may not matter for this requirement

[June7]

The expressions are returning a True or False. Most likely the field values are neither True nor False (-1, 0). Instead of Field Value Is, use Expression Is.

[Ian120]

Thanks for the help guys. The integer check works well after I changed the Field Value is to Expression is, with the same codes up there. However, the check for multiple of 1.5 doesn't work. The whole line is now: Expression is [STC18_W] Mod 1.5 <> 0. The results are that the field wouldn't turn red no matter what I enter. I am sure I clicked 'Apply' before closing the window.

For the concern of being Double. The Format property was already blank as I left it. The "Field Size" is Double, "Format" is blank, while "Decimal Places" is Auto. The following rows are all empty until "Required" is YES as I set it, "Indexed" is No, and Text Align is General. I am not sure where to check the underlying values though.

Last but not least, could you explain what the codes actually mean? Int([Field])<>[Field] means the integer of the value is equal to its entered value? So Expression is Int([Field])<>[Field] will trigger the conditional formatting when it is not, i.e the entered value is not an integer?

[June7]

Correct. If field value is 15.4 that is not equal to 15 so it is not an integer.

Issue with MOD might be related to CJ's post #5

Want to provide db for analysis?