Hello.

I am using Access 2010 V 14.

I have a field named 'date' and am trying to get a list of all the unique values between 2 given dates. I was hoping someone could show my an sql which would prompt me for a start and end date and then list out all the dates in between. My date format is YYYYMMDD.

Thank you in advance and please let me know if my request is not clear or if more detail is needed.

Thanks again.

As a query criteria, you can use:

Between [enter start date] and [enter end date]

Hi Carl,

"Date" is a reserved word in Access. I recommend changing the name of your field to something more meaningful to avoid any problems in the future.

If you want a list of "distinct" dates between your date criteria, don't forget to use the DISTINCT keyword in your query.

Just my 2 cents...

Thanks all. Can you provide the query I would use - not sure how to write it. Regards.

Hi,


Without knowing your table structure, this is the basic syntax:

SELECT DISTINCT [FieldName]
FROM TableName
WHERE [FieldName] Between [Enter Start Date] And [Enter End Date]
ORDER BY [FieldName]

Just my 2 cents...

I'm not sure I understand the question. If you use a start date of May 1 2012 and an enddate of May 31 2012, do you want to see 31 rows, for May 1, May 2, Msy 3, ..., May 31? Or do you want to see one row for each date within that range stored in your table? Or do you want to see all of the records for that date range in your table, including multiple records on the same date?

Note that Date is a reserved word (for the builtin Date() function that returns today's date) and a really bad choice of fieldname; and that dates are not stored with any particular format, but as a number (a count of days and fractions of a day since #12/30/1899 00:00:00#). Is this in fact a Date/Time field, or a text field you're interpreting as a date?

Thanks. That was exactly what I was trying to achieve.