All dates in range

[JoeComputer]

As part of a larger issue, I am trying to figure out how to have an Access 2000 select query produce all dates in a date range into one field on the query. Assume at this point there are no tables in the query.

If 01/01/06 (mm/dd/yy) and 02/01/06 is used, then it'd list all the dates between those two, inclusive.

Is this even possible?

Thanks for looking.

 

[Rich]

Why would you want a list of dates with nothing else?

 

[JoeComputer]

Doesn't matter...

This is one part of a larger issue that I'm trying to work through.

I am breaking this database down into smaller pieces, and this is one of those pieces. I'm currently using a table that lists 365 date records for 2006 but the query help I'm asking about in this thread would be more flexible.

Since you asked, here's the big picture (copied and reformatted from microsoft.public.access.reports newsgroups):

I collect data in this fashion:

Clock# StartDate EndDate AbsenceType Dept Classification

7 01/01/06 01/04/06 Vacation Ship. Clerk
13 01/02/06 01/02/06 LOA I.T. Slug
35 01/04/06 01/05/06 Misc. I.T. Dweeb

I expand the above data as such (using a query):

ActiveDate Clock# AbsenceType

01/01/06 7 Vacation
01/02/06 7 Vacation
01/02/06 13 LOA
01/03/06 7 Vacation
01/04/06 7 Vacation
01/04/06 35 Misc.
01/05/06 35 Misc.

I use a crosstab query on the above query to create this:

ActiveDate 7 13 35

01/01/06 V
01/02/06 V L
01/03/06 V
01/04/06 V M
01/05/06 M

So I'm trying to get a report to show the crosstab's data in the same way, but reports and the controls on them need set fields to bind to. Therein lies the problem. The 'ActiveDate' column wouldn't change, but the Clock#'s constantly do depending on what criteria is chosen, such as what Dept and/or Classification.

End Goal:

SOMEHOW, whether using a crosstab query or not, get a report that shows 'Clock#' as Column Headings (Clock#'s vary), each date in a specified date-range (I'm using 365 dates for 2006) as Row Headings, and some other data as Values ('AbsenceType', in the example above).

It doesn't even technically HAVE to be a report, but I can't figure out groupings that work this way for Forms...

I've been working on this aspect of my database for 2 weeks, trying various
techniques, but can't seem to find a lead from support.microsoft.com.

 

[JoeComputer]

Ok, so ignoring my "big picture" post...

Anyone care to take a stab at my initial question, being having an Access 2000 select query produce all dates in a date range into a field on the query?