Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problems with no records when using query (Access 2000)

    How do I get a query to put a zero if the query comes up with no records? I am checking to see if there is a balance that has not been submitted. Sometimes there is a balance, other times there isn't. What do I do???

    It works okay until I do a Sum, then if the total is zero, it comes up blank.

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

    Re: Problems with no records when using query (Access 2000)

    Would you like to be more specific please.

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

    Re: Problems with no records when using query (Access 2000)

    You don't provide enough details to give specific advice, but in such situations, you can often use the Nz function. Nz(Value1, Value2) usually returns Value1, but if that is null (blank), it returns Value2 instead. For example, Sum(Nz([MyField],0))

    If you need more help, please provide some details about your query.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems with no records when using query (Access 2000)

    Hi Pat,

    Well I'm doing a calculation sheet form that comes up and shows Total Fees Collected, which is every amount in the applied table that has the Board checkbox checked, and the Submit checkbox checked. Then the next amount on the form shows the total of the Amount Withheld from the last submission. This total comes from summing up any amount that has the Board checkbox checked, the Submit box checked, and the HalfSubmit box checked. Sometimes there might be an amount, sometimes there might not be. That's where the problem arises. If the total sum is zero, I get nothing showing when I click the datasheet view.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems with no records when using query (Access 2000)

    I finally got it to work! I added a couple of calculation fields to my first query and now it seems to be doing what I want it to do. Anyway, I have another question, but I'll post it.

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

    Re: Problems with no records when using query (Access 2000)

    Would you like to post your solution so that I can understand how you solved it, as well as understand it.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems with no records when using query (Access 2000)

    Sure thing, as soon as I get my other problem solved and my program updated.

Posting Permissions

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