Results 1 to 3 of 3
  1. #1
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352

    Query by Date Range Selection in Combo

    Hi all,

    I currently have a working query that pulls all data from the previous month (not prev 30 days but the month). It feeds other queries which do some other functions and then those feed a graph to display some data. Now it is necessary for this graph to not only show data from the previous month, but to also have the options to show data from the past 3 months, 6 months, and 12 months. I know I could do this by creating different queries for each time frame, I don't want to do that however, more work than necessary IMO.



    I have a combo with the options:

    "Previous Month"
    "Previous 3 months"
    "Previous 6 months"
    "Previous 12 months"

    And then I would like to appropriately refresh the graph based on their selection. As I stated, my query is currently set up to grab only the previous month's data:
    WHERE (((Year([EntryDate])*12+DatePart("m",[EntryDate]))=Year(Date())*12+DatePart("m",Date())-1))

    How can I modify this to appropriately respond to the combo box selection (i.e user selects "Previous 6 months" and the graph refreshes with data from the prev 6 months)? It would be great if I only had to make changes to this one query as I have many others and this one is the baseline query that obtains the date ranges of data to feed the others.

    Thanks for any help!

    EDIT: I do not know- this may be an involved process, and my MSA skills have deteriorated due to not being used. If it would be easier I could create separate queries for each date range, then would it be possible to have the other queries select which 'date range query' to use based on the cbo? Just spitballing.
    Last edited by nick404; 08-16-2016 at 11:01 AM. Reason: additional thought

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    on the form have 2 boxes, txtStartDate, txtEndDate
    when the user selects the combo item, set the 2 dates in the text boxes.
    then the query can just pull data from this.
    select * from table where [entryDate] between forms!frmMain!txtSTartDate and forms!frmMain!txtEndDate

    the AFTERUPDATE event of the combo would set the text boxes.
    Code:
    sub cboBox_afterupdate()
    dim m,y
    y = year(date)
    m = month(date)
    vDate = m & "/1/" & y
    
     select case cboBox
       case "Previous 3 months"
           txtStartDate = dateAdd("m",-3,vDate)
           txtEndDate = dateAdd("m",1,dateAdd("",-1,vDate))
    
       case "Previous 6 months"
           txtStartDate = dateAdd("m",-6,vDate)
           txtEndDate = dateAdd("m",1,dateAdd("",-1,vDate))
       case "Previous 12 months"
           txtStartDate = dateAdd("m",-12,vDate)
           txtEndDate = dateAdd("m",1,dateAdd("",-1,vDate))
    end sub

  3. #3
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    Thank you ranman,

    Worked perfectly, couldn't have asked for better!

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

Similar Threads

  1. Replies: 7
    Last Post: 03-30-2015, 10:04 AM
  2. Replies: 3
    Last Post: 04-20-2014, 08:39 AM
  3. Replies: 12
    Last Post: 01-23-2014, 03:24 PM
  4. Replies: 4
    Last Post: 05-26-2012, 09:29 AM
  5. Replies: 7
    Last Post: 04-30-2012, 10:52 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