Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cross Tab (2000)

    Hi,

    I have a report base on four Crosstab Queries with review date fields. I have a report date menu form to let user to enter report date then open the report. I tired to enter the following txt in the Criteria field of ReviewDate (Total: Where) in the Crosstab query:

    Between [Forms]![frmReport]![Beginning Date] And [Forms]![frmReport]![Ending Date]

    I got the "Microsoft Jet database enjine does not recognize "[Beginning Date]" as a valid field name or expression." message.

    I know it works on the regular query. So what should I do now?

    Please advice.

    Regards

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

    Re: Cross Tab (2000)

    You must declare the parameters explicitly:
    Open the crosstab query in design view.
    Select Query | Parameters...
    Enter [Forms]![frmReport]![Beginning Date] in the Parameter column, and select Date/Time as Data type.
    In the next row, enter [Forms]![frmReport]![Ending Date] in the parameter column, and select Date/Time as Data type.
    Click OK.
    Save the query.

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

    Re: Cross Tab (2000)

    You can declare parameters in Query | Parameters in any query that has parameters, whether it is a crosstab query or not. In many situations it isn't required, but it is essential for crosstab queries if you want to use it as the record source for a report.

    To refer to a control c on a subform b on a main form a, you can use [Forms]![a]!<!t>[b]<!/t>![c]. In this expression, b must be the name of the subform as a control on the main form. This is not necessarily the same as the name of the subform in the database window. Open the main form in design view and click once on the subform to see its name as a control.

  4. #4
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross Tab (2000)

    Edited by HansV to replace <!t>[b]<!/t> with <!t>[t]<!/t>b<!t>[/t]<!/t>, otherwise <!t>[b]<!/t> is interpreted as the tag for bold text.

    Thank you so much, Hans. As always it works now.

    Just want to be clear, if I use crosstab query with parameters, I have to declare it in Select Query | Parameters... However, it doesn't need to declare in the regular query, right?

    Also, I have another quick question. I have a form (a) with a subform ([img]/forums/images/smilies/cool.gif[/img]. I want to set a field of subform ([img]/forums/images/smilies/cool.gif[/img] as control in a txt field.

    Should I set: "[Forms]![a]!<!t>[b]![c]" Or "[Forms[![a]![subforms]!<!t>[b]![c]"

    Thank you so much.

    Regards

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross Tab (2000)

    Thank you so much, Hans.

Posting Permissions

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