Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2005
    Posts
    43

    Unanswered: Static Column Headings in Crosstab Query

    Hi:

    I have a crosstab query that creates columns from two combo boxes selected on a form. This part works.

    I would like the column headings to remain fixed, such as "RUN1" and "RUN2," even though the user may select different data. I need to do this so that I can do calculations on those two columns, but I can't do calculations if the column names keeps changing. I've tried all sorts of IIF statements in the Property Sheet (Query Properties) Column Headings, but nothing has worked so far.

    I appreciate any assistance.
    Mos

  2. #2
    Join Date
    Jun 2005
    Posts
    43
    Just sharing my solution, in case it may help out someone else.

    In my crosstab query, first thing is to define the Parameters. This is via a button on the toolbar. For this particular query, I have two, and they would look like this:

    [forms]![name_of_form]![name_of_combo_box_01]
    [forms]![name_of_form]![name_of_combo_box_02]

    Then, in the query, for the column that is to be the Column heading in the Crosstab, I have the following:

    Expr1: IIf([COLUMN_variable]=[forms]![name_of_form]![name_of_combo_box_01],"Static_Column_Heading1","Static_Column_Heading2" )

    I also had to add a WHERE statement for that Column variable (in design query mode, pull the variable down again, in the Total row, select WHERE):

    [forms]![name_of_form]![name_of_combo_box_01]
    Or [forms]![name_of_form]![name_of_combo_box_02]

    For the sake of completion, under the Property Sheet, I added in the Column Headings:

    "Static_Column_Heading1","Static_Column_Headin g2"

    This forces the columns to appear, irrespective of any data appearing, which is necessary to do any math in a successive query.

    Hope this helps.
    Mos

Posting Permissions

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