Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    2

    Angry Unanswered: Getting #error on my report

    I am a newb with MS access and am getting the #error on my report when a query returns with EOF.

    This is what I have done and the purpose of the report.

    I primarily need a report that will provide the sales by category for a date range(provided by the user) and the sales for the same categories but for the year previous. I have created 2 queries that are identical, except the second query minuses the date inputed from the user by a year. What is happening is that some categories are new this year and did not appear last year and those records are coming up with no records and causing the #Error to appear on my report.

    I have tried everything to trap it as a null value but alas nothing has succeeded. I am at a stand still. Please help.

    Thanks in advance.

  2. #2
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    Try this in your query

    At the moment you will have something like Sum(Sales). Change it to:

    Nz(Sum(Sales), 0)

    This will change all the null values to zeros
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

  3. #3
    Join Date
    May 2003
    Posts
    2
    No dice. The problem is not with handling a null value but that there is not a record to put a null value to. It gives an #error on the report when it cannot retrieve any data. What I want is that if it the query returns with no records to give me the value of 0.

  4. #4
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    Ah now I understand.

    What about this. Changing the join in your query to OUTER JOIN ie
    All from Categories and only equal records from Sales
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

Posting Permissions

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