Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2011
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Strange change in addition Access 2010

    Hello,
    I have an Access 2010 MDB that keeps up with a simple attendance formats.
    We use a default of BLANK for data sheet, the number 0 for present, and number 1 for absent.
    In the query I just add the values in the fields and if = or > than 3 print on list.

    ( JulAM: +[Attendance 2011]![07/03A]+[Attendance 2011]![07/10A]+[Attendance 2011]![07/17A]+[Attendance 2011]![07/24A]+[Attendance 2011]![07/31A] )
    This is actual expression. as you see it is simple addition of selected fields.

    All was working fine until July (Remember Jan - June was ok) when we noted that if the field was blank (the default) then no action could be taked. No totals.

    Does anyone know if this is a programing change in Access and do we need to default to an actual number instead of null?
    Thanks

    BTY- I know its cumbersom but the secretary likes it because it will sort the way she wants.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Just a shot in the dark here, but was July {a school (assumption) vacation month} the 1st month when maybe no one entered any data zeros or ones because of vacation? This would then not produce a value in the variable JulAM causing a test such as JulyAm => 3 to fail since JulyAM would be NULL. If so you could rewrite the text on JulAM to test for NULL first and if not NULL then test for =>3.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    For all versions of Access, any addition that involves a Null in any of the values produces a Null result. So I think the RG is correct. The problem has always been there in the formula, but this is just the first time you have a Null.

    The NZ function can be used to convert Null values to Zero.

    JulAM: nz([Attendance 2011]![07/03A],0)+NZ([Attendance 2011]![07/10A],0)+nz([Attendance 2011]![07/17A],0)+nz([Attendance 2011]![07/24A],0)+nz([Attendance 2011]![07/31A],0) )
    Regards
    John



  4. #4
    New Lounger
    Join Date
    Sep 2011
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Red face Thank you gentlemen

    I thank you so much for your advice. I see this should have been in the formula before. This is why I like the Windows Secrets Lounge.

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    I'm sure I speak for John as well as myself in saying you're Welcome and that's why we are here.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Tags for this Thread

Posting Permissions

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