Solved Filtering problem

[John Sh]

I have a filter on a table that includes the date, formatted as date/time. The controls are formatted general date.
The filter works if the first and last date are different but returns a blank screen if the dates are the same, even though the times are different.
How do I get around this?
I have tried using datevalue but it makes no difference
This is a screen shot of the filter string in msgbox.

 

[MarkK]

How have you tried using DateValue()?

This sounds like the result you would get if the date values in the table do not include times. What are the table values in the date_of_change field for #4/12/24#?

Try this in the immediate pane...
? cdbl(#4/12/24#)
Then try this...
? cdbl(#4/12/24 0:00:01#)
This will show you exactly how the date is stored in the table as a number.

For dates where the date portion is the same, values with a time are always greater than the value without a time. Changing the filter to start on the day before, selects all the dates that have not times.

 

[cheekybuddha]

I have a filter on a table that includes the date, formatted as date/time. The controls are formatted general date.
The filter works if the first and last date are different but returns a blank screen if the dates are the same, even though the times are different.
How do I get around this?
I have tried using datevalue but it makes no difference
This is a screen shot of the filter string in msgbox.

View attachment 123284

Do you have a space between the greater than symbol and the equals sign?

If so, I suggest you remove it.

 

[John Sh]

Do you have a space between the greater than symbol and the equals sign?

If so, I suggest you remove it.

No spaces.

 

[CJ_London]

Is 4/12 the 4th December or the 12th April

 

[John Sh]

Is 4/12 the 4th December or the 12th April

It is international standard date format so "dd/mm/yyyy"
Not that it would make any difference, as long as all my dates were of the same format.
John

 

[cheekybuddha]

It is international standard date format so "dd/mm/yyyy"

No, it's not. It's in SQL date format so it's "mm/dd/yyyy"

Not that it would make any difference, as long as all my dates were of the same format.

Dates passed to SQL must be in either US date format or an unambiguous date format like ISO: "yyyy-mm-dd"

How are you constructing your filter?

Please post the code you use.

 

[John Sh]

No, it's not. It's in SQL date format so it's "mm/dd/yyyy"


Dates passed to SQL must be in either US date format or an unambiguous date format like ISO: "yyyy-mm-dd"

How are you constructing your filter?

Please post the code you use.

Sorry but you're wrong. A date "28/02/2024" would imply 28 months to the year, by your reckoning.
Also the filter string is not used in SQL, it is passed as a string to me.Filter.

This code is in a separate form where the user can select the filter criteria.
fDate and lDate are a catchall if the user doesn't select a date.
The function "addAnd()" adds either "" or "AND" into the string as it is created.
The final filter string can be seen in post #1

Private Sub setFilter()
    Dim bDate As Date
    Dim aDate As Date
    Dim fDate As Date
    Dim lDate As Date
    TempVars!Filter = ""
    fDate = DateValue(dMin("date_of_change", "" & Me.RecordSource & ""))
    lDate = DateValue(DMax("date_of_change", "" & Me.RecordSource & ""))
    bDate = 0
    aDate = 0
    If Not IsNull(Me.cboDateFirst) Then bDate = Me.cboDateFirst
    If Not IsNull(Me.cboDateLast) Then aDate = Me.cboDateLast
    If Nz(Me.cboCollect, "") <> "" Then sFilter = addAnd(sFilter) & "[Collection]= '" & Me.cboCollect & "'"
    If Nz(Me.cboUser, "") <> "" Then sFilter = addAnd(sFilter) & "[user_Name]= '" & Me.cboUser & "'"
    If Nz(Me.cboField, "") > "" Then sFilter = addAnd(sFilter) & "[field_Name]= '" & Me.cboField & "'"
    If bDate > 0 And aDate > 0 Then sFilter = addAnd(sFilter) & "[date_of_change] between #" & bDate & "# AND  #" & aDate & "#"
    If bDate > 0 And aDate = 0 Then sFilter = addAnd(sFilter) & "[date_of_change] between #" & bDate & "# AND  #" & lDate & "#"
    If bDate = 0 And aDate > 0 Then sFilter = addAnd(sFilter) & "[date_of_change] between #" & fDate & "# AND  #" & aDate & "#"
    TempVars!Filter = sFilter
End Sub

This code is in the onload event of the filtered form

 If TempVars!Filter > "" Then
            Me.Filter = TempVars!Filter
            Me.FilterOn = True
            DoCmd.SetOrderBy "Date_of_change"
        End If

Below is a sample of the table. I have temporarily removed the time data.

 

[cheekybuddha]

Sorry but you're wrong. A date "28/02/2024" would imply 28 months to the year, by your reckoning.

Since this date is unambiguous, it will be parsed in SQL correctly as 28th Feb,

But not so with "11/02/2024" - that will be interpreted as 2nd Nov.

Indulge me.

Add a function in a standard module:

Function SQLDate( _
           vIn As Variant, _
           Optional blWrap As Boolean = True, _
           Optional delim As String = "#" _
         ) As String

  Const ISO_DATETIME As String = "yyyy\-mm\-dd hh:nn:ss"
  Dim strRet As String

  If IsDate(vIn) Then
    strRet = Format(vIn, ISO_DATETIME)
    If blWrap Then strRet = delim & strRet & delim
  ElseIf IsNull(vIn) Then
    strRet = "NULL"
  End If
  SQLDate = strRet

End Function

Then adjust your code:

' ...
    If bDate > 0 And aDate > 0 Then sFilter = addAnd(sFilter) & "[date_of_change] between " & SQLDate(bDate) & " AND " & SQLDate(aDate)
    If bDate > 0 And aDate = 0 Then sFilter = addAnd(sFilter) & "[date_of_change] between " & SQLDate(bDate) & " AND " & SQLDate(lDate)
    If bDate = 0 And aDate > 0 Then sFilter = addAnd(sFilter) & "[date_of_change] between " & SQLDate(fDate) & " AND " & SQLDate(aDate)
'...

See if it helps.

 

[John Sh]

Since this date is unambiguous, it will be parsed in SQL correctly as 28th Feb,

But not so with "11/02/2024" - that will be interpreted as 2nd Nov.

Indulge me.

See if it helps.

Indulged you are, and Yes it fixed the problem.
I only had to remove the blWrap clause as I finished up with "##" around the dates.
I'll remove both the optionals as they are not required.
Thank you for the code and the lesson. Much appreciated.
John

 

[June7]

More about international dates http://allenbrowne.com/ser-36.html

 

[cheekybuddha]

I only had to remove the blWrap clause as I finished up with "##" around the dates.
I'll remove both the optionals as they are not required.

Removing the blWrap clause loses you a major part of the function's utility: removing the necessity to fiddle around adding # delimiters in your SQL.

If you had copied the code I had posted and replaced your equivalent code you would not have ended up with doubled delimiters. Notice I removed the delimiters from your SQL.

As a result your code is easier and cleaner to write using the function; any time you have a date parameter you just wrap it in the function.

If you had a specific need not to auto-delimit your date you can just pass False to that parameter in the function:

?SQLDate(Now, False)
2026-02-20 08:11:53

The [optional] delim is useful if ever you need to create SQL for another RDBMS, eg SQLServer, MySQL or Postgres, which delimit dates using a single quote character rather than the # symbol:

?SQLDate(Now, , "'")
'2026-02-20 08:11:53'

However, you are welcome to use it as you please! The main thing is that you can see that the format of dates passed to SQL statements is independent of your local date format settings, even if our American friends don't have to worry just through happy coincidence!

 

[CJ_London]

It is international standard date format so "dd/mm/yyyy

The key word here is ‘format’. SQL users the underlying value ( what it is) not the formatted value (what you see), which is why you should never format fields at the table level.

Dates are actually stored as a form of double number, the part before the decimal point is the number of days since 31/12/1899 to express the date, the bit after the time as the time in seconds/86400 (the number of seconds in a day) - which is why you cannot express a calculated time value greater than 23:59:59

Use cdbl(datefileld) to see the actual value

 

[GPGeorge]

"The key word here is ‘format’. SQL uses the underlying value ( what it is) not the formatted value (what you see), which is why you should never format fields at the table level."

I know. My bias is showing.

 

[Ken Sheridan]

which is why you cannot express a calculated time value greater than 23:59:59

The following little function will return the result of an arithmetical expression on time durations as a string expression:

Public Function TimeElapsed(ByVal dtmTime As Date, strMinSec As String, _
            Optional ByVal blnShowdays As Boolean = False) As String

    ' Returns a date/time value as a duration
    ' in hours etc or days:hours etc if optional
    ' blnShowDays argument is True.
    ' Time format is determined by strMinSec argument,
    ' e.g. "nn" to show hours:minutes,
    ' "nn:ss" to show hours:minutes:seconds,
    ' "" to show hours only
    ' Call the fucntion, in a query for example, like this:
    ' SELECT EmployeeID,
    ' TimeElapsed(SUM(TimeEnd-TimeStart), "nn") As TotalTime
    ' FROM TimeLog
    ' GROUP BY EmployeeID;
    
    Dim lngDays As Long
    Dim strDays As String
    Dim strHours As String
    Dim IsNegative As Boolean
    
    ' if duration is a negative value then flag as IsNegative
    ' and convert to a positive value
    If dtmTime < 0 Then
        IsNegative = True
        dtmTime = Abs(dtmTime)
    End If
    
    ' get whole days
    lngDays = Int(dtmTime)
    strDays = CStr(lngDays)
    ' get hours
    strHours = Format(dtmTime, "hh")
    
    If blnShowdays Then
        TimeElapsed = lngDays & ":" & strHours & Format(dtmTime, ":" & strMinSec)
    Else
        TimeElapsed = Format((Val(strDays) * 24) + Val(strHours), "#,##0") & _
            Format(dtmTime, ":" & strMinSec)
    End If
    
    ' remove trailing colon if necessary
    If Right(TimeElapsed, 1) = ":" Then
        TimeElapsed = Left(TimeElapsed, Len(TimeElapsed) - 1)
    End If
    
    ' add minus sign if duration is a negatve value
    If IsNegative Then
        TimeElapsed = "-" & TimeElapsed
    End If
    
End Function

For example:
? TimeElapsed(#08:45# + #20:30#,"nn")
29:15

Further arithmetic cannot be done on the return value of course. It must be done on the original expression.