quaterly filter

[batman1056]

Hi, I have a group of figures that I only want to show the values for the last quater part of the year, the period would be
Q1=April/May/June
Q2=July/Aug/Sept
Q3=oCT/nOV/DEC
Q4=Jan/Feb/Mar

What I want is for a filter to check todays date and use the data that falls in whatever period we are in i.e.
24/08/04 (today) will use the data from Q2

How do I do this.

Thanks

 

[batman1056]

Bump.. anyone>

 

[sfreeman@co.mer]

OK, here is the real answer....

SELECT Table3.ID, Table3.fk_ID, Table3.date
FROM Table3
WHERE (((DatePart("q",[date]))=DatePart("q",Now())));

 

[raskew]

Try the following in the debug (immediate) window:

FYStart = #1 Apr 04#
dteMyDate = #27 Aug 04#
myQtr = int(Datediff("m",FYStart,dteMyDate)/3) + 1
? myqtr
2

To see it in action, copy/paste the following into a new query
in Northwind. Run the query and when prompted for [FYStart]
enter 1 Apr 95. Output will be all Orders in the 3rd quarter, i.e.
(Oct - Dec 95) based on your designated FYStart date.

SELECT
    Orders.OrderID
  , Orders.CustomerID
  , Orders.OrderDate
  , Int(DateDiff("m",[FYStart],[OrderDate])/3)+1 AS MyQtr
FROM
   Orders
WHERE
   (((Int(DateDiff("m",[FYStart],[OrderDate])/3)+1)=3));

HTH - Bob