Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    163

    crosstab referencing form requires column headings?

    Ack,

    I've got financial data reported 8 quarters at a time, using a date entered into a form as a starting point. For some reason, if I type a literal date into the query criteria (ie - "Between 1/31/2004 and 3/25/04"") the query runs fine, but when I reference a form (ie "Between 1/31/2004 and Forms!Form.Field), I get the old "MS Jet does not recognise Forms!Form.Field as a valid reference or field" error. If I set the column headers it works - but I don't want to populate 20+ queries with column headers each quarter.

    Does anyone know of a way for a crosstab to reference a form object without having to specify column headers?

    Thanks.

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Does anyone know of a way for a crosstab to reference a form object without having to specify column headers?
    column headers are a necessary requirement of a cross tab query. Without headers, how would you know what column was what ? I assume you have a field with the Qtr in the underlying data. That should be assigned as your header. Any criteria that you don't want diplayed can be unticked so they don't show in your query output.

    but when I reference a form (ie "Between 1/31/2004 and Forms!Form.Field), I get the old "MS Jet does not recognise Forms!Form.Field as a valid reference or field" error
    I think you need to specify it as something like:
    Between 1/31/2004 and Forms!myForm!myField

    where myForm and myField are the names of your form and object respectively.

    Or for completeness:
    Between 1/31/2004 and Forms!myForm!myField.value

    Note also that the form must be open for it to be references.

    Chris
    Last edited by howey; 01-05-05 at 07:41. Reason: put correct quotes in

  3. #3
    Join Date
    Nov 2003
    Posts
    163
    Thanks, Chris. My main question is, why does the user have to specify a literal string for the column heading property? Why not a function or a query against a table? If I'm reporting 8 quarters at a time and I have 20 reports/subreports that reference crosstabs, that's major surgery to manually replace all those column headers with updated strings each quarter. It seems like Access should be able to do this since it already has the names of the columns (ie - the values of the rows in the source). Requiring the user to specify a literal string seems like reinventing a 3rd wheel (to mix metaphors).

    I'm thinking I'll write a vb proc to update the columnheader property of all relevant queries each time the form is updated. I use a similar procedure to throw form variables to pass-through queries hitting SQL-Server db's. Of course, in this procedure I'll need to cycle through the dataset and paste each record into a string.


    Thanks for your help.

  4. #4
    Join Date
    Nov 2003
    Posts
    163
    I've figured it out!

    Just to clarify - it was the object reference becoming invalid that was the issue, not the inclusion or lack of a string in the columnheaders property. If my source query uses a literal date ("#1/1/2004#) as a criteria, a cross-tab based on that query runs fine with no column headers specified. Once I substitute a form variable for the literal date criteria in the source, Access can no longer recognize the field on the form for any crosstab based on that query unless literal column headers are specified.

    I just stumbled across the fact that crosstabs absolutely require explicit declaration of form variables in their source queries by setting the Query | Paramaters property (ie - identifying the datatype of the variable).

    Thanks again for your help.

  5. #5
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi
    Maybe I did not understanding your problem correctly.

    Suppose you have the following data:

    Dept.....cost......date......Qtr
    Admin.....100.....20/12......4
    Admin.....200.....20/7........3
    Sales......150.....15/12......4
    Sales......70.......17/7........3

    Then a crosstab will give you:
    Dept.........3...........4
    Admin......200.........100
    Sales.......70...........150

    So 3 & 4 from your data becomes your column headings. If you'd selected data to include q2 then 2 would also be in your headins (assuming the data existed).

    But I now suspect the question you are asking is how do I change the report column headings so that they reference the ever changing cross-tab query headings. Is that right ?

    The problem is, how would you know how many columns a crosstab will generate or what the column headering might be ?

    If this is the case then I've done something similar by storing a variable in a stand alone table. The variable is the qtr number. Then all my form column headers can extrapolate the correct header from the table using a formula. So suppose I store 2004-03 as a text string in my table, then the next seven qtrs can be calculated with a bit of string manipulation and referenced in the report.

    Furthermore, I then get the query to use the variable as it's condition. So the only thing the user needs to do is change the variable and then the querys/reports will work for the appropriate periods. The only thing is with crosstabs is making sure all columns are generated i.e. all eight columns are generated regardless of whether there is data for that qtr or not.

    Your VB solution might be a better solution.

    Sorry if I'm still missing the point.

    Chris

Posting Permissions

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