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

Filter by Selection on Date fields

P: n/a
I have a date field in a table formatted as a "Medium Date", e.g.,
29-Mar-06. When I filter by that selection it's okay when the date is
after the 13th of the month, but for the 12th or below it transposes
the month with the day, so if I filter by 1-Feb-06, I get those records
from 2-Jan-06. Anything I can do about this?

Thanks for any help.

Rachel Bourne

Mar 29 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
See:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

The article explains how to avoid the 3 cases where Access misunderstands
your non-American dates.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Eddie901" <ed******@gmail.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...
I have a date field in a table formatted as a "Medium Date", e.g.,
29-Mar-06. When I filter by that selection it's okay when the date is
after the 13th of the month, but for the 12th or below it transposes
the month with the day, so if I filter by 1-Feb-06, I get those records
from 2-Jan-06. Anything I can do about this?

Mar 29 '06 #2

P: n/a
Thanks for this information, Allen. Having read it, I am still confused
as to what I have to do to solve my problem, however. Or maybe I just
have to live with it...

Allen Browne wrote:
See:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

The article explains how to avoid the 3 cases where Access misunderstands
your non-American dates.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Eddie901" <ed******@gmail.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...
I have a date field in a table formatted as a "Medium Date", e.g.,
29-Mar-06. When I filter by that selection it's okay when the date is
after the 13th of the month, but for the 12th or below it transposes
the month with the day, so if I filter by 1-Feb-06, I get those records
from 2-Jan-06. Anything I can do about this?


Mar 30 '06 #3

P: n/a
The format JET expects for a literal date in a SQL statement is mm/dd/yyyy,
delimited with #.

When you create a filter string, you need to use that format.

If you copy the SQLDate() function from the article, you can add the date to
your filter string like this:
strWhere = "[MyDateField] = " & SQLDate(Me.MyTextBox)

Or, you can use the Format() function directly if you prefer:
strWhere = "[MyDateField] = " & Format(Me.MyTextBox, "\#mm\/dd\/yyyy\#")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Eddie901" <ed******@gmail.com> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...
Thanks for this information, Allen. Having read it, I am still confused
as to what I have to do to solve my problem, however. Or maybe I just
have to live with it...

Allen Browne wrote:
See:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

The article explains how to avoid the 3 cases where Access misunderstands
your non-American dates.

"Eddie901" <ed******@gmail.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...
>I have a date field in a table formatted as a "Medium Date", e.g.,
> 29-Mar-06. When I filter by that selection it's okay when the date is
> after the 13th of the month, but for the 12th or below it transposes
> the month with the day, so if I filter by 1-Feb-06, I get those records
> from 2-Jan-06. Anything I can do about this?

Mar 30 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.