Subform filter between dates not working

[ali32j]

Hi All

I am trying to filter a subform based on between dates, i enter 2 dates, using a pop up calender to select the dates to filter to, and pressing a apply command button which runs the below code, only problem is, it does nt filter to what it should, not really sure why....both the text boxes are of the format "Short Date"

SUbform is based on a query from 2 tables, all with dates entered in short date format

Code is:
Me!frmVisitReport.Form.Filter = "AppointmentDate BETWEEN #" & Text48 & "# AND #" & Text50 & "#"
Me!frmVisitReport.Form.FilterOn = True
Me.frmVisitReport.Form.OrderByOn = True

cant even work out what its filter to.... it should return maybe 5 or 6 records, but instead is returning over a hundred which are not within the dates asked...


Appreciate if anyone can shed any light on this

Ali

 

[TheAceMan1]

How are ya ali32j . . .

Perhaps:

[blue]Me!frmVisitReport.Form.Filter = "AppointmentDate BETWEEN #" & Format(Text48, "mmm dd,yyyy") & "# AND #" & Format(Text50, "mmm dd,yyyy") & "#"
[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[PHV]

Use the ANSI standard like date format:
Me!frmVisitReport.Form.Filter = "AppointmentDate BETWEEN #" & Format(Text48, "yyyy-mm-dd") & "# AND #" & Format(Text50, "yyyy-mm-dd") & "#"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ali32j]

Hi PHV / Aceman

Thanks they worked great, could you explain why? not really sure in the details of differences between my code and yours...

Appreciate your help once again!!

Ali

 

[PHV]

It's a matter of ambiguity between dd/mm and mm/dd

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886