how to get dates for a months and year

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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))
Click to expand...
for Aug 2003.

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

Thanks
 
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
 
Thanks a lot, Dale.

Dale Fye said:
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

Alex said:
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
Click to expand...
Click to expand...
 
Back
Top