iif statement dealing with nulls

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • rdshultz
    #1

    iif statement dealing with nulls

    using access 2000. Have a form based on a couple of controls, it
    finds the last used Inquiry number in a table. So it finds the
    highest Inquiry number for the "Office" , "Company" and "YearStamp"
    selected by the user.

    Code below:

    =IIf(IsNull(DMa x("[Inquiry Number]","Inquiry" ,"[Office] = '" &
    [Form]![Office Code] & "' and" & "[Company] = '" & [Form]![Company
    Code] & "' and " & "[YearStamp] = " &
    [Form]![Year])),0,DMax("[Inquiry Number]","Inquiry" ,"[Office] = '" &
    [Form]![Office Code] & "' and" & "[Company] = '" & [Form]![Company
    Code] & "' and " & "[YearStamp] = " & [Form]![Year]))

    I use to just use the DMax but had to change it to an IIF because of
    nulls. But this code simply places #ERROR in the control when it is
    null.

    Am I doing something wrong? Wait obviously I am doing something
    wrong. Can someone lead me in the right direction.

    I'm doing this iif statement in the control source property.
    Tags: None
    • Phil
      #2
      Re: iif statement dealing with nulls

      just off the cuff, have you tried using NZ(dmax etc.) instead of the iif


      rdshultz <rdshultz@noote r.com> posted in
      news:87602d82.0 406300757.3a57f e14@posting.goo gle.com
      [color=blue]
      > using access 2000. Have a form based on a couple of controls, it
      > finds the last used Inquiry number in a table. So it finds the
      > highest Inquiry number for the "Office" , "Company" and "YearStamp"
      > selected by the user.
      >
      > Code below:
      >
      > =IIf(IsNull(DMa x("[Inquiry Number]","Inquiry" ,"[Office] = '" &
      > [Form]![Office Code] & "' and" & "[Company] = '" & [Form]![Company
      > Code] & "' and " & "[YearStamp] = " &
      > [Form]![Year])),0,DMax("[Inquiry Number]","Inquiry" ,"[Office] = '" &
      > [Form]![Office Code] & "' and" & "[Company] = '" & [Form]![Company
      > Code] & "' and " & "[YearStamp] = " & [Form]![Year]))
      >
      > I use to just use the DMax but had to change it to an IIF because of
      > nulls. But this code simply places #ERROR in the control when it is
      > null.
      >
      > Am I doing something wrong? Wait obviously I am doing something
      > wrong. Can someone lead me in the right direction.
      >
      > I'm doing this iif statement in the control source property.[/color]

      --
      Phil


        Comment

        • Pieter Linden
          #3
          Re: iif statement dealing with nulls

          how about using NZ(<<expression >>) which will convert the Null value
          to zero for you?

            Comment

            Previous template Next
            Working...