It is possible to list all dates inbetween a date range? Please help

[joe789]

Hi Folks,

As a step in getting to my end result, I am trying to take a database that has a beginning date and ending date fields. For example, the first record might have a unique record key of 111, a beginning date field value of 7/1/03, and a ending date field of 7/5/03. Is it possible to create a query or in some way list the unique record key and each date within that date range.

For example:

Unique Key BegDate EndDate
111 7/1/03 7/5/03

Would become:

Unique Key Date
111 7/1/03
111 7/2/03
111 7/3/03
111 7/4/03
111 7/5/03

Thank you VERY much for any help, as I am frantically trying to find out how to do this for a non-profit government agency and the person requesting this needs it to run additional analysis. I have never needed to do something like this before, so don't know where to start ... any help would be greatly appreciated. And I hope there is some easy way to do this.

Thanks,

Joe

 

[Malcy]

Hi
I needed to do something similar, although a few people suggested I was going wrong way. Anyway I did and what you need is the function called DateAdd.
You can either search on this forum for it or else in help files.
Takes a while getting used to it but since you work with US dates (why didn't they invent this stuff in the UK and use proper dates!?!) you should find it a bit easier than I did.
You would probably need to create a temporary table to hold these records and if selecting the project from a form then use ADO to AddNew to the temporary table with the Id field taken from the form and the incremental date created using DateAdd.
Probably need a DoWhile loop using date = end date as the criteria.
Hope this helps you a bit
Good luck

 

[Rich]

Use the query wizard to build a select query add the two fields you require.
In the criteria section for the Date field add
Between [EnterStartDate] And [EnterEndDate]

the query will now return all records between the dates entered. If you save the query Access will recognise the Regional Settings for your dates.
Once you have that working you might like to improve it by entering the Parameters via a form / cal. control where you can validate the dates before passing them to the query

 

[The_Doc_Man]

I wonder if I'm reading the question differently than the others did...

I believe you want something that will ENUMERATE the dates between (including) two dates in your record, even if the given date isn't actually named in the table. I.e. in your example, 7/1/03 isn't on an actual record in the table but because it is enclosed in the range of your record #111, you want to see it.

I'm not sure if that is a correct interpretation of your question, but if it is, you can do this in a couple of steps that involve a bizarre join. To make this work, you need to do a one-time action to manually create a table that contains every date from some arbitrary starting date to some arbitrary ending date. (Fer instance, from the earliest date in your table based on your BegDate field to the latest date in your table based on your EndDate field.)

So this table contains one field (unless you have issues in mind...), a date. And the records are 1/1/03, 1/2/03, 1/3/03, etc. etc. for as many days as there are, one record per date, from the selected start to the selected end.

Now you can do a pseudo-join. Something like

SELECT [UniqueKey],[ADate] FROM DATELIST, EVENTLIST WHERE
DATELIST.ADate >= EVENTLIST.BegDate AND
DATELIST.ADate <= EVENTLIST.EndDate;

WARNING: This might take a while to run. It is a permutation join. Access will try to do it anyway.

Then, you can use this query to either populate a table via append or maketable. Then you can run a query to eliminate duplicates when the dates overlap.

The other way to do this would be to write VBA code, which isn't always easy if you're not comfortable with it. But actually, it would run faster because then you could use the DateAdd function suggested by Malcy to generate new entries in a recordset for each record in your list that has this key in it.

Then you would run the "eliminate duplicates" query as suggested before.

For ME, the VBA code would be easier, but I don't know how comfortable you are with VBA, so I mentioned the alternative (and its drawback - speed or lack thereof).