filter subform between dates

[Ali29J]

Hi all

I am trying to use the following code in the onclick of a command button located on the master form to filter a subform to show records between the dates

Me!frmVisitReport.Form.Filter = "AppointmentDate BETWEEN #'" & Text48 & "'# AND #'" & Text50 & "'#"

Me!frmVisitReport.Form.FilterOn = True

at present i am getting syntax error but cant work out why?!?

Can anyone offer any guidance?

Ali

 

[Remou]

The delimiter for dates is hash (#), for text it is single quotes ('), so:
[tt]Me!frmVisitReport.Form.Filter = "AppointmentDate BETWEEN #" & Text48 & "# AND #" & Text50 & "#"[/tt]

 

[Ali29J]

great thanks for the help remou

 

[Ali29J]

Hi Remou

The filter works to certain extent, it finds the first date filters from that date, but it does nt seem to be considering the second date???

Ali

 

[PHV]

If AppointmentDate holds time values:
Me!frmVisitReport.Form.Filter = "Int(AppointmentDate) Between #" & Me!Text48 & "# And #" & Me!Text50 & "#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ali29J]

hmmm very strange

when i set date to filter for a day works great
when i set to filter for a week from beginning 19/02/07 works great
when set to week beginning 26/02/07, finds first date but continues past second date
same sort of thing with dates througout march?!?

very strange, can anyone help?

PHV tried your code same results as above

ALi

 

[PHV]

OK, use non ambiguous date format:
Me!frmVisitReport.Form.Filter = "Int(AppointmentDate) Between #" & Format(Me!Text48, "yyyy-mm-dd") & "# And #" & Format(Me!Text50, "yyyy-mm-dd") & "#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886