sorting specific time(date) frames within a table

[Guest]

good evening,

Here is something I am trying to do, but can't seem to get a handle on. I'm
am hoping for some guidance and direction.

We have a table called Projects. In it we keep track of hours worked on all
projects and hours worked by employees on those projects.

I have a listbox that lists these timeframes: Today, thisweek, thismonth,
last week, lastmonth, custom.

The listbox is on a form and I want the data to show up on the subform.

I want to be able to click on THISWEEK and have all the records for this
week to show up. OR LASTMONTH and have all of last months records to show
up.

I would also like to be able to create a "CUSTOM" search. All the records
between this date and that date.

I understand the logic behind this, but not how to get it done.

THISWEEK = If today is thursday then all dates from today minus 4, meaning
go back 4 days and show all those records to present. Wednesday would be
minus 3, etc.
LASTMONTH = todays's date, extract the month, subtract 1, show all the
records for this month minus 1 or last month.

Is this sort of logic correct? Am I thinking in the right direction?
If anyone out there could get me started it would be a huge help.
Thanks so much.

Lisa B.

 

[Steve Schapel]

Lisa,

One possible approach here would be to adjust the Record Source of the
subform, according to the selection in the listbox. Something vaguely
along these lines...

Private Sub YourListbox_AfterUpdate()
Dim BaseSQL As String
Dim CritSQL As String
BaseSQL = "SELECT * FROM YourQuery WHERE "
Select Case Me.YourListbox
Case "Today"
CritSQL = "YourDateField = Date()"
Case "This Week"
CritSQL = "YourDateField >= (Date() - Weekday(Date(),2))"
Case ...

Case "Custom"
CritSQL = "YourDateField Between " & CLng(Me.DateFrom) & " And
" & CLng(Me.DateTo)
End Select
Me.YourSubform.Form.RecordSource = BaseSQL & CritSQL
End Sub

 

[Guest]

Good Morning Steve,

Thank you for the speedy reply. This place works so great.

What you have explained is a bit over my head. Is it possible for you to
break it down alittle? Where would I begin with what you wrote? Put it
where?

Or is there a simpler way to go about what I'm trying to do?
I live in rural Maine and have no opportunity to take Access classes, so I'm
learning by trial and error and this bulletin board!

Thank you again for your time.

LisaB.

 

[Steve Schapel]

Lisa

My suggestion relates to writing a VBA procedure to run on the After
Update event of your listbox. I can't think of an easier way to do it.
In design view of your form, you would go to the property sheet for
the listbox, find the After Update event property, select Event
Procedure, click the littly ellipsis (...) button to the right to access
the VB Editor window, and then type your code in there.

My example code relies on building a SQL string which is then assigned
as the record source of the subform. The SQL string consists of 2
parts, the part that is common to all options (BaseSQL) and the part
that varies according to your listbox selection (CritSQL). You have
given us very little to go on, so I had to use generic names in the
sample code. YourQuery refers to the name of the query that the subform
is based on without the date selection. YourDateField refers to the
name of the field your date selection criteria will be applied to.
YourSubform refers to the name of your subform. DateFrom and DateTo
refer to the names of the unbound textboxes on the form where you enter
the beginning and ending dates for the Custom date range. If you are
not familiar with the Select Case construct in VBA, see if you can look
it up in Help. Have a go, and then post back if you need further help.