Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Crosstab - No record - Error (Access 2003)

    I hope this message makes sense.

    When I run this Cross tab Query I get the attached error message. This because there are no records with "3" as the Criteria. I have a heading for this type and without a record the whole report falls over. What can I do to stop this.


    TRANSFORM Count(tbl_Applications.ProjectID) AS CountOfProjectID
    SELECT tbl_Applications.StaffName, tbl_Applications.Region, Count(tbl_Applications.ProjectID) AS [Total Of ProjectID]
    FROM lup_Criteria INNER JOIN tbl_Applications ON lup_Criteria.CriteriaID = tbl_Applications.CriteriaID
    WHERE (((tbl_Applications.Status)="active") AND ((lup_Criteria.Criteria)="Caseload"))
    GROUP BY tbl_Applications.StaffName, tbl_Applications.Region, lup_Criteria.Criteria
    PIVOT tbl_Applications.SupervisionStandard;

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

    Re: Crosstab - No record - Error (Access 2003)

    Where does the '3' come into this? Are you running the query by itself, or is it the record source of a form or report?

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Crosstab - No record - Error (Access 2003)

    In the Table Applications there is a field called SupervisionStandard. There are five types 1,2,3,4 and MH. There are no applications with a supervision standard of 3 at this moment.

    Kerry

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

    Re: Crosstab - No record - Error (Access 2003)

    But '3' isn't mentioned in the SQL for the query. So I repeat my question: Are you running the query by itself, or is it the record source of a form or report?

  5. #5
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Crosstab - No record - Error (Access 2003)

    The query is a record source for a report.

  6. #6
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Crosstab - No record - Error (Access 2003)

    This is a dump of the Report in question. I had to create a record with 3 as the supervision level to make it run.

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

    Re: Crosstab - No record - Error (Access 2003)

    When you created the report did you have 3 in the data at the time?

    You probably have a column for 3 but when it's not in the query it deepends.

  8. #8
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Crosstab - No record - Error (Access 2003)

    Hi Pat

    Yes - at the time I created the report I did have a 3 in the data, but now I dont, but will at sometime.

    There must be a way to adjust the query to say "if there is a no record in any type of "SupervisionStandard" then put in a Zero or something?

    Kerry

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

    Re: Crosstab - No record - Error (Access 2003)

    What you can do is to dynamically setup the report in the OnOpen event. You set each Details section ControlSource to one of the queries columns and set the remaining columns to not visible.

    There is an example of how to do this by Hans in the search.

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

    Re: Crosstab - No record - Error (Access 2003)

    Hi Kerry,

    Try <post#=155586>post 155586</post#> to see how it's done.

Posting Permissions

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