Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2013
    Posts
    2

    Unanswered: i want to make a combo box that filters the record ; today,weekly, monthly

    i am creating a database program which intends to run in a network. to be access by many users at the same time. and i want to have a combo box that filters the records today,weekly and monthly. i have a date and time field "Date & Time". anyone can help me with this. thanks !

  2. #2
    Join Date
    Nov 2011
    Posts
    413

    Filter By Date

    Filter by Today
    = (Year([YourDateField[) = Year(Date()) And Month([YourDateField])=Month(Date()) And Day([YourDateField]) = Day(Date()))


    Filter by This Week
    =Year([YourDateField])=Year(Date()) And DatePart("ww",[YourDateField],0)=DatePart("ww",Date(),0))


    Filter This Month
    =(Year([YourDateField])=Year(Date()) And Month([YourDateField])=Month(Date()))
    Of course substitute your date field and any other requirements.


    HTH

  3. #3
    Join Date
    Oct 2013
    Posts
    2

    combo box

    hello. thanks for the reply. but can you send in the whole code of the combo box. thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    What id do is have a 3 column list box, with only one column visible. The three columns would be the description and the start & before / cutoff dates.
    When populating the list box set it to a value list.
    Set the list using vba
    Dim strValueList as string
    dim Tomorrow as string
    Tomorrow = format(datedadd("d",1,date()), "#yyyy/mm/dd#;")
    `use the date() function to get todays date as opposed to the now() function which gets the current date and time. Ive used dated add to set the end filter to be tomorrow, you could choose a different value
    StrValueList = "All;#1900/01/01#;" & tomorrow
    ` i tend to use ISO dates to avoid confusion between date formats
    `01 jan 1900 is the start for vba dates
    Strvaluelist = strvaluelist & "Today;" & format(date(),"#yyyy/mm/dd#;") & tomorrow
    `this defines the start and cut off date
    'the# symbol delimits a date literal
    ...add others as required
    'eg last seven days including today
    Strvaluelist = strvaluelist & "Last 7 days;" & format(dateadd("d",-7,date()),"#yyyy/mm/dd#;") & tomorrow
    if you want the last week or month then you are going to have to do a bit more work to define this week, last week, this month, last month and so on

    Then in the list box 'on click' event id set up the filter
    You need to find the code to pull the right row and columns
    Column(0) is the description
    1 is the start date
    2 is the before / cutoff date.
    Offhand i think its mylistbox.selected.column(index) but im not certain
    Lets assume it is
    With mylistbox.selected
    Me.filter = "mydatecolumn >= column(1) & " and mydatecolumn <" & .column(2)
    Me.filteron= true
    End with

    'one of thje reason youneed to use the cutoff date is that ACCESS stores date and times int he same variable when usign the datatype datetime. so if you want a sepcific days data then youneed to specify thge time band
    10 oct 2013 00:00:00 to 10 oct 2013 23:59:59 and use between
    OR
    10 oct 2013 >= date < 11 oct 2013. the implicitly they are the same but are easlier to code to.
    You will need to replace mylistbox and mydatecolumn with the names of your listbox and column
    As ever this is air code, it almost certainly has errors both in logic and typos. Its almost certainly includes vba property errors, but it should point you in the right direction
    Last edited by healdem; 10-10-13 at 05:33.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •