What's the syntax for a WHERE in a date/time field?

[Megatron-Lives]

I am able to INSERT values into the date/time field as text, so why do I get a data type mismatch when I try to limit my records in a query with a WHERE as text?

example

INSERT INTO tblDates VALUES (01/01/01)
enters: 1/1/1899 (god knows why)

so i try

INSERT INTO tblDates VALUES (01/01/2001)
and that enters: 12:00:43 AM (again, god knows why)

so I try

INSERT INTO tblDates VALUES ('01/01/01')
EUREKA!! ok, so I have to insert the date as text

So then if I want to query for a date:

SELECT ScheduleDate FROM tblDates WHERE ScheduleDate='01/01/01'

But that nets me a Data Type Mismatch.

So, if the db will accept an INSERT as text, why can't I use text in my WHERE? What am I supposed to use? Without the "" it searches for a time of 12:00:43, not a date of 01/01/01

 

[Megatron-Lives]

ok, my brother brushed off the access cobwebs and remembered that i had to offset the date with #

SELECT ScheduleDate FROM tblDates WHERE ScheduleDate=#01/01/01#