Access Query To List Sequential Dates In Period

Normally I use a form with a startdate and enddate textboxes and a button to open a report or form or whatever based on a query. In the criteria for the date in the criteria use something like

Between [Forms]![searchform]![startdate] And [Forms]![searchform]![enddate]

Thanks, wiswald, that is of course the method to select dates between the startdate and enddate. However, that's the general method to select dates that pre-exist in a table. Let me clarify what I'm looking for.

Let's say I have a table of expected financial transactions:

Date   Amount
04/03  +100
04/10  -50
04/12  +20
04/18  +55
04/27  -80
04/30  +20

I want to be able to query this table in such a way that I get every single calendar date between,say, 04/01 and 04/10, with the existing values from this table if they're there, and a zero value if not. That means I need to be able to create a query listing every single calendar date between 04/01 and 04/10, and LEFT JOIN that to the ExpectedTransactions table.

Date   Amount
04/01  0
04/02  0
04/03  +100
04/04  0
04/05  0
04/06  0
04/07  0
04/08  0
04/09  0
04/10  -50

And I want to be able to do this *without* using a table of every calendar date.

(PS: The purpose is creating an accurate account balance prognosis time series that gives me the expected account balance for every calendar date.)

I would resort to a function or routine to build a record set in this circumstance...  (And I use SQL when ever possible!)

Thanks., RgGray3. That's one of the other workarounds I've used in very specific circumstances. But there are many queries and reports in my application that need the calendar dates for time series information, so the recordset solution is inconvenient, to say the least.

There's nothing you can do in sql to get these results, unless you build a table of all possible dates.
Otherwise you are forced into using VBA to create the records in a table (which is probably how you would get the first option!)

Thanks, RgGray3. That's a very useful suggestion. Let's see if anyone comes up with a SQL solution, and if not, I'll go with the temp table.

Here is how to create a dynamic list of dates for up to one year from any date.

Create two small tables, tblDays with Integer values from 1 to 31, and tblMonth with Integer values from 1 to 12.
Use these in a query with a Cartesian (multiplying) join as shown.
Now, use this with your date interval in another query where you create an outer join to your data.

A note: If you prefer, you can of course use tblDays with an alias for tblMonths as well by filtering on 1 to 12, thus using one table only. In that case a more neutral name than Day should be given the field holding the values.

Or you could create a general purpose query, qdyMonth:

SELECT
  [Day] As Month
FROM
  tblDays
WHERE
  [Day] Between 1 And 12

and use that for tblMonth.

/gustav

  Date1 DateTime, 
  Date2 DateTime;
SELECT DISTINCT 
    DateSerial(
      Year([Date1]),
      Month([Date1])-1+[tblMonth]![Month],
      Day([Date1])-1+[tblDays]![Day]) AS 
  DateOfPeriod
FROM 
  tblDays, 
  tblMonth
WHERE 
  DateSerial(
    Year([Date1]),
    Month([Date1])-1+[tblMonth]![Month],
    Day([Date1])-1+[tblDays]![Day])
  Between 
    [Date1] 
    And 
    [Date2];

Select allOpen in new window

Harfang, the point about needing only the day before or after is very well made. Thanks! I need the dates to graph the changes in account balance, and to paint a realistic picture, the day before a transaction should show the old balance, not some interpolated intermediate value. Getting the day before/after dates is indeed very easy and would fix my problem.

SELECT Date - 1 AS DayBefore FROM Transactions ORDER BY DayBefore;

Use these dates to find the last known balance *before* these dates, and UNION these with the actual balance query to create the data series I need. Combined with a graph that has date scale on the X axis, I'm done!

The tip about using a helper table with integers will also be very useful. Thank you, harfang, and cactus_data for the trick query to create dates. (My first reaction was 'that'll never work" but then I noticed the DISTINCT keyword.)

I'm going to distribute points...

Thanks! I'm glad this advice useful: it will save you a lot of unnecessary calculations.
Success with your project!
(°v°)