News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • If Then Statement in Report Text Box (Access 2010)

    Posted on Paul Yelk Comment on the AskWoody Lounge

    Home Forums AskWoody support Productivity software by function MS Access and database help If Then Statement in Report Text Box (Access 2010)

    This topic contains 2 replies, has 2 voices, and was last updated by  WSHiTechCoach 2 years, 7 months ago.

    • Author
      Posts
    • #508294 Reply

      Paul Yelk
      AskWoody Lounger

      I have created a database of my train collection.

      I have a report that displays everything about one item on a page.

      I have a field “Greenburg_Value” in which I have the value of the item – if the Greenburg catalog contains a price (so in some records this field is blank).
      I also have a field “Pct_MSRP_Greenburg_Value” in which if there is a value in “Greenburg_Value” field I then calculate the percent the Greenburg value is of the MSRP.

      For example, if the Greenburg_Value field contains 50.00 and the MSRP is 100.00, then the Pct_MSRP_Greenburg_Value field would be 50.0 (with percent understood).

      On the report I am showing this data as follows:

      =Format([Greenburg_Value],”$ 0.00 “) & “(” & Format([Pct_MSRP_Greenburg_Value],”00.0%”) & “)”

      That works fine if there is a value in the Greenburg_Value field. However, if the Greenburg_Value field is blank, then the report shows ().

      I know I need to put some type of IIF (Ifthen) statement in there, but not sure where.

      Can you help me put it in there?

      And how can I separate the % sign from the value? Currently it is showing 50.0%. My preference is to place a space between the value and the percent sign so it would appear as 50.0 %

      Thanks,

    • #1592193 Reply

      WSHiTechCoach
      AskWoody Lounger

      I have created a database of my train collection.

      I have a report that displays everything about one item on a page.

      I have a field “Greenburg_Value” in which I have the value of the item – if the Greenburg catalog contains a price (so in some records this field is blank).
      I also have a field “Pct_MSRP_Greenburg_Value” in which if there is a value in “Greenburg_Value” field I then calculate the percent the Greenburg value is of the MSRP.

      For example, if the Greenburg_Value field contains 50.00 and the MSRP is 100.00, then the Pct_MSRP_Greenburg_Value field would be 50.0 (with percent understood).

      On the report I am showing this data as follows:

      =Format([Greenburg_Value],”$ 0.00 “) & “(” & Format([Pct_MSRP_Greenburg_Value],”00.0%”) & “)”

      That works fine if there is a value in the Greenburg_Value field. However, if the Greenburg_Value field is blank, then the report shows ().

      I know I need to put some type of IIF (Ifthen) statement in there, but not sure where.

      Can you help me put it in there?

      And how can I separate the % sign from the value? Currently it is showing 50.0%. My preference is to place a space between the value and the percent sign so it would appear as 50.0 %

      Thanks,

      Simple put a space before the % in the format() and the percent sign will appear as 50.0 %.

      You were actually telling it to not put a space.

      Try:

      Code:
      =Format([Greenburg_Value],”$ 0.00 “) & “(” & Format(Nz([Pct_MSRP_Greenburg_Value],0),”00.0 %”) & “)”
      
      • #1592877 Reply

        Paul Yelk
        AskWoody Lounger

        Thank you – that worked!

        Now checking reports to ensure everything is printing as desired.

    • #1592194 Reply

      WSHiTechCoach
      AskWoody Lounger

      or

      Code:
      =Format([Greenburg_Value],”$ 0.00 “) &  IIF(IsNull([Pct_MSRP_Greenburg_Value]),””,   “(” & Format([Pct_MSRP_Greenburg_Value],”00.0 %”) & “)”)
    • #1592890 Reply

      WSHiTechCoach
      AskWoody Lounger

      You’re welcome.

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: If Then Statement in Report Text Box (Access 2010)

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.