Results 1 to 7 of 7
  1. #1
    TubbyGrey's Avatar
    TubbyGrey is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    4

    Date Filtering problem

    Greetings,

    (Attachment uploaded)

    What I know about SQL, VB or Access for that matter can be placed in a thimble without fear of overflowing. Despite this, I’ve (somehow) been able to cobble together a database that is actually functional and serves its purpose well. With one problem.. Date Filtering



    Need some guru's brain juice. This is based on the Northwind.mdb provided with Access 2003. I’m having a great deal of difficulty in one of the Report in which I’ve tried to filter the date. I expected the results to be displayed by showing: Product followed by Customer Name, Qty. and Cost. Instead it is listing the Customer Name several times and not summing the Qty or Cost as I had hoped/expected.

    One Report that is filtered by date works great (CustomerExtended Filter)
    Another works fine without filtering by date (SummaryProductsReport)
    The problem one gets all boogered-up when trying to filter date (Copy of SummaryProductsReport)

    If someone can take a peek at the attachment and give me a hint of what I’m doing wrong I’d appreciate it.

    Thanks for your time.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I suspect you want to change the Group By on the date field in the query to Where.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    TubbyGrey's Avatar
    TubbyGrey is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    4
    pbaldy,

    Thanks for giving it a try. Changing the Sort didn't change the displayed results at all. Which is telling me that my attempt at filtering the date is placed in the wrong area.

    I'll toy with (a copy of) the one filtered report that works properly and try to see where I've gone astray.

    Thanks for being one of the many that do help.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, I didn't say to change the sort. Try this:

    SELECT DISTINCTROW [Customers Query].CompanyName, [Customers Query].ProductName, Sum([Customers Query].Quantity) AS [Sum Of Quantity], Sum([Customers Query].ExtendedPrice) AS [Sum Of ExtendedPrice]
    FROM [Customers Query], Orders, [Customers Query] AS [Customers Query_1], Orders AS Orders_1
    WHERE (((Orders.OrderDate) Between #9/1/2010# And #9/5/2010#))
    GROUP BY [Customers Query].CompanyName, [Customers Query].ProductName
    ORDER BY [Customers Query].ProductName, Sum([Customers Query].Quantity) DESC;

    It halves the number of records returned.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    TubbyGrey's Avatar
    TubbyGrey is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    4
    pbaldy,

    You didn't say Sort.. my mistake.. the WHERE/GROUP BY was in the Total row not Sort as it looked like at the time. (I've placed my readers where they belong now)

    I know I'm way-off on my filter efforts now because what should have halved my report results didn't change it by one iota. (your code was placed in the Record Source)

    I'm going to keep working on this.. when I can get a little closer to the results that should be displayed I'll post back.. one way or the other.

    Thanks for your time.

  6. #6
    TubbyGrey's Avatar
    TubbyGrey is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    4

    Solved

    The long and short of my filter trouble was using OrderDate from a Table.

    At the risk of over explaining.. this is how it looked when I (finally) hit on the right way by using (what I think is) an Expression.

    QUERY BUILDER
    FIELD: CompanyName | ProductName | Sum Of Quantity: Quantity | Sum Of ExtendedPrice: | [Orders].[OrderDate]
    TABLE: Customers Query | Customers Query | Customers Query | Customers Query |
    TOTAL: | Group By | Sum | Sum | Where
    SORT: | Ascending | Descending | |
    SHOW: {checked} | {checked} | {checked} | {checked} | {unchecked}
    CRITERIA: | Between #9/1/2010# And #9/10/2010#

    SQL VIEW
    SELECT DISTINCTROW [Customers Query].CompanyName, [Customers Query].ProductName, Sum([Customers Query].Quantity) AS [Sum Of Quantity], Sum([Customers Query].ExtendedPrice) AS [Sum Of ExtendedPrice]
    FROM [Customers Query]
    WHERE ((([Orders].[OrderDate]) Between #9/1/2010# And #9/10/2010#))
    GROUP BY [Customers Query].CompanyName, [Customers Query].ProductName
    ORDER BY [Customers Query].ProductName, Sum([Customers Query].Quantity) DESC;

    pbaldy, Thanks for your effort to help.. I appreciated your time.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it sorted out.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 09-27-2010, 03:10 PM
  2. Problem With Date
    By jlg759 in forum Programming
    Replies: 6
    Last Post: 07-02-2010, 12:40 PM
  3. Filtering results by date between two datefields
    By lakylekidd in forum Programming
    Replies: 9
    Last Post: 06-07-2010, 07:42 AM
  4. Date Problem
    By oldteddybear in forum Queries
    Replies: 1
    Last Post: 08-30-2009, 08:52 AM
  5. Problem filtering a report
    By mrk68 in forum Reports
    Replies: 1
    Last Post: 05-03-2009, 09:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums