Link to home
Create AccountLog in
Avatar of FlorisMK
FlorisMKFlag for Netherlands

asked on

Access Query To List Sequential Dates In Period

For several Access applications, I've needed to be able to create a list of all dates between a given start and end date. I've never figured out how to do it in Access SQL, so I've resorted to ugly workarounds like a Dates table containing all relevant dates, which is not very satisfying.

Is there a way to create a query in Access that accepts StartDate and EndDate as parameters, and produces a result set listing every single date from StartDate to EndDate, inclusive?

Thanks!
Avatar of wiswalld
wiswalld
Flag of United States of America image

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]
Avatar of FlorisMK

ASKER

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.)
Avatar of RgGray3
RgGray3

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.
Avatar of peter57r
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!)
SOLUTION
Avatar of RgGray3
RgGray3

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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];

Open in new window

SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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°)