how to get dates for a months and year

[Guest]

I need to generate a query with annual sales and with sales per months. I'm
going to create the queries for the total year and months separately and then
combine them.
But, I've stuck with how to put a restrictions on the year and months dates.
I need Aug 2003 - Jul 2004 fiscal year, and all those months.

Can anybody help me with this?

Thanks

 

[Guest]

I'm trying the following:

IIf(Month(Date())>8,DateSerial(Year(Date())-1,8,0),DateSerial(Year(Date()),8,0)) And <=IIf(Month(Date())>8,DateSerial(Year(Date())-1,9,0),DateSerial(Year(Date()),9,0))

for Aug 2003.

Please, advise whether it's correct and not to complicated.

Thanks

 

[Dale Fye]

If this is considered to be FY 2004 then you could try something like:

PARAMETERS [Enter FY] Long;
SELECT Year([SalesDate]) as SalesYear,
Month([SalesDate] as SalesMonth,
SUM([Sales]) as MonthlySales
FROM yourTable
WHERE SalesDate > DateSerial([Enter FY] -1, 8, 1)
AND SalesDate < DateSerial([Enter FY], 9, 1)

The first part of the where clause tells it to include date SalesDate
greater than Midnight on August 1st.
The second part of the where clause takes into account that your SalesDate
field is probably a date/time value and in order to include the sales from
the last day in August, you should really just indicate < Sept 1st of the
current FY.

or you could use a WHERE clause like:

WHERE YEAR(DateAdd("m", 5, SalesDate)) = 2004

 

[Guest]

Thanks a lot, Dale.

If this is considered to be FY 2004 then you could try something like:

PARAMETERS [Enter FY] Long;
SELECT Year([SalesDate]) as SalesYear,
Month([SalesDate] as SalesMonth,
SUM([Sales]) as MonthlySales
FROM yourTable
WHERE SalesDate > DateSerial([Enter FY] -1, 8, 1)
AND SalesDate < DateSerial([Enter FY], 9, 1)

The first part of the where clause tells it to include date SalesDate
greater than Midnight on August 1st.
The second part of the where clause takes into account that your SalesDate
field is probably a date/time value and in order to include the sales from
the last day in August, you should really just indicate < Sept 1st of the
current FY.

or you could use a WHERE clause like:

WHERE YEAR(DateAdd("m", 5, SalesDate)) = 2004

--
HTH

Dale

I need to generate a query with annual sales and with sales per months. I'm
going to create the queries for the total year and months separately and then
combine them.
But, I've stuck with how to put a restrictions on the year and months dates.
I need Aug 2003 - Jul 2004 fiscal year, and all those months.

Can anybody help me with this?

Thanks