Filter a record by -Today, This Week, This Month and This Year. (1 Viewer)

[mtn]

Hi,
I have a record of my staff members. I display this on a form using a list box. What I want to do is to be able to click on various buttons each with an SQL statement like the one below that will filter the data by Today, This Week, Last Week, This Month and This Year.

The code below works well for all the entries that I modified for today but I haven't been able to modify the code to filter the data for the other criterias listed above.

strWhereSql = "WHERE [Staff].[Entered On] = Date()"

Can anybody give me an asistance please?

Thanks.
SA.

 

[MarkK]

Here's a function that returns the date of the Sunday of the current week. It uses the VBA.Weekday() function. You could use similar logic to solve for the other dates you'll need to find.

Function GetThisWeekSunday(d1 as Date) as Date
  GetThisWeekSunday = d1 - Weekday(d1) + 1
End Function

Also, the first day of this month is ...

DateValue("01-" & Month(Date()) & "-" & Year(Date()))

 

[Brianwarnock]

Functions Month and Year easily cater for 2 of the options with building on Lagbolts function to look for between dates taking care of the two weekly ones.

Brian

 

[mtn]

I found away around it. Thanks for all your help. What I did was to have two hidden text box (txtFrom and txtTo) on my from. I have a function to get me the following das/period: Today, yesterday, This week, last week, this month, last month, this year, calendar year to date, and last year.

On my form I have buttons for each of those periods so when you click on any of them this way:

'Call the function you like
GetThisMonth

'Set my SQL statement
strStartSql = "SELECT ....e.t.c.

'Show only records updated this month
strWhereSql = "WHERE [Staff].[Entered On] BETWEEN [Forms]![frmContactsOne].[txtFrom] And [Forms]![frmContactsOne].[txtTo]"
strSql = strStartSql & strWhereSql

With that I get my listbox to filter the data for me for any period I want. I don't know if it's the best way but it'd doing exactly what I want.

 

[DCrake]

Have a look at this Sample database it should give you what you want.

 

[namliam]

You might find this link intresting:
http://www.access-programmers.co.uk/forums/showthread.php?p=943722#post943722