Combobox For "This Week" or "This Month" etc to populate 2 text boxes for filter OR.

[MackMan]

Combobox For "This Week" or "This Month" etc to populate 2 text boxes for filter OR.

I'm asking for some -more- help with a problem I have and reach out to the guru's of this forum...

As per the attached I have a very simple set up (so I thought) of a combo box and two text boxes. I'd like to be able to either select "This Week", "this Month" OR "Last Month" etc, to populate the two text boxes (both with start date and end date respectively, in order to filter a form or report.

I know a few things to get started but I'm not sure of how the code will look.

for example, obviously, I'll need the Date(), find first and last for this month, but what about last week, and next month? not as easy as I thought it would be if I'm honest. The combo box simplifies filtering as opposed to typing in the dates.

The text boxes can be entered with dates on an ad-hoc basis.

Any help, as always, is greatly appreciated.

 

[plog]

Re: Combobox For "This Week" or "This Month" etc to populate 2 text boxes for filter

Here's a link to the functions you will need:

http://www.techonthenet.com/access/functions/

The ones you will need will be DateAdd, DateSerial and Weekday. Take your options one by one and work through them. Current month should be easiest, followed by next month, this week and next week.

Also, for each timeframe (Week, Month) be sure to test the edge cases (December/January, leap years). When you get stuck, post your code for each one here and we can help.

 

[MackMan]

Re: Combobox For "This Week" or "This Month" etc to populate 2 text boxes for filter

ok, So here's what I've got.. and all works perfectly.. however, I'll play around with last quarter, 1st quarter later..

But.. I have far too many Ifs and elses and End ifs... It looks untidy
Would "Case" be better for something like this?

 Private Sub cboRange_AfterUpdate()
 If Me.cboRange = "This Week" Then
Me.txtFrom = Date - Weekday(Date) + 1
Me.txtTo = Date - Weekday(Date) + 7
Else
 If Me.cboRange = "This Month" Then
Me.txtFrom = DateSerial(Year(Date), Month(Date), 1)
Me.txtTo = DateSerial(Year(Date), Month(Date) + 1, 0)
Else
 If Me.cboRange = "This Year" Then
Me.txtFrom = DateSerial(Year(Date), 1, 1)
Me.txtTo = DateSerial(Year(Date), 12, 31)
Else
  
 If Me.cboRange = "Last Week" Then
Me.txtFrom = Date - 7 - Weekday(Date) + 1
Me.txtTo = Date - 7 - Weekday(Date) + 7
Else
 If Me.cboRange = "Last Month" Then
Me.txtFrom = DateSerial(Year(Date), Month(Date) - 1, 1)
Me.txtTo = DateSerial(Year(Date), Month(Date), 0)
Else
 If Me.cboRange = "Last Year" Then
Me.txtFrom = DateSerial(Year(Date) - 1, 1, 1)
Me.txtTo = DateSerial(Year(Date) - 1, 12, 31)
Else
 End If
End If
End If
End If
End If
End If
  
 End Sub

 

[plog]

Re: Combobox For "This Week" or "This Month" etc to populate 2 text boxes for filter

Check out the Case Statement:

http://www.techonthenet.com/access/functions/advanced/case.php

It's essentially a cleaner way to implement a bunch of If/else statements.