Filter by Day of the Week

[SteveC24]

Hello,

I've been working on this all afternoon, and it is still foxing me! Can anyone help? This is my situation:

I have a form that lists events that occur at least once a week. (some events occur just once, others occur 3 days a week, others occur every day).
I would like the form to display those events that are going on TODAY.

The events are entered, and the days they occur selected using tick-boxes on another form.

I have got this far on the form that lists TODAYS events:

Me.Filter = "Sunday=True"
Me.FilterOn = True

I need to replace that 'Sunday' with whatever TODAY happens to be. I have a field on the form that contains todays day, that works, but I CANNOT figure out how to combine the two...can anyone help?

Thanks!

 

[charlesandrews]

Steve,

If your form is run off a query, then you could put date() in the search criteria in the date field. This way, the query will only show any activity from today. If your button is referring to that query then hey presto!!!

Hope that helps!!!

 

[SteveC24]

I think this is the problem I am facing. I don't want to work by DATES, I want to work by DAYS.

The Date() function thingy will return todays date as 12/09/04, I need it to return and work with just Sunday. It is irrelevant what the date is for this database, all I want it to work with is the day.

 

[charlesandrews]

Steve,

If you manually type in the day on your table, then try the attachment I ave made up for you. Notice how I have put the word Sunday in the query?

Hope that helps m8!!!

 

[SteveC24]

Yep, that works, and would be fine, but if possible I would like to stick with using checkboxes to select the days.

Also, I would need it to work with multiple days for each event.

 

[charlesandrews]

Could u send me a copy of what you have so far?

 

[SteveC24]

Certainly can...this one doesn't have a query in at all...

 

[Rich]

Look up the Weekday function

 

[charlesandrews]

Steve,

What I have done is created 3 querys and three forms.

On your recurring form, when the user clicks everyday, the form daily event is linked to on-click. When the Daily event form comes up, if they add new record it willl assign the days with the ticks.

Hope that helps

 

[Rich]

You don't need a separate field to identify the day from a valid date.
Either the Weekday, Day, functions or Format ([YourDate],"dddd") etc in a query/filter will do

 

[SteveC24]

OK - thank you people

Rich - thanks for that...will try and remember it too!

Charles - I am not totally sure of what you have achieved? The form frmRecurring doesn't only list the items that are happening today? That was what I was after?

 

[raskew]

1) Seven check-boxes named Chk1 through Chk7, labeled "Sunday" through "Saturday".

2) The AfterUpdate event for each check-box =CheckEm()

3) CheckEm scrolls through each of the check boxes and builds an In() statement, then reconstructs the record-source of the subform containing
the data.

'*******************************************
'Purpose:   Scroll through series of check
'           boxes and perform specified
'           action
'Coded by:  raskew
'*******************************************
Dim i       As Integer
Dim strHold As String
Dim strLbl  As String
Dim strSQL  As String

strSQL = "SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Format([OrderDate],'dddd') AS Expr1" _
      & " FROM Orders"

    strHold = ""
    For i = 1 To 7
       strLbl = "Chk" & Format(i)
       If Me(strLbl) = True Then
          strHold = strHold & i & ","
       End If
    Next i
    
    If Len(strHold) > 0 Then
       strHold = Left(strHold, Len(strHold) - 1)
       strSQL = strSQL & " WHERE (((Weekday([OrderDate])) In(" & strHold & ")))"
    End If
    
    strSQL = strSQL & " ORDER By Orders.OrderID;"
    
    With Me.sfrmOrdersByDay.Form
       .RecordSource = strSQL
       .Requery
    End With

End Function

I've attached a small A97 example. Note that this is linked to Northwind's Orders table. If yours is in the normal location, it should link without problem otherwise it'll need to be adjusted.