Solved Filter subform by short time (1 Viewer)

[amir0914]

Hi all,
I have a field on subform that contains short time data, now how can I filter subform by typing time in text box on form? is it possible?

 

[pbaldy]

It's certainly possible, though you'd need to watch out for times that aren't what they appear. In other words, you may see 10:32 but it may actually be 10:32:12. Code would either set the Filter property of the subform or change its recordsource.

 

[arnelgp]

sample code:

Private Sub TextOnMainForm_AfterUpdate()
    Dim t As Date
    
    If IsDate(Me.TexOnMainForm) Then
        t = CDate(Me.TextOnMainForm)
        With Me.subformName.Form
            .Filter = "TimeValue([Time]) = #" & TimeValue(t) & "#"
            .FilterOn = True
        End With
    End If
End Sub

 

[pbaldy]

Not very efficient to apply a formula to every record in the table.

 

[arnelgp]

Not very efficient to apply a formula to every record in the table.

the field is Date/Time (only the date portion is not displayed, but it is there).
you can try to add:

today's date + the time
#1/1/1990# + same time as above.

say the time is #12:01#

when you filter by:
Filter = "[timeField] =#12:01#"

it will only show the record with today's date (hidden) + #12:01"

but when you use a function(), it will return both record!
not efficient but productive.

 

[pbaldy]

You assume the field may include a date component, which may or may not be true (yes, I know how date/time values are stored). You haven't accounted for the possibility of seconds in the stored value.

 

[arnelgp]

You assume the field may include a date componen

it's not an assumption.
it is based on my Test db. i tested it before i post.

 

[arnelgp]

possibility of seconds in the stored value

no i did not.
if that is the case (data entry operator can type also the second portion even if the format is "hh:nn", but also
the "second" portions will not show, but will be saved.

Private Sub TextOnMainForm_AfterUpdate()
    Dim t As Date
   
    If IsDate(Me.TexOnMainForm) Then
        t = CDate(Me.TextOnMainForm)
        With Me.subformName.Form
            .Filter = "Format$([Time], 'hh:nn') = '" & Format$(t, "hh:nn") & "'"
            .FilterOn = True
        End With
    End If
End Sub

)

 

[amir0914]

sample code:

Private Sub TextOnMainForm_AfterUpdate()
    Dim t As Date
   
    If IsDate(Me.TexOnMainForm) Then
        t = CDate(Me.TextOnMainForm)
        With Me.subformName.Form
            .Filter = "TimeValue([Time]) = #" & TimeValue(t) & "#"
            .FilterOn = True
        End With
    End If
End Sub

That's really great, thank you so much
Can it be used on change event of textbox? (not AfterUpdate)

 

[arnelgp]

yes you can, but little modification:

Private Sub TexOnMainForm_Change()
    Dim t As String
    Dim v As Variant
        
    t = Me.TexOnMainForm.Text & ""
    
    If Len(t) <> 0 Then
        v = Split(t & "", ":")
        
        If UBound(v) > 0 Then
            If Len(Trim$(v(1) & "")) > 0 Then
                t = v(0) & v(1)
            Else
                t = v(0)
            End If
        Else
            t = v(0)
        End If
        With Me.subFormName.Form
            .Filter = "Format$([Time], 'hhnnss') Like '" & t & "*'"
            .FilterOn = True
        End With

    Else
        Me.subFormName.Form.FilterOn = False
    End If
End Sub