• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 47
  • Last Modified:

MS Access How can I filter my time input datasheet by week with a data picker?

I have a project/task sample database attached. The form frmEmployeeWeeklyInput includes a datasheet of projects and a date picker. How can I have the drop down requery the datasheet below to just display projects for the week of the date selected?

Please note the date picker needs to be friendly with the employee picker above.

I.E.
Date selected:
Nov 30
The datasheet should requery and only display projects that have a date from Nov 26 to Dec 2 (using WEEKLYDATE).


See attached sample database.
TimeInputTest---Copy.accdb
0
DJPr0
Asked:
DJPr0
  • 3
  • 2
  • 2
1 Solution
 
PatHartmanCommented:
Use the Format() function to get the week and year from the selection date

Where Format( WEEKLYDATE, "ww/yyyy") = Format(Format(Forms!frmEmployeeWeeklyInput!SelectedDate, "ww/yyyy")
0
 
Gustav BrockCIOCommented:
You can add this code to your update event to filter the subform:

Private Sub Text5_AfterUpdate()

    Me.tblEmployeeweeklyinput.Form.Filter = "WeeklyDate Between #" & Format(DateAdd("d", 1 - Weekday(Me!Text5.Value), Me!Text5.Value), "yyyy\/mm\/dd") & "# and #" & Format(DateAdd("d", 7 - Weekday(Me!Text5.Value), Me!Text5.Value), "yyyy\/mm\/dd") & "#"
    Me.tblEmployeeweeklyinput.Form.FilterOn = True

End Sub

Open in new window

/gustav
0
 
DJPr0Author Commented:
Gustav,

It works if I click on the datasheet below after selecting a date - how can have the datasheet (tblEmployeeweeklyinput) update or requery (without having to click on the datasheet) after selecting the date like the drop down above (Select Employee)?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Gustav BrockCIOCommented:
Try using the OnChange event and the Text property:

Private Sub Text5_Change()

    If IsDate(Me!Text5.Text) Then
        Me.tblEmployeeweeklyinput.Form.Filter = "WeeklyDate Between #" & Format(DateAdd("d", 1 - Weekday(Me!Text5.Text), Me!Text5.Text), "yyyy\/mm\/dd") & "# And #" & Format(DateAdd("d", 7 - Weekday(Me!Text5.Text), Me!Text5.Text), "yyyy\/mm\/dd") & "#"
        Me.tblEmployeeweeklyinput.Form.FilterOn = True
    End If

End Sub

Open in new window

/gustav
0
 
DJPr0Author Commented:
Thanks Gustav!
0
 
PatHartmanCommented:
FYI,
The Change event fires for EVERY character typed.  The BeforeUpdate or AfterUpdate events are more appropriate if you are testing the completed entry.  The Change event and the .text property are used if you want to check character by character as the data is entered.  Since dates have no meaning until they are complete, then I would use a different event.

If you put a stop in the code you accepted, you'll see that it fires multiple times.  As it happens, there are probably no ill effects in this case but you should understand that events have specific uses and understand when to use which event.  

if you want to enter 1/19/2017, you will see that after 1/1, this code will say that the date is valid so perhaps there could be ill effects in the future.
0
 
Gustav BrockCIOCommented:
It will just filter for each valid date entered and only then. No big deal.

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now