I have a table full of sales records, and among the fields are year, month, and day the represents the date of the sale record.

How can I create an SELECT statement that will only returned the records between specified dates?

SELECT * FROM records
WHERE year > 1999 AND month > 3 AND day > 12 AND
year < 2001 AND month < 6 AND day < 15

Won't work because dates like 2000/2/31 and 2000/7/28 would be considered invalid....

Please help... thanks ahead of time...

Try something like:

SELECT *
FROM TableName
WHERE (Day > 3 AND Day < 20) AND (Month > 2 AND Month < 12) AND (Year > 2000 AND Year < 2002)

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

I don't think that would work since the and's with bracket ultimately result in the same as without brackets...

That call would still reject valid dates like 2000/2/21 since Day < 20 is part of the WHERE AND statement...

Well, it worked when I tried it out with a little test table I set up in Access.

The table was as follows:

RecID - Autonumber field
Text - Text field in which I just type nonsense
Day - Number field, contained the following values: 1, 2, 8, 10
Month - Number field, contained the following values: 3, 5, 12, 5
Year - Number field, contained the following values: 1999, 2000, 2001, 2002

From your first post, I,m assumung that the date is split into separate fields and just held as number values. So if you then want to compare this with a date in the format 20/02/2000 you would first have to split this up into the separate Day, Month and Year values prior to making the comparison, something like:

<%
varStartDate = CDate("20/02/2002"
varStartDay = DatePart("d", varStartDate)
varStartMonth = DatePart("m", varStartDate)
varStartYear = DatePart("yyyy", varStartDate)
%>

Then do something similar for the end date of the range, then make a numeric comparison with the values in the database.

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo