Filter by Selection on Date fields

[Eddie901]

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

[Allen Browne]

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?

[Eddie901]

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?

[Allen Browne]

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?