Results 1 to 7 of 7
  1. #1
    numberguy is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Oct 2010
    Posts
    15

    Conditional number formatting

    Is there a way to change the number format in a report based off of the value of another field in the report? Specifically, I have a report where two of the fields are [units] and [quantity]. I am wanting the [quantity] field format to have no decimal places if the [unit] is "pounds". In all other cases ([unit] does not equal "pounds") I want the [quantity] field number format to have 2 decimal places. I have looked at conditional formatting, but that appears to only work for changing colors. Thanks for any help!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can do an expression in textbox:

    =Format([quantity], IIf([unit]="pounds", "0", "0.00"))
    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.

  3. #3
    numberguy is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Oct 2010
    Posts
    15
    Thanks for the reply. I am a novice. Where in the text box to you put the expression? I tried putting it in the Format section of the properties sheet for the [Quantity] text box contol and Access converted what I typed to "=For"m"at(["q"ua"n"tit"y"], IIF([u"n"it]=pounds, 0, 0.00))".

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    In the Control Source property.
    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.

  5. #5
    numberguy is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Oct 2010
    Posts
    15
    Thanks again for the reply. The control source for the Quantity text box refers to the [Quantity] field of the data base that is the report's Record Source. When I try to add the expression to the control source property for the Quantity text box I get an error. If I make the expression the only thing in the control source property for the Quantity text box the report shows #Type! in the Quantity field. What am I doing wrong?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The expression must be the only thing in the Control Source property. The textbox name must not be Quantity or that will cause a circular reference. Use something like tbxQty.

    Is the Unit field a text type? Is it storing the actual text "pounds" or an ID for a lookup table record?
    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.

  7. #7
    numberguy is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Oct 2010
    Posts
    15
    Changed the name of the Quantity text box solved the issue. Thank you for the help!!!!!!!!!!

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

Similar Threads

  1. Replies: 6
    Last Post: 05-26-2013, 09:10 PM
  2. conditional formatting
    By michele in forum Forms
    Replies: 6
    Last Post: 02-01-2012, 01:16 PM
  3. Conditional Formatting
    By schwabe in forum Reports
    Replies: 2
    Last Post: 02-01-2012, 10:28 AM
  4. conditional formatting right(..)
    By bbeernaert in forum Access
    Replies: 3
    Last Post: 08-25-2010, 12:33 AM
  5. Conditional Formatting
    By DanOzDirect in forum Reports
    Replies: 3
    Last Post: 07-21-2010, 08:49 PM

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