Results 1 to 7 of 7
  1. #1
    wcook101 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    56

    Is there an easy way to open a report and pull only data based the current month?

    For example I want to see all the stuff I bought this month. I built a report and I know how to get current month info using a couple of techniques but none of them are good for lazy people like me. I don't want to be asked everytime I run the report what the current month is... I want the report to just open with all the data from >=01/01/2020 and so on and then on 02/01/2020 it would start opening only Feb data. Also, I don't want the data from everything I have bought within the time frame of a month, because that would obviously pull data from the month before.
    Anybody have any ideas? Someone has to have done this before.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Let's assume the recordsource for the report has a field named PurchaseDate.
    Add a WHERE clause to the recordsource
    WHERE (Month(PurchaseDate) = Month(Date()) and Year(PurchaseDate) = Year(Date()))

  3. #3
    wcook101 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    56
    The Report is based off a Query named DealsQ In that Query there is a field called Date/Time from the Deals Table "DealsT"
    So I put the following in the record source for the report. Im guessing Im off on the syntax. I Tried a variety of statements. I tried a Select approach. Basically nothing worked. So I just added what you said to to what was already in my record source for the report. Like so..
    DealsQ Where (Month(Date/Time) = Month(Date()) and Year(Date/Time) = Year(Date()))
    I get an error message saying no record source.

  4. #4
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Bad naming, you shouldn't use spaces or special characters in field/table/form... names, wrap the field name (Date/Time) in square brackets: DealsQ Where (Month([Date/Time]) = Month(Date()) and Year([Date/Time]) = Year(Date()))

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Now knowing the object names along with Vlad's advice for the brackets, try this for the report recordsource:
    Select * From DealsQ Where (Month([Date/Time]) = Month(Date()) and Year([Date/Time]) = Year(Date()))

  6. #6
    wcook101 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    56
    Pretty cool. It works like a charm. Now im going to have to break it down and figure out why it works. Just looking at it as is, makes no sense. To think I had it and it would have worked if it werent for those damn /'s
    I actually was in a class where the instructor used date/time as a field and he never mentioned it could cause errors.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I actually was in a class where the instructor used date/time as a field and he never mentioned it could cause errors.
    I'm amazed by how many times we see such things being taught/used by instructors. In another current post in this forum, an exam question uses the word Name as a table field in a query. Who's teaching the teachers?? Along that line, what you've been given as a solution pertains to what is known as filtering the record source.
    Just looking at it as is, makes no sense.
    If you're a computer, you're trying to divide Date by Time (Date/Time). Never going to work.

    FYI
    Naming conventions
    - http://access.mvps.org/access/general/gen0012.htm
    - https://www.access-programmers.co.uk...d.php?t=225837
    - http://allenbrowne.com/AppIssueBadWord.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. combo box in form to pull report based on month
    By CRobledo in forum Programming
    Replies: 3
    Last Post: 12-07-2016, 11:49 AM
  2. Replies: 2
    Last Post: 02-12-2015, 05:14 AM
  3. Replies: 1
    Last Post: 10-28-2014, 06:36 AM
  4. Replies: 9
    Last Post: 09-17-2013, 11:08 PM
  5. Replies: 2
    Last Post: 04-02-2013, 02:24 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