Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cross Tab Query (A2k)

    With the following CrossTab, is it possible to specify a date range in the month field.
    My current format doesn't work as it should.

    TRANSFORM Sum(tblGranlyn.QtyPur) AS SumOfQtyPur
    SELECT [group] & " - " & [Item] AS ID, Count(tblGranlyn.QtyPur) AS [Total Of QtyPur]
    FROM tblGranlyn
    WHERE (((Format([PurDate],"mmm"))>=[StartDate] And (Format([PurDate],"mmm"))<=[EndDate]))
    GROUP BY [group] & " - " & [Item]
    PIVOT Format([PurDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

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

    Re: Cross Tab Query (A2k)

    Format returns a string, you can't compare that to a date. Try

    WHERE PurDate Between StartDate And EndDate

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross Tab Query (A2k)

    Hans.

    It didn't work but worked around it by creating a query from the table and then basing the CrossTab on the new query.
    I was able to add the date criteria to the new query.

    Thanks any way.

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross Tab Query (A2k)

    <P ID="edit" class=small>(Edited by D Willett on 26-Nov-03 11:34. )</P>Hans
    I've modified the query using a Date Selection form:

    Between [forms]![frmDates]![txtDateFrom] And [forms]![frmDates]![txtDateTo]

    The query is fine, but when is the best time to request the form to pass the values to the report ?

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

    Re: Cross Tab Query (A2k)

    If you set the record source of the report to the query, it will automatically pick up the parameters from the form.

    But perhaps I don't understand your question?

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross Tab Query (A2k)

    Yes I was a little vague with the question <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>.

    Scenario:

    Main form with command button to call the report.
    Reports source is the query with criteria set to date forms From - To fields.
    Date form needs to be called from somewhere.

    Not sure whether to call it from the print button or open event of the report.

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

    Re: Cross Tab Query (A2k)

    I would do it like this:
    - The command button on the main form opens the date selection form.
    - The "OK" command button on the date selection form opens the report.

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross Tab Query (A2k)

    Thanks Hans
    That was too easy.

Posting Permissions

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