Results 1 to 5 of 5
  1. #1
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119

    How to filter a report data per year and month?

    Hi guys, I have design a report based on a query. The report groups data per month and date.
    In order the make things very simple for users, i designed a form and placed 3 comboxbox (CboYear, CboMonth, CboProduct) then 2 buttons (CmdViewReport, CmdClear).

    Now i want to filter the report based based on the criteria that selected from the combobox.

    I have managed to make the month and the product part work. But can't get the year part to work.

    I have attached a demo access database file so that you have a look at what i did.

    demo.zipPlease can someone help?

    Thanks in advance.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You need 2 more text boxes: txtStartDate, txtEndDate.
    they can be invisible if needed.
    fill in these 2 boxes after update of cboMo or cboYr....
    Code:
    Sub cboYr_afterUpdate()
      FillDates
    end sub
    
    sub cboMo_afterUpdate()
       FillDates
    end sub
    
    sub FillDates()
    txtStartDate= cboMo & "/1/" & cboYr
    txtEndDate= DateAdd("d",-1,dateAdd("m",1,txtStartDate))
    End sub
    the query uses these text boxes as criteria.

  3. #3
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    Hi Ranman 256,
    Sorry for the late reply. I try you code and got a "type mismatch error" on
    Code:
    txtEndDate= DateAdd("d",-1,dateAdd("m",1,txtStartDate))
    after I update cboYear or CboMonth.
    And again even when I reset the debug mode, the value of txtEndDate is 5/31/2001 when I select 2017 from cboYear and May from CboMonth.
    Thank you

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I made a few changes, namely to the tables and to the form.
    Attached Files Attached Files

  5. #5
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119
    Works like a charm.
    Thanks a lot.

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

Similar Threads

  1. Month/Year Filter on Continuous Form
    By doyouwantabedbath in forum Forms
    Replies: 1
    Last Post: 03-17-2016, 01:47 PM
  2. Replies: 4
    Last Post: 12-13-2013, 01:10 PM
  3. Filter Form records By Month Year
    By maxmaggot in forum Forms
    Replies: 4
    Last Post: 04-05-2013, 06:34 PM
  4. Query / report per month and year
    By ripper in forum Queries
    Replies: 2
    Last Post: 10-24-2011, 07:51 AM
  5. Replies: 1
    Last Post: 04-12-2011, 06:45 PM

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