none
Supress / character if no denominator in Access Report

    Question

  • I have a query called qryPhysiciansSummary that has several measures on it and a report based on this query.  The report has the Doctors Name and Physician ID number in the header and several fields in the denominator.  It prints out one page per Physician and totals about 150 pages.  I am creating a "grid" in the details section of an access report.  I want to show numerator and denominator values from my query if the denominator exists.  The numerator is called PN6Num and the denominator is called PN6Den.  I created a text box with the following in it.

    =[PN6Num] & " / " & [PN6Den]

    If there is a value for the numerator and denominator it shows 3 / 3 as an example which is what I want.  If the denominator is null which happens about half the time I don't want to show anything.  However, the above text box show the / symbol.  How do I modify the expression I put in my text box so that nothing shows including the / sign if there is no denominator?

    Thanks,

    Chuck

     

    Wednesday, July 28, 2010 7:36 PM

Answers

  • Try to use IIf.

    =IIf(IsNull([PN6Den]);"";[PN6Num] & " / " & [PN6Den])

    • Proposed as answer by William Sessums Wednesday, July 28, 2010 7:53 PM
    • Marked as answer by ChuWil Wednesday, July 28, 2010 8:54 PM
    Wednesday, July 28, 2010 7:44 PM
  • In addition to Andrey's good suggestion, you can use an Access-specific (i.e. not portable to SQL or other databases) quirk: both the & and the + operators concatenate strings, but they handle NULL differently. & treats a null as a zero length string; + returns NULL if eitehr argument is NULL.

    To show just the numerator if the denominator is null, you can use:

    =[PN6Num] & (" / " + [PN6Den])

    To have the entire expression disappear if it's null, drop the parentheses and use + for both concatenations.

     


    John W. Vinson/MVP
    • Marked as answer by ChuWil Wednesday, July 28, 2010 8:56 PM
    Wednesday, July 28, 2010 8:48 PM

All replies

  • Try to use IIf.

    =IIf(IsNull([PN6Den]);"";[PN6Num] & " / " & [PN6Den])

    • Proposed as answer by William Sessums Wednesday, July 28, 2010 7:53 PM
    • Marked as answer by ChuWil Wednesday, July 28, 2010 8:54 PM
    Wednesday, July 28, 2010 7:44 PM
  • In addition to Andrey's good suggestion, you can use an Access-specific (i.e. not portable to SQL or other databases) quirk: both the & and the + operators concatenate strings, but they handle NULL differently. & treats a null as a zero length string; + returns NULL if eitehr argument is NULL.

    To show just the numerator if the denominator is null, you can use:

    =[PN6Num] & (" / " + [PN6Den])

    To have the entire expression disappear if it's null, drop the parentheses and use + for both concatenations.

     


    John W. Vinson/MVP
    • Marked as answer by ChuWil Wednesday, July 28, 2010 8:56 PM
    Wednesday, July 28, 2010 8:48 PM