Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter CrossTab (2K)

    Hi,

    I have a crosstab query that calculates certain values.
    The cross tab query is based on a normal query with fields of

    Expr1: Nz(Month([DateSent]),1)
    With the reference of [Forms]![FrmReport]![CboMonth]

    I also have an identical expression(obviously slightly altered) for year.

    When I run the crosstab the following error message is displayed.

    I need to filter the query dependent upon whether DateSent falls with the parameters set by CboMonth and CboYear from FrmReport.
    Attached Images Attached Images

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

    Re: Filter CrossTab (2K)

    Open the 'normal' query in design view.
    Select Query | Parameters...
    Enter [Forms]![FrmReport]![CboMonth] in the Parameter column, and select Integer as data type.
    Do the same for the year parameter.
    Click OK.
    Save the query.
    Try to open the crosstab query.
    If you get the same error message, open the crosstab query in design view and repeat the above steps to declare the parameters explicitly.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter CrossTab (2K)

    I have done both of these and I still get the same error message.

    The DateSent field has been placed in the original query.

    When I run the original query it works fines, albeit now I have to enter the Parameters of [Forms]![FrmReport]![CboMonth] & [CboYear] twice (presumably down to the parameters.

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

    Re: Filter CrossTab (2K)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter CrossTab (2K)

    As attached, I hope it holds enough info...
    Attached Files Attached Files

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

    Re: Filter CrossTab (2K)

    1) You reported that the parameter was Forms]![FrmReport]![CboMonth] but the name of the form is FrmReports, so you must obviously use that instead of FrmReport in the parameter declaration.

    2) You have included the field DateSent twice in QryActual-Date: once implicitly as part of QryPayments.*, once explicitly. This causes confusion. Remove the explicit instance of DateSent from QryActual-Date.

    The crosstab query should then work, with the parameters declared only in QryActual-Date.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter CrossTab (2K)

    I think I've been sitting in front of the screen too long!!
    Thank you for resolving that.

    I now have another query, why does the attached error display if you select a month without records? I have added the coding in the report to display a message if there is no data.
    Attached Images Attached Images

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

    Re: Filter CrossTab (2K)

    Please provide details and/or attach database.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter CrossTab (2K)

    When you go into FrmReports and select the relevant month, then click on the command button that previews the report, it is then that it comes up with the error.
    Presumably this is picking up on an anomaly within one of the expressions.

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

    Re: Filter CrossTab (2K)

    For most buttons on the form I get a message that the relevant query doesn't exist, so I cannot test.

Posting Permissions

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