1

I have the end dates of each quarter as the PK for a table, and I need to compare a date to see which quarter-ending value would be used in a calculation.

The Table looks like:

EndingDate     Value
12/31/2012     $1,000

For example, given 3/1/2013 I would need to return 12/31/2012 and use that date to retrieve the $1,000 value.

Does anyone know what to use in MS Access 2007 to perform this? I tried:

DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, DATEINQUESTION), 0))

The calculation always uses the previous ending quarter's date, and the value associated with that date.

Mark C.
  • 6,332
  • 4
  • 35
  • 71
  • 1
    I don't understand why `12/31/2012` should be the quarter end date for `3/1/2013`. Do you mean the end date of the previous quarter? – HansUp Jan 14 '14 at 19:47
  • Well, in the above scenario - 3/31/2013 has not yet come to pass, thus it would not have a Value, so (you are correct) we use the end date of the previous quarter. – Mark C. Jan 14 '14 at 19:49

2 Answers2

2

One way to do it would be with a combination of DMax() and DLookup(). For sample data in a table named [EndingBalances]

EndingDate  Value
----------  -----
2012-09-30    900
2012-12-31   1000
2013-03-31   1100
2013-06-30   1200
2013-09-30   1300
2013-12-31   1400

the expression

DMax("EndingDate","EndingBalances","EndingDate<#2013-03-01#")

would return the date

2012-12-31

and therefore the expression

DLookup("Value","EndingBalances","EndingDate=#" & Format(DMax("EndingDate","EndingBalances","EndingDate<#2013-03-01#"), "yyyy-mm-dd") & "#")

would return the value

1000
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
0

I used this solution and it worked for me:

 Format(DateAdd("s",-1,DateAdd("q",DateDiff("q","1/1/1950",Date()),"1/1/1900")),"Short Date")

It works so far..

Mark C.
  • 6,332
  • 4
  • 35
  • 71