Solved Between operator (1 Viewer)

[zelarra821]

Good Morning.

I have a problem searching in a date range. To do this, I use this function that @MajP created for me:

Public Function GetBetweenFilter(Ctrl1 As Access.Control, ctrl2 As Access.Control, TheFieldName As String, Optional TheSql_Datatype As SQL_DataType = sdt_date) As String
  Dim val1 As String
  Dim val2 As String
  If Not IsNull(Ctrl1) And Not IsNull(ctrl2) Then
    val1 = CSql(Ctrl1, TheSql_Datatype)
    val2 = CSql(ctrl2, TheSql_Datatype)
    GetBetweenFilter = TheFieldName & " BETWEEN " & val1 & " AND " & val2
  End If
End Function

If I search between two dates, the first one does include it, but the second does not.

However, if I change between to this:

GetBetweenFilter = TheFieldName & ">=" & val1 & " AND " & TheFieldName & "<=" & val2

He does the same to me.

I have to say that I am searching between two dates which I know for a fact that there are records in the extremes in which I am searching.

Does anyone know how I can fix it?

Thank you.

 

[cheekybuddha]

Try this:

' ...
    val1 = CSql(Ctrl1, TheSql_Datatype)
    val2 = CSql(ctrl2 + 1, TheSql_Datatype)
    GetBetweenFilter = TheFieldName & " BETWEEN " & val1 & " AND " & val2
' ...

If that doesn't work, then please explain in more detail what you require, and give some examples with dates.

hth,

d

 

[arnelgp]

can you use another:

Public Function BuildBetweenFilter(p1 As Variant, p2 As Variant, fieldName As String, fieldDataType As DataTypeEnum) As String
    Dim sText As String
    If IsEmpty(p1) Or IsEmpty(p2) Then
        Exit Function
    End If
    sText = fieldName & " Between "
    Select Case fieldDataType
    Case DataTypeEnum.dbInteger, _
        DataTypeEnum.dbCurrency, _
        DataTypeEnum.dbDecimal, _
        DataTypeEnum.dbDouble, _
        DataTypeEnum.dbFloat, _
        DataTypeEnum.dbLong
        sText = sText & p1 & " And " & p2
    Case DataTypeEnum.dbDate
        sText = sText & Format$(p1, "\#mm\/dd\/yyyy\#") & " And " & Format$(p2, "\#mm\/dd\/yyyy\#")
    Case DataTypeEnum.dbText, DataTypeEnum.dbMemo
        sText = sText & "'" & Replace$(p1, "'", "''") & "' And '" & Replace$(p2, "'", "''") & "'"
        
    End Select
    BuildBetweenFilter = sText
End Function

 

[zelarra821]

Thank you so much guys.

With @cheekybuddha's option it works for me, it's simpler than @arnelgp's. Also, I don't know if @MajP will mean anything, because I'm using a module made by him.

 

[cheekybuddha]

To understand what is happening, you need to understand that dates contain a time portion, even though you don't always see it (due to how a date is formatted)

So if you want dates up to and including today, you might be tempted to write:
<= #2021-05-19#

But if you have a record with date #2021-05-19 08:00:00#, it won't match even though it happened today because it > #2021-05-19 00:00:00#

 

[zelarra821]

And what function can I use to remove that part of the hour?

 

[cheekybuddha]

First, you need to work out whether you really want/need to remove it.

The trick we used above (by adjusting the @MajP's function) was instead of saying:
<= #2021-05-19#

we said:
< #2021-05-20#

That includes all dates on #2021-05-19#, even if they have a time portion.

This way, the time portion remains and can be used in other scenarios.

 

[zelarra821]

I don't understand, @cheekybuddha, could you explain it in other words?

 

[cheekybuddha]

Will have to get back to you later. Others may jump in in the meanwhile.

 

[arnelgp]

If TheSql_Datatype = sdt_date Then
GetBetweenFilter = "DateValue(" & TheFieldName & ") BETWEEN " & val1 & " AND " & val2
Else
GetBetweenFilter = TheFieldName & " BETWEEN " & val1 & " AND " & val2
End If

 

[zelarra821]

Instead of touching the @MajP function, what I have done is put that function (DateValue) in the source query of the report, and it already shows me the records that it should. I don't know if @MajP will have something to add about it.

 

[arnelgp]

I don't know if @MajP will have something to add about it

you PM him.

 

[The_Doc_Man]

I don't understand, @cheekybuddha, could you explain it in other words

To understand dates/times in Access, you need to know that the DATE data type is what is called a TYPECAST, where you take another datatype and interpret it specially. As a simpler example, a Yes/No (or Boolean) data element is a BYTE holding the Y/N value. For dates & times, the DATE data element is actually a DOUBLE holding a "timeline" value. Both Windows and UNIX use the same concept - days since a reference date.

All dates in Access are actually just the number of days and fractions of a day since a reference point. In Access, the reference of day 0 is midnight Dec 30, 1899. (No, I don't know why Microsoft picked that date...) The FIX(date) is just the integer number of days. The fraction that you would get if you did ( Date - FIX(Date)) is the time field, where 0 = midnight and 0.50000 is Noon.

Today's date for Access is something more than 40,000 days since the reference date. You can add and subtract date/time fields easily because they are stored as a DOUBLE, which is just a number that can be programmatically manipulated just like any other number. (But it is not recommended to do multiplication or division of dates if you expect them to mean anything afterwards.) The difference between two dates is just the time-span between them. If the date values included times, the difference includes fractions of a day.

When you COMPARE two times, you can either literally compare them using the equals sign, or you can compare using the less-than or greater-than signs. Using the equals-sign, dates and times must match to the second. I would have said "fraction of a second" but Access isn't set up to use times with fractions of second without extra work.

To show dates and times literally in VBA equations, you use the octothorpe or pound-sign (#) to enclose dates & times, just like you would use double or single quotes to enclose text strings. So a validly formed date and time might look like this in VBA: #25-Dec-2020 09:35:00# - 9:35 AM on Christmas day 2020.

If you are looking for everything on or after Jan-1-2019 and on or before 15-Jan-2019, this would be written as Between #1-Jan-2019# And #15-Jan-2019 23:59:59# - or you could include one second later and use Between #1-Jan-2019# And #16-Jan-2019# - and you get away with that because if you don't put a time, #00:00:00# is assumed. That is, in VBA literal constants, a date without a time is midnight of that date. But if you used Now() to store a date, you got a date AND time. If you used Date() to store a date, you don't get a time.

Hope that helps you understand dates better.

 

[zelarra821]

Thanks so much for the explanation.

 

[Pat Hartman]

Check the function carefully. What is the result if the dates are 01/31/2021 and 12/31/2020? How about 12/25/2020 and 02/05/2021
When you use string data, 01/31/21 will be less than 12/31/2020 and 12/25/2020 will be greater than 02/05/2021. I ask because the function you are using converts the dates to strings and that would make the comparison operate on text rules rather than numeric rules.

 

[zelarra821]

Check the function carefully. What is the result if the dates are 01/31/2021 and 12/31/2020? How about 12/25/2020 and 02/05/2021
When you use string data, 01/31/21 will be less than 12/31/2020 and 12/25/2020 will be greater than 02/05/2021. I ask because the function you are using converts the dates to strings and that would make the comparison operate on text rules rather than numeric rules.

The function allows you to tell it the type of data, and I have already done that.

 

[Pat Hartman]

I was looking at the

Dim val1 As String
Dim val2 As String

You didn't include the cSQL() function so I couldn't see what it was doing.

 

[zelarra821]

Public Function CSql(ByVal Value As Variant, Optional Sql_Type As SQL_DataType = sdt_UseSubType) As String
    'Can be used when the Value is subtyped. For example you pass a declared variable
    Const SqlNull       As String = "Null"
    Dim Sql             As String
    
    'If the Sql_type is not passed then use the data type of the value
    If Trim(Value & " ") = "" Then
      CSql = SqlNull
    Else
         If Sql_Type = sdt_UseSubType Then
           Select Case varType(Value)
             Case vbEmpty, vbNull
               Sql_Type = sdt_Null
             Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal, vbByte
               Sql_Type = sdt_Numeric
             Case vbDate
               Sql_Type = sdt_date
             Case vbString
               Sql_Type = sdt_text
             Case vbBoolean
               Sql_Type = sdt_Boolean
             Case Else
               Sql_Type = sdt_Null
           End Select
         End If
         
        Select Case Sql_Type
           Case sdt_text
                 Sql = Replace(Trim(Value), "'", "''")
                 If Sql = "" Then
                     Sql = SqlNull
                 Else
                     Sql = " '" & Sql & "'"
                 End If
           Case sdt_Numeric
                 If IsNumeric(Value) Then
                  Sql = CStr(Value)
                 Else
                  MsgBox "Invalid data: " & Value & ". You specified a numeric data type", vbInformation, NombreBD
                  Exit Function
                 End If
           Case sdt_date
                 If IsDate(Value) Then
                     If Int(CDate(Value)) = Value Then
                        Sql = Format$(Value, "\#mm\/dd\/yyyy\#")
                     Else
                        Sql = Format$(Value, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
                     End If
                 Else
                   MsgBox "Invalid data: " & Value & ". You specified a date data type", vbInformation, NombreBD
                   Exit Function
                 End If
           Case sdt_Boolean
                 If Value = "True" Or Value = "False" Or Value = -1 Or Value = 0 Or Value = "Yes" Or Value = "No" Then
                   If Value = "True" Or Value = "Yes" Then Value = -1
                   If Value = "False" Or Value = "No" Then Value = 0
                   Sql = str(Value)
                 Else
                   MsgBox "Invalid data: " & Value & ". You specified a boolean data type", vbInformation, NombreBD
                   Exit Function
                 End If
            Case sdt_Null
              Sql = SqlNull
        End Select
          CSql = Trim(Sql)
   End If
    
End Function

 

[MajP]

If I understand the issue you are missing values at the endpoint. As people have stated this is because there are time values in your records. So if the string returns
SomeField Between #1/1/2021# AND #1/10/2021#
This will exclude records with 1/10/2021 hh:mm:ss where hh:mm:ss > 0

I am not sure you want to go with @cheekybuddha solution and add a day. That returns all values on 1/10/2021 but also possible to return records from 1/11/2021 that have zero time component(not sure if that is possible).

This filter is probably being used on a query. The Field being filtered should then be based on a calculated control in the query. Something like
YourFieldName: datevalue([originalFieldName]).
Now the field has no time value and no need to modify the code. That way you do not have to modify any code

The other solution is on your form itself. If the after update of the control for end date
me.enddate = int(me.enddate) + .0.99999

That would convert the enddate to 1/10/2021 11:59:59 PM
This would alleviate returning 1/11/2021 values

 

[Minty]

I am not sure you want to go with @cheekybuddha solution and add a day. That returns all values on 1/10/2021 but also possible to return records from 1/11/2021 that have zero time component(not sure if that is possible).

@MajP No, it's not possible to have a null time component that's why <#2021-05-21# works. The earliest time value would be 00:00 but it is still the 2021-05-21 date, so less than that value always works.

As a number

? CDbl (#2021-05-21 00:00#)
44337
? cdbl (#2021-05-20 23:59:59#)
44336.9999884259