Results 1 to 10 of 10
  1. #1
    Ian120 is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2023
    Posts
    23

    Conditoinal Formatting codes

    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!

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    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]
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    And for the other requirement try:
    [Field] Mod 1.5 <> 0
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Ian120 is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2023
    Posts
    23
    I am not sure why but now both fields turn red when input is non-zero.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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

  6. #6
    Ian120 is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2023
    Posts
    23
    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.
    Attached Thumbnails Attached Thumbnails access_8.png  

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Ian120 is offline Novice
    Windows 8 Access 2016
    Join Date
    Jan 2023
    Posts
    23
    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?

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    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?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Alt Codes
    By FL_Boy in forum Access
    Replies: 6
    Last Post: 10-07-2021, 09:17 PM
  2. Replies: 4
    Last Post: 11-24-2019, 08:24 AM
  3. Zip codes
    By joecamel9166 in forum Access
    Replies: 4
    Last Post: 04-01-2016, 02:12 PM
  4. Sql codes
    By hawke in forum Programming
    Replies: 1
    Last Post: 01-14-2015, 11:53 AM
  5. Zip Codes
    By Laurie B. in forum Access
    Replies: 6
    Last Post: 02-25-2011, 02:38 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums