Results 1 to 8 of 8
  1. #1
    Jaik is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Aug 2016
    Posts
    33

    If I filter data by In() I get only last day of month's data

    In the custom query I have fields Type, Date, Sale, Return.


    I filtered Type column by In (Material, Product, Service). However, If I use 'In' for filtering data I get only last date of month data from date column. (ex 1-31,2-28,3-31...etc).
    As the result, I got blank column for sale. Is there any other to filter Material, Product, and Service in Type column to receive data?

    Thank you

  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
    You'd have to use actual dates, with # delimiters. Using In() for dates seems pretty unusual. Typically one would use Between.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Maybe post the SQL code of your query so we can take a look. So you want to limit your data by choosing only records with a Type of Material, Product and Service? Is so why not just list the 3 values in the Criteria column?

  4. #4
    Jaik is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Aug 2016
    Posts
    33
    SELECT Detail.[Type], Detail.[Date], Detail.Return,
    Detail.Sale, Year(Now()) AS [Current Year], DatePart("q",[Date]) AS [Calendar Quarter],
    DatePart("yyyy",[ Date]) AS [Calendar Year], Format([Date],"qyyyy") AS [Quarter Year], Sum(Detail.Sale) AS [Total Sale],
    Sum(Detail.Return) AS [Total Return], Format([ Date],"myyyy") AS [Month Year]
    FROM Detail
    GROUP BY Detail.[Type], Detail.[Date], Detail.Return, Detail.Sale, Year(Now()), DatePart("q",[Date]), DatePart("yyyy",[Date]), Format([Date],"qyyyy"), Format([Date],"myyyy")
    HAVING (((Detail.[Type])="Purchase" Or (Detail.[Type])="Accrual" Or (Detail.[ Type])="Prepay"));
    This is the query and I need to have those custom date fields. Also I tried using Criteria, but it gives me same data as using 'In'

  5. #5
    Jaik is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Aug 2016
    Posts
    33
    Since I am filtering on values I can use Between. Is there other ways to filter out values? I used criteria to filter values, but It gave me same result as using 'In'.

  6. #6
    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 thought you were trying to use In() on a date field, my bad. Using In() or the Or won't make a difference, and it should be a WHERE clause instead of HAVING, but that won't affect the result you're getting. I suspect the grouping by various date formats is giving you the odd result. Can you attach the db here? Or at least a picture of what the raw data and the data returned by the query look like?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    always possible that Date and Type being reserved words could be causing the problem

  8. #8
    Jaik is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Aug 2016
    Posts
    33
    Thank you guys for support! I decided to delete all unnecessary values in the Type columns before data importation, and it worked.

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

Similar Threads

  1. Replies: 1
    Last Post: 03-22-2015, 02:21 PM
  2. Replies: 2
    Last Post: 02-12-2015, 05:14 AM
  3. Replies: 6
    Last Post: 05-05-2014, 10:51 PM
  4. Display data from month x-1 and x-2
    By donodarazao in forum Reports
    Replies: 5
    Last Post: 11-06-2010, 12:57 PM
  5. Tracking End of Month Data
    By spherearrow in forum Database Design
    Replies: 5
    Last Post: 09-13-2010, 10:55 AM

Tags for this Thread

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