Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-20-2004, 08:58 AM   #1
joe789
Newly Registered User
 
Join Date: Mar 2001
Location: Sylvania, OH 43560
Posts: 154
Thanks: 1
Thanked 0 Times in 0 Posts
joe789
It is possible to list all dates inbetween a date range? Please help

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

joe789 is offline   Reply With Quote
Old 08-20-2004, 10:09 AM   #2
Malcy
Newly Registered User
 
Join Date: Mar 2003
Location: Edinburgh, Scotland
Posts: 586
Thanks: 1
Thanked 0 Times in 0 Posts
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
__________________
Malcy
Solve one problem and create another!!!
Malcy is offline   Reply With Quote
Old 08-20-2004, 10:25 AM   #3
Rich
Guest
 
Posts: n/a
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

  Reply With Quote
Old 08-20-2004, 11:45 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,354
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
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).

The_Doc_Man is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 07:16 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World