Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Aging Expressions (Access XP)

    I have an Accounts Receivable Report as part of my database and need the query to return any record with a balance due that is more than one day old.

    Fields in the query are [DateofSale], Age:Now()-[DateofSale] and [BalanceDue]

    My criteria is Not Null under Balance Due

    Every time I try to run it I get an invalid use of Null error message.

    Can someone help?

    thanks

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Aging Expressions (Access XP)

    Are there any records where DateofSale is Null?

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aging Expressions (Access XP)

    No, that will never happen

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Aging Expressions (Access XP)

    I would change your expression to include Date instead of Now.

    To know more about this problem, could you post the SQL of the query or post a compacted, zipped database.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aging Expressions (Access XP)

    To check for a null field, you should use the isnull function:

    <code>
    isnull([BalanceDue])
    </code>
    Legare Coleman

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Aging Expressions (Access XP)


  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Aging Expressions (Access XP)

    <hr>No, that will never happen<hr>
    Have you checked that it has not happened, or do you just believe that it never will?

    What happens if you add Not Null under [Dateofsale] in the query?

    or change the query expression to: Ageate()-CDate(NZ([DateofSale],"01/01/07"))

    This would use the Date 01/01/07 in place of any Null dates
    Regards
    John



  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Aging Expressions (Access XP)

    In a query, Is Null and Is Not Null are the 'standard' criteria to test for a field being null or not null. The IsNull function is used in expressions.

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aging Expressions (Access XP)

    Actually, Hans, I tried both of those and I keep getting an "invalid use of Null" message. I can't believe I'm having this problem on something that I thought would be so easy. I don't do a lot of this stuff any longer because my paying job keeps me so busy, but I guess I have to go back to the text books on this one. It's a very simple, I think, set up - I have a field for date of sale, a field for age of sale, i.e., today - date of sale, and a [balance due field]. Each of those is returning correct values, and if I do nothing, the query returns all records. However, when I want it to return only records with a balance due and start using what I think are the correct criteria in the Balance Due field, (IsNotNull), I err out

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Aging Expressions (Access XP)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  11. #11
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aging Expressions (Access XP)

    Here's some background. I used Date of Sale to make it easy to explain this problem, but in the actual database, which is used by a funeral service company, Date of Sale is actually Date of Death. I think you will agree Date of Death will never, ever be Null.

    This expression is going to be used in two places (databases). I've had to set up an archived database for them to handle everything up to 12/31/06. Starting 1/1/07, we are going to a new version of this thing. This became necessary based on varying tax rates over the years, on the fact that the State of Ohio from time to time mandates what they can charge tax on and what they can't, i.e., this year they have said they can only charge tax on merchandise, not services; (in the past we charged tax based on 50% of the cost of merchandise and 100% of the cost of services). So because there were 5500 records in the database and we didn't want to change formulas and have it recalculate everything, they decided this year that rather than just an archive table, they'd rather have a new database and go back to the old one only until the bills on all those records were paid.

    So, because there are unpaid records in that old database, I need to use the criteria there and any reference to 1/1/07 would be a problem.

    The new database starts with 1/1/07, and I'm not sure what impact what you have described will have there, but will try it when I get home tonight.

    Thanks for your help.

  12. #12
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aging Expressions (Access XP)

    Will strip it down and send it tonight.

    Thanks.

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Aging Expressions (Access XP)

    <hr>I think you will agree Date of Death will never, ever be Null<hr>
    Not even on prepaid plans? My father's arrangements were paid years in advance of his death.
    Charlotte

  14. #14
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aging Expressions (Access XP)

    No, never, those records are kept separately (they've never said why) and not entered into the database until after Date of Death.

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Aging Expressions (Access XP)

    My point is that Null values sometimes happen when they should not! (unless the table makes that Date field 'required')
    People click buttons when they should not, and create a record without realising its has happened.

    My use of 1/1/07 was arbitrary. You can replace that with any date you like. It might make sense to use 1/1/1900 so that any record with a Null date (if there are any) comes up with a number of days that is clearly wrong.
    Regards
    John



Page 1 of 2 12 LastLast

Posting Permissions

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