Solved Filters between the start and end dates (1 Viewer)

Uldis007

New member
Local time
Today, 15:00
Joined
Nov 17, 2021
Messages
22
Hello!
I'm trying to create a filter that filters between the start and end dates, but there is a syntax error that I can't find.
The other filters work fine, but as I try to filter between dates it shows error 3075
Help please!

Here is my code




Code:
Private Sub btnSearchCase_Click()

Dim strFilter As String


strFilter = "1=1"


    If CaseSearchBox <> "" Then
      strFilter = strFilter & " AND [RemontaPieteikumaNumurs]=" & CaseSearchBox
    End If
    
    If CarRegNumberSearchBox <> "" Then
      strFilter = strFilter & " AND [ReģistrācijasNumurs]='" & CarRegNumberSearchBox & "'"
    End If
    
    If CarNumberSearchBox <> "" Then
      strFilter = strFilter & " AND [GarāžasNumurs]=" & CarNumberSearchBox
    End If
    
    If StatusSearchBox1 <> "" Then
        strFilter = strFilter & " AND [LastOfStatuss]='" & StatusSearchBox1 & "'"
    End If
    
'This is the part where I get stuck
    
    If IsDate(Me.StartDateSearchBox) And IsDate(Me.StartDateSearchBox) Then
        strFilter = strFilter & "  [PieteikumaDatums] BETWEEN #" & Me.StartDateSearchBox & "#   AND #" & Me.StartDateSearchBox & "#"
          
    End If
    
    Me.Filter = strFilter
    Me.FilterOn = True

End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:00
Joined
Oct 29, 2018
Messages
21,357
Do a Debug.Print strFilter to see the syntax problem.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:00
Joined
May 7, 2009
Messages
19,169
you add " And ":

If IsDate(Me.StartDateSearchBox) And IsDate(Me.StartDateSearchBox) Then
strFilter = strFilter & " And [PieteikumaDatums] BETWEEN #" & Me.StartDateSearchBox & "# AND #" & Me.StartDateSearchBox & "#"

End If
 

bob fitz

AWF VIP
Local time
Today, 13:00
Joined
May 23, 2011
Messages
4,717
I would think you need an END date:

If IsDate(Me.StartDateSearchBox) And IsDate(Me.ENDDateSearchBox) Then
strFilter = strFilter & " And [PieteikumaDatums] BETWEEN #" & Me.StartDateSearchBox & "# AND #" & Me.ENDDateSearchBox & "#"

End If
 

Uldis007

New member
Local time
Today, 15:00
Joined
Nov 17, 2021
Messages
22
you add " And ":

If IsDate(Me.StartDateSearchBox) And IsDate(Me.StartDateSearchBox) Then
strFilter = strFilter & " And [PieteikumaDatums] BETWEEN #" & Me.StartDateSearchBox & "# AND #" & Me.StartDateSearchBox & "#"

End If
I added "And" but unfortunately no change
1649338309136.png
 

Uldis007

New member
Local time
Today, 15:00
Joined
Nov 17, 2021
Messages
22
I would think you need an END date:

If IsDate(Me.StartDateSearchBox) And IsDate(Me.ENDDateSearchBox) Then
strFilter = strFilter & " And [PieteikumaDatums] BETWEEN #" & Me.StartDateSearchBox & "# AND #" & Me.ENDDateSearchBox & "#"

End If
sorry for the mistake. I corrected, but nothing changed
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:00
Joined
May 7, 2009
Messages
19,169
add some formatting:

If IsDate(Me.StartDateSearchBox) And IsDate(Me.ENDDateSearchBox) Then
strFilter = strFilter & " And [PieteikumaDatums] BETWEEN #" & Replace$(Me.StartDateSearchBox, ".", "/") & "# AND #" & Replace$(Me.ENDDateSearchBox, ".", "/") & "#"

End If
 

Eugene-LS

Registered User.
Local time
Today, 16:00
Joined
Dec 7, 2018
Messages
481
I corrected, but nothing changed
Try that way:
Code:
    If IsDate(Me.StartDateSearchBox) Or IsDate(Me.EndDateSearchBox) Then
        strFilter = strFilter & _
        " AND [PieteikumaDatums] BETWEEN " & _
            Format$(Nz(Me.StartDateSearchBox, 0), "\#mm\/dd\/yyyy\#") & _
        " And " & _
            Format$(Nz(Me.EndDateSearchBox, 999999), "\#mm\/dd\/yyyy\#")
            ' 999999 = 4637 year  :)
    End If
in this variant it is not necessary to enter both dates - the filter will be built even by one of them
 

cheekybuddha

AWF VIP
Local time
Today, 13:00
Joined
Jul 21, 2014
Messages
2,237
You need also to convert to US or ISO date format.

At the moment you are searching for 4th June.

Also start and end dates seem to be the same!!!
 

Uldis007

New member
Local time
Today, 15:00
Joined
Nov 17, 2021
Messages
22
add some formatting:

If IsDate(Me.StartDateSearchBox) And IsDate(Me.ENDDateSearchBox) Then
strFilter = strFilter & " And [PieteikumaDatums] BETWEEN #" & Replace$(Me.StartDateSearchBox, ".", "/") & "# AND #" & Replace$(Me.ENDDateSearchBox, ".", "/") & "#"

End If
The syntax error is no longer displayed, but the dates in the form are not filtered.

1649339639282.png
 

Uldis007

New member
Local time
Today, 15:00
Joined
Nov 17, 2021
Messages
22
Try that way:
Code:
    If IsDate(Me.StartDateSearchBox) Or IsDate(Me.EndDateSearchBox) Then
        strFilter = strFilter & _
        " AND [PieteikumaDatums] BETWEEN " & _
            Format$(Nz(Me.StartDateSearchBox, 0), "\#mm\/dd\/yyyy\#") & _
        " And " & _
            Format$(Nz(Me.EndDateSearchBox, 999999), "\#mm\/dd\/yyyy\#")
            ' 999999 = 4637 year  :)
    End If
in this variant it is not necessary to enter both dates - the filter will be built even by one of them
YES! Super big thanks, now it's work!
You saved my day!
 

Uldis007

New member
Local time
Today, 15:00
Joined
Nov 17, 2021
Messages
22
Many thanks to everyone who got involved in solving the problem!
You are the best!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:00
Joined
May 7, 2009
Messages
19,169
in the future, you don't want to get confused about your Date Locale setting,
like:

1/2/2022 (what date is this, january or february?)

create a function that will convert your date to US date format (on whatever locale):
Code:
'arnelgp
'from VBA developer handbook
'
#If VBA7 Then
Private Declare PtrSafe Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal LCID As Long, ByVal LCType As Long, lpData As Any, ByVal cchData As Integer) As Integer
Private Declare PtrSafe Function GetSystemDefaultLCID Lib "kernel32" () As Long
Private Declare PtrSafe Function GetUserDefaultLCID Lib "kernel32" () As Long
#Else
Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal LCID As Long, ByVal LCType As Long, lpData As Any, ByVal cchData As Integer) As Integer
Private Declare Function GetSystemDefaultLCID Lib "kernel32" () As Long
Private Declare Function GetUserDefaultLCID Lib "kernel32" () As Long
#End If

Private Const LOCALE_SSHORTDATE = &H1F      ' short date format string
Private Const LOCALE_SLONGDATE = &H20       ' long date format string

Public Function USDate(d) As String
    Dim s As String
    Dim v As Variant
    d = d & ""
    d = Replace(Replace(d, ".", "/"), "-", "/")
    v = Split(d, "/")
    s = StGetLocaleInfo(LOCALE_SSHORTDATE)
    'we'll just assume we are looking for "m" or "d" as the first letter
    If left(s, 1) = "m" Then
        'no worry, already in US date format
        If v(0) > 12 Then
            d = v(1) & "/" & v(0) & "/" & v(2)
        End If
    ElseIf left(s, 1) = "d" Then
        'british format?
        If v(1) > 12 Then
            d = v(1) & "/" & v(0) & "/" & v(2)
        End If
    End If
    USDate = "#" & d & "#"
End Function

'----------------------------------------------------------------------
' StGetLocaleInfo
'
'   Gets Locale (international) info about current config
'   See LOCALE constants at top of module for LCTYPE values
'----------------------------------------------------------------------
Private Function StGetLocaleInfo(LCType As Long, Optional fUserDefault As Boolean = True) As String
    Dim LCID As Long
    Dim stBuff As String * 255
   
    'Get current language ID
    If fUserDefault Then
        LCID = GetUserDefaultLCID()
    Else
        LCID = GetSystemDefaultLCID()
    End If
   
    'ask for the locale info
    If (GetLocaleInfo(LCID, LCType, ByVal stBuff, Len(stBuff)) > 0) Then
        StGetLocaleInfo = StFromSz(stBuff)
    End If

End Function

'------------------------------------------------------------
'   StFromSz
'
'    Find the first vbNullChar in a string, and return
'    everything prior to that character. Extremely
'    useful when combined with the Windows API function calls.
'------------------------------------------------------------
Private Function StFromSz(ByVal sz As String) As String
   
    Dim ich As Integer
   
    ich = InStr(sz, vbNullChar)
    Select Case ich
        ' It's best to put the most likely case first.
        Case Is > 1
            ' Found in the string, so return the portion
            ' up to the null character.
            StFromSz = left$(sz, ich - 1)
        Case 0
            ' Not found at all, so just
            ' return the original value.
            StFromSz = sz
        Case 1
            ' Found at the first position, so return an empty string.
            StFromSz = vbNullString
    End Select
End Function

?USDate(Date)
#4/7/2022#
 

Users who are viewing this thread

Top Bottom