By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,608 Members | 1,486 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,608 IT Pros & Developers. It's quick & easy.

Filter Form By Date Criteria

P: 68
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Toggle10_Click()
  2. [Forms]![Enrollment].Filter = "sessions.[start date]=" & StartDate
  3. [Forms]![Enrollment].FilterOn = True
  4. End Sub
is there any problme with this code?
Please adivse
Jul 23 '09 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 1,287
If [start date] is a date field, then you must surround it by # signs.
Expand|Select|Wrap|Line Numbers
  1. [Forms]![Enrollment].Filter = "sessions.[start date] = #" & StartDate & "#"
Jul 23 '09 #2

P: 68
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
Jul 23 '09 #3

Expert 100+
P: 1,287
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.
Jul 23 '09 #4

missinglinq
Expert 2.5K+
P: 3,532
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)>
Jul 23 '09 #5

P: 68
Thanks gents good stuff to learn.

I'm glad to learn this and appreicate it
Jul 24 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.