6 months from now question

[Bay0519]

Hi,

Could someone help me with this problem? I can't seem to figure it out. I have a query that showed all total schedule hours based on month. But what I want is when user run the report, it will only show all hours within the next 6 months. For example, if I run the report today, then the report will show oct total, nov total, dec total, jan total, feb total, and mar total. How can I accomplish this task?

Thank you.

[NeoPa]

It's hard without any details of your project, but essentially you need to include in a WHERE clause :

... [DateField]<DateAdd('m',6,Date())

Welcome to Bytes!

[Bay0519]

Hi Neopa,

Thank you for your quick response. My project is to know what kind of workload in the schedule for the next 6 months group by month. So I created a query to that show all the remaining hours based on indate

 SELECT (Format([INDATE],"YYYY - mm")) AS [month], Sum(resource_report.RemaininHours) AS SumOfRemaininHours, resource_report.RESOURCE_ID
FROM resource_report
GROUP BY (Format([INDATE],"YYYY - mm")), resource_report.RESOURCE_ID
ORDER BY (Format([INDATE],"YYYY - mm")); 

this query will show me all the hours group by year and month. My question is, is it possible so when user opens a report, it will only show hours for the next 6 months? because the query will show all year and months that are in the resouce_report table.

Hope it won't confuse you more.

[NeoPa]

I never recommend formatting results in a query. This is likely to cause you problems down the line. The general rule is "Only ever format the data (convert the value to a string) at the last point where it is used for the operator."

Having said that, try the following :

SELECT   Format([INDATE],'YYYY - mm') AS [Month],
         Sum([RemaininHours]) AS SumOfRemaininHours,
         [RESOURCE_ID]
 
FROM     [resource_report]
 
WHERE    [INDATE] Between CDate('1 ' & Format(Date(),'mmm yyyy')) And
                          CDate('1 ' & Format(DateAdd('m',6,Date()))-1
 
GROUP BY Format([INDATE],'YYYY - mm'),
         [RESOURCE_ID]
 
ORDER BY Format([INDATE],'YYYY - mm')

I was still unable to determine from your posts whether current or past data should be handled, so I assumed it must be in this version. I have also assumed you intend the current month to be included in the 6 months. EG From 5 Oct 2009 this would include dates up to the end of March 2010. If this is not what you require then please explain clearly what you are looking for.

[Bay0519]

It worked... Thank you so much.... :)

[NeoPa]

It's always good to help :)