Select consecutive dates in a query

[Keith Hawes]

I have a query where the first column contains dates.
I wish to find consecutive dates stating with a date I choose.
IE [date] + the following 5 consecutive dates.
If are choose 16/5/08 I would like to see rows for 15,16,17,18,19/05/08
Is this possible if so what formula do I use.

Thanks
Keith

 

[namliam]

What happens if 17 is not there? Do you still want to see 19?

 

[redneckgeek]

DOH!!! Missed the word "consecutive" in your original post. Disregard...
Try something like this:

SELECT TOP 5 your_date
FROM your_table
WHERE your_date>=[ENTER BEGIN DATE]
ORDER BY your_date;

This way, it doesn't matter whether or not the next 5 dates are consecutive.

 

[khawar]

Achieved it through a mix of vba and sql
Download the attached sample

 

[Keith Hawes]

What happens if 17 is not there? Do you still want to see 19?

Thank you Mailman. Yes I do want to see 19 if 17 is missing.
Regards
Keith

 

[Keith Hawes]

Achieved it through a mix of vba and sql
Download the attached sample

Thank you Khawar.
I copied your Form and Query in to my Database and then altered my table name to Table1. I also added the Number column as you had in your table, and made my date column XDate.
I get a result of 28 dates, some in FEB,March and April. The table runs from the 1-Feb-2008 to 26-June-2008. I used the same start date as you but got more than 5 dates all random.
Regards Keith

 

[Keith Hawes]

DOH!!! Missed the word "consecutive" in your original post. Disregard...
Try something like this:

SELECT TOP 5 your_date
FROM your_table
WHERE your_date>=[ENTER BEGIN DATE]
ORDER BY your_date;

This way, it doesn't matter whether or not the next 5 dates are consecutive.

Thank You Redneckgeek
I have only used Queries using the criteria box. Where do I put your code.
Regards
Keith

 

[redneckgeek]

In your query designer (criteria box), go to View->SQL View.
That will take you to a page with "code" that looks similar to what I have posted. However, as I stated, my code picks the next 5 dates. If you want to see only records for those days that are within the NEXT 5 DAYS, this won't do it for you.

 

[redneckgeek]

This would pull all records from your table that had been put in within a 5 day period around the date you specify. Things in lower case are made up field/table names. You'll have to supply your own in their place.

SELECT DISTINCTROW table1.field1, table1.some_date
FROM table1, (SELECT [enter start date] AS TheDate FROM table1) rs
WHERE table1.some_date Between [thedate] And DateAdd("d",5,[thedate])
ORDER BY table1.some_date;

The subquery:
(SELECT [enter start date] AS TheDate FROM anytable) rs

Will ask you to enter the first day of the range. Even though "FROM table1" shouldn't be necessary, Access wants to see a FROM statement.