Results 1 to 9 of 9
  1. #1
    JPM1981 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2019
    Posts
    4

    Have a text box show 0 if another text box is below a certain number


    Hi all,

    I posted on another website, but IT suggested posting on this site. I apologize for the cross-post, but I am approaching a deadline, and the pressure is on.

    I am having trouble in Access 2010 getting an IIF function to work.


    I have 4 text boxes that show on a report; [BilledHours](standard format), [IssueHours](standard format), [Hour%](percentage format), and [HourDiff](standard format).


    This takes place in an invoice footer. I am trying to make it so that the [HourDiff] box shows a 0 only if the [Hour%] is less than 5, but execute a formula if the [Hour%] is over 5. The report shows multiple invoices, and there is only 1 invoice that needs the IIF to work.


    I am using


    "=IIF([Hour%]<=5,0,[BilledHours]-[IssueHours])"


    When I run the report, nothing changes, but when I change it to greater than, the boxes all change to 0.


    Thanks in Advance,


    John M.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you post a copy of the database in zip format?

  3. #3
    JPM1981 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2019
    Posts
    4
    My employer says no.

    I can try and answer structure questions about the report, but the database we created is considered proprietary, and specifically made for our company.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I never use Percent or Currency format setting in tables because I find them confusing in queries. I just tested with Percentage. Try:

    =IIf([Hour%]<=0.05,0,[BilledHours]-[IssueHours])

    Advise not to use spaces nor punctuation/special characters in naming convention.


    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
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Your question seems relatively straight forward, but as usual the devil is in the details.
    It could be field definition/data type, it could be some other syntax issue (not likely)...
    But seeing the data is best way to get a response when
    approaching a deadline, and the pressure is on.
    Can you concoct a simple database with the fields you need, some dummy values for Names etc and adapt a report from what you have? You may only nee 3-4 records.

    Dummy data like

    Polly Ester, Porky Pig, Heeza Farmer
    Central City, BigTown...

    we don't need anything private.
    Just data type and report layout with a sample expected result

  6. #6
    JPM1981 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2019
    Posts
    4
    Wow, that did it!!!

    It's funny how I"m always thinking about point zero numbers for percentages, but totally forget in reports.

    Thank you so much!

    Please mark as solved.

  7. #7
    JPM1981 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2019
    Posts
    4
    The unfortunate thing is that the queries this report uses is so intricate and finicky that it'd take longer to create a demo that worked properly, than by going through the normal process. This is the report in the database that has a sub-sub-sub-queries, and a sub-sub-query. Making sure the thing runs properly is a chore in itself. But thank you for taking the time to help me out.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Your question seems relatively straight forward, but as usual the devil is in the details.
    It could be field definition/data type, it could be some other syntax issue (not likely)...
    But seeing the data is best way to get a response when
    approaching a deadline, and the pressure is on.
    Can you concoct a simple database with the fields you need, some dummy values for Names etc and adapt a rport from what you have? You may only need 3-4 records.

    Dummy data like

    Polly Ester, Porky Pig, Heeza Farmer
    Central City, BigTown...

    we don't need anything private.
    Just data type and report layout with a sample expected result


    Update:

    Sample attached (I used query, not report)


    Ooops see you have it resolved. Good stuff.
    Attached Files Attached Files

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Glad it's working. You can always mark your own threads as "Solved" with Thread Tools dropdown above first post.
    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. Replies: 3
    Last Post: 05-22-2019, 12:25 PM
  2. Replies: 5
    Last Post: 02-24-2016, 10:58 PM
  3. Replies: 4
    Last Post: 04-26-2014, 02:47 AM
  4. Replies: 3
    Last Post: 12-22-2012, 05:33 PM
  5. Replies: 2
    Last Post: 07-21-2012, 01:21 AM

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