Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    85

    Unanswered: #error in report

    When there is no entty at all (when the query is empty, I get

    “#Error”

    in the summary control.

    Can some help me find a way of getting a zero instead.

    This problem has been on the forum in januari 2003 with topic IIf(IsNull(Sum([DollarAmount])),0, NZ(Sum([DollarAmount])))????

    izyrider give this explanation

    a possible explanation (i did not check it):

    ?? i guess Sum(null) = error
    and your isnull is looking at Sum([DollarAmount])
    but Sum([DollarAmount]) is never null, it is either error or a value.


    I tried his solutions
    =IIf(IsNull(DSum([delta_fee],"ticket_deri_delta")),0,1)
    and
    =IIf(IsNull(Som([delta_fee])),0,1)
    but with me both statements don't work. When I have 0ne or more records no problem I can use Count in the detail section or Sum in the footer.
    But if there are no records I get #error.
    Is there someone with a solutions. I have been searching and trying but no result.

  2. #2
    Join Date
    Jun 2003
    Location
    UK
    Posts
    68
    Izyriders explaination is correct. The function he is using is the Nz function which allows for null values. You can easily find more about this on Access Help

    Paul

  3. #3
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    85
    Originally posted by Funkster
    Izyriders explaination is correct. The function he is using is the Nz function which allows for null values. You can easily find more about this on Access Help

    Paul
    I know how to use the NZ function to get 0 value into a field. But my problem is that if there are no records I don't get the value 0 returned when I'm using count or IIF.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    May I ask what criteria would generate a query with zero records returned? Not even null?

    Your issue may be with your where clause, but I couldn't say for sure until I know how you're pulling it. Please post a bit more surrounding detail for the source of this report, hopefully it's a quickie.

  5. #5
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    85
    Originally posted by Teddy
    May I ask what criteria would generate a query with zero records returned? Not even null?

    Your issue may be with your where clause, but I couldn't say for sure until I know how you're pulling it. Please post a bit more surrounding detail for the source of this report, hopefully it's a quickie.
    I made a query with no criteria. I'm using the filter in the report like

    delta>0 and trade_datum Between [forms]![Dialoogvenster Rapporten]![Begindatum] And [forms]![Dialoogvenster Rapporten]![Einddatum]

    In the report foot is a field tot_delta=sum([delta_fee])

    Is this enough info for you.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by lodewijk
    I made a query with no criteria. I'm using the filter in the report like

    delta>0 and trade_datum Between [forms]![Dialoogvenster Rapporten]![Begindatum] And [forms]![Dialoogvenster Rapporten]![Einddatum]

    In the report foot is a field tot_delta=sum([delta_fee])

    Is this enough info for you.
    Ok, I believe the issue is with the "filter" you're describing. This is a problem with the way the dataset is formed.

    Essentially the dataset is formed from your "from" clause BEFORE the where clause, or filter is set. That's why you're returning nothing, as opposed to null. So, since you're comparing nothing, as opposed to null, you're returning errors.

    Here's a better example of what I'm saying:

    Suppose you have to tables.. master and detail. Now suppose that you have master.master_id as a foriegn key in detail. Now suppose you need to return all the detail for a given master id based on some criteria.. we'll say date just for example sake. So you have:

    SELECT detail.*
    FROM master INNER JOIN detail ON master.master_id=detail.master_id
    WHERE detail.date = date() AND master_id = 1

    This query is flawed. Here's why.
    If there are multiple detail entries for master_id "1" in the detail table, and some of them are outside your criteria range, they won't be assigned null. That's because:

    FROM master INNER JOIN detail ON master.master_id=detail.master_id

    DID have records in it. So they were not assigned null. In order to return a null value for the dataset, you have to include the additional criteria in the join statement, like so:

    FROM master INNER JOIN detail ON master.master_id=detail.master_id AND detail.date = date()

    Now when the dataset is created, anything in detail that is NOT within the specified date will be assigned null, and you will be able to return it in your query.

    And as you know, if you can return null, then you can apply the nz() function to assign 0 and correctly perform aggregate functions.

    Does that make sense?

  7. #7
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    85
    Originally posted by Teddy
    Ok, I believe the issue is with the "filter" you're describing. This is a problem with the way the dataset is formed.

    Essentially the dataset is formed from your "from" clause BEFORE the where clause, or filter is set. That's why you're returning nothing, as opposed to null. So, since you're comparing nothing, as opposed to null, you're returning errors.

    Here's a better example of what I'm saying:

    Suppose you have to tables.. master and detail. Now suppose that you have master.master_id as a foriegn key in detail. Now suppose you need to return all the detail for a given master id based on some criteria.. we'll say date just for example sake. So you have:

    SELECT detail.*
    FROM master INNER JOIN detail ON master.master_id=detail.master_id
    WHERE detail.date = date() AND master_id = 1

    This query is flawed. Here's why.
    If there are multiple detail entries for master_id "1" in the detail table, and some of them are outside your criteria range, they won't be assigned null. That's because:

    FROM master INNER JOIN detail ON master.master_id=detail.master_id

    DID have records in it. So they were not assigned null. In order to return a null value for the dataset, you have to include the additional criteria in the join statement, like so:

    FROM master INNER JOIN detail ON master.master_id=detail.master_id AND detail.date = date()

    Now when the dataset is created, anything in detail that is NOT within the specified date will be assigned null, and you will be able to return it in your query.

    And as you know, if you can return null, then you can apply the nz() function to assign 0 and correctly perform aggregate functions.

    Does that make sense?
    Yes, it makes sence. Thank you very much for the explanation. I wil try to adjust my query with a AND.
    Give me a little bit off time tonight to figure it out with my query. I get back to you tomorrow with the result.

  8. #8
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    85
    Originally posted by lodewijk
    Yes, it makes sence. Thank you very much for the explanation. I wil try to adjust my query with a AND.
    Give me a little bit off time tonight to figure it out with my query. I get back to you tomorrow with the result.
    Long night?
    Tried your suggestion but I get the message join expression not supported.

    This is what I tried
    SELECT trade_deri_delta.*
    FROM ticket INNER JOIN trade_deri_delta ON ticket.ticketnr = trade_deri_delta.ticket_id and trade_deri_delta.trade_datum=date();

    The master is ticket
    The detail is trade_deri_delta

    am I doing something wrong? It seems to me that after the AND there also has to be a relation between master and detail.

  9. #9
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    85
    Originally posted by lodewijk
    Long night?
    Tried your suggestion but I get the message join expression not supported.

    This is what I tried
    SELECT trade_deri_delta.*
    FROM ticket INNER JOIN trade_deri_delta ON ticket.ticketnr = trade_deri_delta.ticket_id and trade_deri_delta.trade_datum=date();

    The master is ticket
    The detail is trade_deri_delta

    am I doing something wrong? It seems to me that after the AND there also has to be a relation between master and detail.
    I spent already a lot of days solving this problem. More suggestions are welcome.

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by lodewijk
    I spent already a lot of days solving this problem. More suggestions are welcome.
    what error are you receiving? It may well be a parenthesis error.

  11. #11
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    85
    Originally posted by Teddy
    what error are you receiving? It may well be a parenthesis error.
    In Dutch

    #fout

    Parenthesis error is a error given using wrong parameters? If that is the case why is the sum function working with one record?

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by lodewijk
    In Dutch

    #fout

    Parenthesis error is a error given using wrong parameters? If that is the case why is the sum function working with one record?
    I was referring to access' bizarre tendency to require obscene quantities of parenthesis in the join statement to function properly. I'm not exactly sure how they would be applied here.

    is #fout essentially #error for us english folk?

    Also, can you run the query by itself and get a return?

  13. #13
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    85
    Originally posted by Teddy
    I was referring to access' bizarre tendency to require obscene quantities of parenthesis in the join statement to function properly. I'm not exactly sure how they would be applied here.

    is #fout essentially #error for us english folk?
    Yes #error for your english folk.

    Still the question why #error in the sum with no records and a value with one or more records.
    For me it is time to sleep otherwise I can't get up tomorrow.
    Talk to you.

Posting Permissions

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