How to apply filter on a sub-form by pressing a button based on date values from two textboxes

0
votes

I am trying to built a VBA code that somehow apply a filter that only shows records where the field [Date] value comes between from & To dates(User input). below is my Form View.

Form View

Object Names are as below

Set Range Button = btnSetRange, From TextBox = txtFromDate; To TextBox = txtToDate; Subform Name= ShipmentHistoryDataSheet; Subform-Source Object = Forms![Shipment-History subform]. Shipment-History subform Record Source is a select query from Table "Shipment-History".

I have tried to write the code by myself but didn't worked out well as I'm not an expert in access or coding. below is the code that i wrote, But not working as expected:P

Private Sub btnSetRange_Click()

        Dim subform2 As Form

        Set subform2 = Me!ShipmentHistoryDataSheet.Form

        subform2.Filter = "[Date]>" & Me.txtFromDate And "[Date]<" & Me.txtToDate

        subform2.FilterOn = True
        subform2.Requery

End Sub

Can anybody help?

1
ms-accessvbams-access-2013

1 Answers

0
votes

Try with specific formatting of the string expressions for the date values:

Private Sub btnSetRange_Click()

    Dim subform2 As Form

    Set subform2 = Me!ShipmentHistoryDataSheet.Form

    subform2.Filter = "[Date] >= #" & Format(Me.txtFromDate, "yyyy\/mm\/dd") & "# And [Date] < #" & Format(Me.txtToDate, "yyyy\/mm\/dd") & "#"
    subform2.FilterOn = True

    ' subform2.Requery  ' should not be needed.

End Sub