Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Victoria, Texas, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report based on a crosstab query (Access 97)

    I have a report based on a crosstab query that shows the number of items in a department. The report works fine as long as all the departments show up in the query. However if a department does not show up in the query, then I get an error message that says the Microsoft Jet database engine does not recognize the control name on the report as a valid field name or expression and does not run the report.
    Does anybody have a fix for this so I can get the report to run.
    Thanks
    JimB

  2. #2
    Star Lounger
    Join Date
    Feb 2001
    Location
    Wirral, Merseyside, Merseyside, England
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report based on a crosstab query (Access 97)

    Create a table with a list of departments then do a query with an outside join from the departments table to the query that you are running the crosstab query on. This should select all departments.

    Then create the crosstab query on your new query.

    Not sure whether this will work, but its what I would try first

  3. #3
    New Lounger
    Join Date
    May 2001
    Location
    California, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report based on a crosstab query (Access 97)

    If your departments do not change you can at on to the end of your SQL for the crosstab query just before the semi-colin "IN(Department One, Department Two, Department Three);" etc. without the quotes. This will create a column for each department regardless of the existance of data.

    Hope this helps.

    Warren

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report based on a crosstab query (Access 97)

    >>just before the semi-colin "IN(Department One, Department Two, Department Three);"


    Is this part of a WHERE clause as in "... WHERE DeptNo In(...)"
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    New Lounger
    Join Date
    May 2001
    Location
    California, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report based on a crosstab query (Access 97)

    Hi,

    No, it would be added to the end of his existing crosstab query to create the columns (fields) he needs for the report. Here is a copy of one from one of my databases:

    TRANSFORM First(StudentWorkQuery.WorkDescription) AS FirstOfWorkDescription
    SELECT StudentWorkQuery.Student_PersonID, StudentWorkQuery.FirstName, StudentWorkQuery.LastName
    FROM StudentWorkQuery
    GROUP BY StudentWorkQuery.Student_PersonID, StudentWorkQuery.FirstName, StudentWorkQuery.LastName
    PIVOT StudentWorkQuery.Day In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28,29,30,31);

    I needed numbers instead of the departments wanted here.

    Hope this helps.

    Warren

  6. #6
    Lounger
    Join Date
    Jan 2001
    Location
    Victoria, Texas, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report based on a crosstab query (Access 97)

    That will work to get a label for a column but the problem I am having is with the text control. There is a text control for each department and when there is no data for a department I get the error message. I need to have the text control show blank if there is no data.
    Thanks,
    JimB

  7. #7
    New Lounger
    Join Date
    May 2001
    Location
    California, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report based on a crosstab query (Access 97)

    Hi,

    Maybe I am misunderstanding. Sorry. I thought you were getting the error on the report when there was no data. This error would be true since there is not a column (Field) for the data in the crosstab query. If the crosstab query has the department column and this is used on the report for the Field there shold be no error even with no data. I am using Access 2000, but I don't think this is different for 97.

    You can try the NZ function in the report field and see if this helps. I don't know if 97 has the NZ??

    =NZ([Text265],0)

    Sorry, I don't remember 97 better.

    Warren

  8. #8
    Lounger
    Join Date
    Jan 2001
    Location
    Victoria, Texas, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report based on a crosstab query (Access 97)

    You didn't misunderstand me, I just didn't understand what you were telling me. I finally entered the department numbers in the In statement and the report works great.
    Thanks for the help,
    JimB

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report based on a crosstab query (Access 97)

    Thanks for clearing that up. This is a very useful trick, and I wish I had known about it before!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report based on a crosstab query (Access 97)

    I have had odd behaviour at times from Access when I define output field names as a number (such as 1, 2...). I always use D1, D2... so that it is clear that it is a column name.
    If I remember correctly it was in adressing an output column through code such as RS![1].

Posting Permissions

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