Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2020
    Posts
    2

    Report If/Then Statement in Report Footer

    Hi, all. I'm hoping you can give me some guidance. I have a field in the Report Footer, [orride1] in which I'm trying to achieve the following: If the sum of [orride] is less than 0 (or 1, doesn't matter to me, however i can get it to work), then show it. If not, then show "0.00". I have a separate field doing "=Sum([orride]) and named it "orride1".

    This is how I have the field reading: =Iff([orride1])<0,([orride1]), "0.00)



    When I look at the report, the field [orride1] shows the expected results (-90000.00), but the calculation does not. I'm assuming there's something wrong in my calculation, but I can't see it.

    Can any of you please help?

    Thanks,
    Penny

  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
    Quote Originally Posted by Pennypinscher View Post
    Hi, all. I'm hoping you can give me some guidance. I have a field in the Report Footer, [orride1] in which I'm trying to achieve the following: If the sum of [orride] is less than 0 (or 1, doesn't matter to me, however i can get it to work), then show it. If not, then show "0.00". I have a separate field doing "=Sum([orride]) and named it "orride1".

    This is how I have the field reading: =Iff([orride1])<0,([orride1]), "0.00)

    When I look at the report, the field [orride1] shows the expected results (-90000.00), but the calculation does not. I'm assuming there's something wrong in my calculation, but I can't see it.

    Can any of you please help?

    Thanks,
    Penny
    Try:
    =Iff([orride1])<0,([orride1]), "0.00​")
    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
    Join Date
    Jul 2020
    Posts
    2
    Quote Originally Posted by Bob Fitz View Post
    Try:
    =Iff([orride1])<0,([orride1]), "0.00​")
    Thank you, Bob. That was a keying error on my part on this question. I do have a closing quote around 0.00 in my database. Any other ideas?

    Thank you much!
    Mandy

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Mandy

    I did test the expression before I posted it, so if it didn't work for you then I only have two other suggestions to offer at the moment.

    1) Forms and reports don't have field. They have controls that are usually bound to fields in the forms/reports Record Source but those controls don't necessarily have the same name as the field that they are bound to. Check that orride1 is the control name.

    2) Post a copy of your db for us to examine. Remove any sensitive data. Enter a couple of records to illustrate the problem. Compact and Repair the db then zip it and post the zipped file.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Penny? post#1?, Mandy? post#3?

    I presume iff is another typo - it should be iif

    you should not mix datatypes - summing something implies a number, "0.00" is clearly text

    suggest try

    =iif(Sum([orride])<0,Sum([orride]),0)

    or, depending on where you are going with this value, use the format property for the control to only display negative values or 0.00

    controlsource - =
    Sum([orride])
    format property - "0.00";-#,##0.00,"0.00","0.00"




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

Similar Threads

  1. Replies: 3
    Last Post: 03-08-2019, 03:25 PM
  2. Replies: 6
    Last Post: 05-25-2018, 09:53 AM
  3. Replies: 1
    Last Post: 10-14-2016, 07:00 AM
  4. Force Report Footer to before Page Footer
    By Charles CL in forum Reports
    Replies: 15
    Last Post: 01-21-2016, 12:06 PM
  5. Replies: 5
    Last Post: 08-26-2015, 05:23 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