Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2002
    Location
    USA
    Posts
    66

    Unanswered: report (IIf/Nz) question

    Greetings,

    I have design a report to determine the total # of applicants in a given fiscal year. When the user clicks on “Fiscal Year Report” the following dialog box will appear, and the user can also change the dates for a desire year.

    Start Fiscal Year: 01/07/02
    End Fiscal Year: 06/30/03

    At the bottom I have =Sum([Jan]), =Sum([Feb]) etc. for fiscal year total. When the user changes the year, let’s say 06 – 07, since there is no data, I was trying to get the report to show nothing (blank), instead of #Error. I used IIf, Nz, IsNull etc. several ways and wasn’t successful. Can anybody tell me the correct syntax to use?

    Thanks,
    DD

  2. #2
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    I used this little trick:

    Private Sub Form_Load()
    ReSizeForm Me
    DoCmd.SetWarnings False
    If IsNumeric(Me.bc) Then
    If Me.S2 <> "" Then
    Me.Comando79.Enabled = True
    Else
    Me.Comando79.Enabled = False
    End If
    Else
    MsgBox "No data found", 0, "Alert"
    End If
    End Sub

    s2 is just a control on the form or the report itself taken from the query you use to create the report.

    I did use it for a form but you can use it for the report as well.

    Another way round is to create a hidden control that counts the results. If the control is blank then you've got no results!

    Hope this helps. If think about it a while you should find the solution like I did without any help.
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  3. #3
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173

    Re: report (IIf/Nz) question

    Originally posted by OCM
    Greetings,

    I have design a report to determine the total # of applicants in a given fiscal year. When the user clicks on “Fiscal Year Report” the following dialog box will appear, and the user can also change the dates for a desire year.

    Start Fiscal Year: 01/07/02
    End Fiscal Year: 06/30/03

    At the bottom I have =Sum([Jan]), =Sum([Feb]) etc. for fiscal year total. When the user changes the year, let’s say 06 – 07, since there is no data, I was trying to get the report to show nothing (blank), instead of #Error. I used IIf, Nz, IsNull etc. several ways and wasn’t successful. Can anybody tell me the correct syntax to use?

    Thanks,
    DD
    Have you tried IsError ... iif(IsError[sum([jan])],"",sum([jan])
    Please check the bracketing and parenthesis as it is difficult to count this late in the day ...
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •