Filter Form By Date Criteria

[ahd2008]

I have a form that pop up on the sessions form. i use the below code to filter the sessions form by the strat date of the course but it seems it is not giving any result. I get only empty fields

Private Sub Toggle10_Click()
[Forms]![Enrollment].Filter = "sessions.[start date]=" & StartDate
[Forms]![Enrollment].FilterOn = True
End Sub

is there any problme with this code?
Please adivse

[ChipR]

If [start date] is a date field, then you must surround it by # signs.

[Forms]![Enrollment].Filter = "sessions.[start date] = #" & StartDate & "#"

[ahd2008]

thanks dear.

it works now but there is a prolbme i have three session whith the following start dates:

1-10/07/2009
2-17/07/2009
3-14/12/2009

I typed the second date and then the third in the StartDate to filter sessions form and it worked. but when i typed the first date 10/07/2009 it gave me empty fileds.

Do you know what is the problme?

Appreciate your help

[ChipR]

This is from another forum:

The default Access SQL date format, regardless of locale, is mm/dd/yyyy. If you use an invalid date format, it will 'helpfully' try to convert that to a valid date for you.

So, if you use '30/09/2008', it will recognize you're using dd/mm/yyyy, and convert it appropriately. However, a value like '10/01/2008' is a valid mm/dd/yyyy value to begin with, so it will not be converted, and stored incorrectly in case you actually meant dd/mm/yyyy....

The solution is to always convert your date values to a mm/dd/yyyy string prior to using them in Access SQL statements. You have to be a bit careful here, as using VBA date format masks may not work entirely as you'd expect on non-US locales (e.g. 'helpfully' interpreting "mm/dd/yyyy" as "the localized short date format"), so please test carefully using your particular Access/VBA version.

[missinglinq]

Access is really biased towards using the American date format of mm/dd/yyyy and you're not using that. It can cope with 17/07/2009 and 14/12/2009 because they can only be interpreted one way: July 17, 2009 and December 14, 2009, but 10/07/2009 can be interpreted as October 7, 2009 or July 10, 2009, and Access gets confused.

Allen Browne has an excellent paper on the subject and gives some suggestions, I believe, on handling the problem at

http://www.allenbrowne.com/ser-36.html

Linq ;0)>

[ahd2008]

Thanks gents good stuff to learn.

I'm glad to learn this and appreicate it