running query with date criteria... need assistance

  • Thread starter Thread starter Mr BT
  • Start date Start date
M

Mr BT

Hello, and thank you for the assistance.
I'm going to school for accounting, and we are now learning about MS Access,
specifically 2003. I have a text book "Microsoft Office Access 2003
CourseCard Edition" with "New Perspective in the top right corner, and
"Introductory" on the bottom left.
I guess I would mention this only to legitimize my claim... Anyways, I'm
following directions to "To enter the exact match condition, and then run
the query", with a StartDate Criteria of 07/01/2006. My understanding is the
format will be mm/dd/yyy as I had previously entered on a previous design
screen. The dates ARE appearing in that sequence.

The problem I'm having is with entering the date on my home computer. This
book is for US readers. As far as I can tell I have Office on my home
machine set up for US user even though I'm in Canada.
When I enter #07/01/2006# and query it, I get nothing resulting in the
query. If I enter #01/07/2006# I will get the results the book is showing me
(screenshot of their results) but my results do show the dates as being
#07/01/2006#

I'm not sure what I'm doing differently but I know I didn't have this kind
of trouble on the school machine. I'm hoping someone can shed a bit of light
on this.

Please reply with your answer here.

Thank you again.
 
ACCESS' default format for dates when using them in queries is the US format
("mm/dd/yyyy"), regardless of how your ACCESS is configured to display the
date (in your case, "dd/mm/yyyy"). You can use formats other than the US
format SO LONG AS the format is unambiguous (e.g., "dd-mmm-yyyy" or
"yyyy-mm-dd"). So use the US format in the queries' criteria.

If you're using a date field in your criterion:

WHERE DateField = Format(ADifferentDateField, "\#mm\/dd\/yyyy\#")
 
(in your case, "dd/mm/yyyy").

actually what I've said is the format is mm/dd/yyy, and the criteria is as
you said to enter as
\#mm\/dd\/yyyy\#

but when I query it is not picking up on the date that I'm entering as
07/01/2006 (which would be July 1st, 2006) with zero results...
when I enter the date 01/07/2006 (Jan 7th, 2006) it will result with the
query as shown in my book. Both in the book and the query results show the
date as 07/01/2006

sorry if that wasn't clear in my first post
 
What is your computer's Regional Settings for Date format? Are you
absolutely sure that the date you're seeing is showing as US format and not
Canadian format?
 
That was it, thank you very much
Ken Snell said:
What is your computer's Regional Settings for Date format? Are you
absolutely sure that the date you're seeing is showing as US format and
not Canadian format?
Click to expand...
 
Back
Top