Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Chicago, Illinois, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text box prints incorrect results of Iif (2003)

    A text box on a report whose control source is
    =IIf(nz([subNF1]![txtDATELabelNfo])>nz(![txtIssueDateLLT]),[subNF1]![txtDATELabelNfo],![txtIssueDateLLT])
    is printing the False value in error on an Access 2003 workstation.
    The front-end database is version 2000, while the back-end is 97. When printing from a 2000 workstation, the report correctly prints the True value. I also have a version 97 front-end of the same app that correctly prints the True value.
    Has there been a change in syntax for control names with Access 2003, or possibly with Access XP, that prevents the above from working properly?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text box prints incorrect results of Iif (2003)

    According this kb article ACC2002: Nz Method of the Data Source Control Does Not Function as Expected, there was a problem with the Nz function in Access 2002. I don't know if it still exist in Access 2003 and can't find info in the kb about 2003.
    Have you try :
    =IIf(nz([subNF1]![txtDATELabelNfo],0)>nz(![txtIssueDateLLT],0),[subNF1]![txtDATELabelNfo],![txtIssueDateLLT])
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Chicago, Illinois, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text box prints incorrect results of Iif (2003)

    No, I haven't tried specifying an explicit value for ValueIfNull, but in this case the two controls that are being compared by Iif do not contain null values. They are both text box controls bound to date fields, so I suppose I should use an arbitrarily old data such as #1/1/1900# for explicit values for the Nz function. My debugging is complicated by the fact that I don't have Access 2003 installed on my machine and must rely on feedback from one of my users.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text box prints incorrect results of Iif (2003)

    Access store dates as a numeric values. The expression Format(0,"mm/dd/yyyy") will give you 12/30/1899. So you don't need to provide a arbitrarily date, a 0 is fine.
    Francois

  5. #5
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Chicago, Illinois, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text box prints incorrect results of Iif (2003)

    Francois,
    I added an explicit 0 for ValueIfNull in the Nz functions, but it didn't change the results. I still get the False value from the Iif function, when the True value should be returned. But, this only happens in Access 2003; Access 97 and 2000 return the expected result. I'm wondering whether 2003 doesn't like my [subreportControlName]![controlName] > ![mainreportControlName] syntax, which apparently works with 97 and 2000.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text box prints incorrect results of Iif (2003)

    Maybe something, try :
    [subreportControlName]!Report![controlName] > ![mainreportControlName]
    Not the report name but the word 'report'
    Try also with . instead of ! (I never remind when to use one or the other)
    Francois

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Francois

  8. #8
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Chicago, Illinois, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text box prints incorrect results of Iif (2003)

    Thank you, Francois! I'm pretty sure that's the answer, but I won't know for sure until next week when my 2003 user is available, again.
    Thanks for your efforts and research, I should have been able to find this article myself, it was just too easy to post a help message on this forum and let someone else do the work for me.
    Thanks again, and thanks to Woody's Lounge for being such a great resource.

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text box prints incorrect results of Iif (2003)

    Well we were searching in the Nz direction and when you post about subreport I remember I had problems with some forms and subforms in a db I upgrade from 2000 to 2003.
    I hope that your problem is gone.
    Francois

Posting Permissions

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