Filtering a form by dates that fall in the current week

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

I have a form with a control button that opens another
form when clicked. I want to filter the records of the
opened form by due dates that fall within the current week.

Thanks for any help.
 
My solution to this is a bit complicated, but it's worked
for me before:

On your first form, create two global variables:
WeekStart and WeekEnd.

Dim intWeekday As Integer

intWeekday = Weekday(Date)

Select Case intWeekday
Case 1 'Sunday
WEEK_START = Date
WEEK_END = DateAdd("d", 7 - intWeekday, Date)

Case 7 'Saturday
WEEK_START = DateAdd("d", -7 + intWeekday, Date)
WEEK_END = Date

Case Else
WEEK_START = DateAdd("d", 1 - intWeekday, Date)
WEEK_END = DateAdd("d", 7 - intWeekday, Date)
End Select
<<

On your command button's Click event, put this code:

Dim strSQL As String

'Open the second form
DoCmd.OpenForm "frmSomeFormName"

'Create the query behind the second form
strSQL = "SELECT tblTest.RentNo, tblTest.DateOut,
tblTest.Cust, tblTest.DueDate" & _
" FROM tblTest" & _
" WHERE (((tblTest.DueDate) Between #" &
WEEK_START & "# And #" & WEEK_END& "#));"

'Open the second form and filter it based on the above SQL
Forms!frmSomeFormName.RecordSource = strSQL
Forms!frmSomeFormName.Requery

This should work.

Hope this helps,
Crystal
 
Thanks Crystal. I'll give it a try.
-----Original Message-----
My solution to this is a bit complicated, but it's worked
for me before:

On your first form, create two global variables:
WeekStart and WeekEnd.


Dim intWeekday As Integer

intWeekday = Weekday(Date)

Select Case intWeekday
Case 1 'Sunday
WEEK_START = Date
WEEK_END = DateAdd("d", 7 - intWeekday, Date)

Case 7 'Saturday
WEEK_START = DateAdd("d", -7 + intWeekday, Date)
WEEK_END = Date

Case Else
WEEK_START = DateAdd("d", 1 - intWeekday, Date)
WEEK_END = DateAdd("d", 7 - intWeekday, Date)
End Select
<<

On your command button's Click event, put this code:

Dim strSQL As String

'Open the second form
DoCmd.OpenForm "frmSomeFormName"

'Create the query behind the second form
strSQL = "SELECT tblTest.RentNo, tblTest.DateOut,
tblTest.Cust, tblTest.DueDate" & _
" FROM tblTest" & _
" WHERE (((tblTest.DueDate) Between #" &
WEEK_START & "# And #" & WEEK_END& "#));"

'Open the second form and filter it based on the above SQL
Forms!frmSomeFormName.RecordSource = strSQL
Forms!frmSomeFormName.Requery

This should work.

Hope this helps,
Crystal
.
Click to expand...
 
Back
Top