Date, WeekDay Functions (1 Viewer)

mjdemaris

Working on it...
Local time
Today, 05:40
Joined
Jul 9, 2015
Messages
424
Hello All,

Why is Today not equal to Today???

So, if I filter a form with a date field, which contains today's date, where "Filter = Date()", it returns the record with today's date. However, if I use a formula to find the last day of the week, and convert that to a date
Code:
dDate = (Date - WeekDay(Date) + 7)
, which gives me today's date, since it is Saturday, the filter returns no results.

Somehow, Today does not equal the computed value of Today...

I am trying to filter a form of items that are due by the end of each week, including the current day, with the start of the week beginning Sunday, which is the default. If an item was not completed the previous week, it will remain on the list until completion, so the filter would something like
Code:
"[DueDate] <= " & EndOfTheCurrentWeek & ""
.

Thoughts?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:40
Joined
Oct 29, 2018
Messages
21,358
Hi. What data type is dDate? Can you please show us the complete code? Thanks.
 

mjdemaris

Working on it...
Local time
Today, 05:40
Joined
Jul 9, 2015
Messages
424
Code:
Private Sub cmdCurrentWeek_Click()
'   filter for this week's PMs
    Dim dDate As Date
    Dim lToday As Long
    Dim sfilter As String
    Dim dToday As Date
    
    dDate = Date - Weekday(Date) + 7
    
'    lToday = CLng(Date)
'    dDate = CDate(lToday - CLng(Weekday(CDate(lToday)) + 7))
Debug.Print dDate
    sfilter = "[DueDate] = " & dDate & ""
'    sfilter = "[DueDate] <= Date()"
'    sfilter = "[DueDate] = " & dDate & ""
Debug.Print sfilter
    Me.Filter = sfilter
    Me.FilterOn = True
End Sub

This current filter does not work, even though the date returned by the formula is the same as the date returned by Date().
I also attempted to convert to the 5 digit long number, but that didn't really work either.
 

mjdemaris

Working on it...
Local time
Today, 05:40
Joined
Jul 9, 2015
Messages
424
Well, I discovered something that did help:
Code:
Const conJetDate = "\#mm\/dd\/yyyy\#"

sfilter = "[DueDate] <= " & Format(Date - Weekday(Date) + 7, conJetDate) & ""

By using a date format, which I had gotten from Allen Browne's website some time ago, and used in another database, Access deemed it worthy enough to filter for me.

I don't understand why this works, but it does.

If you have other suggestions I'd love to hear them.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:40
Joined
Oct 29, 2018
Messages
21,358
Well, I discovered something that did help:
Code:
Const conJetDate = "\#mm\/dd\/yyyy\#"

sfilter = "[DueDate] <= " & Format(Date - Weekday(Date) + 7, conJetDate) & ""
By using a date format, which I had gotten from Allen Browne's website some time ago, and used in another database, Access deemed it worthy enough to filter for me.

I don't understand why this works, but it does.

If you have other suggestions I'd love to hear them.
Hi. Glad to hear you found an answer. The filter property is a String, so using a string is/was required. Good luck with your project.
 

Users who are viewing this thread

Top Bottom