Previous Months data

Hi,

I am using Access 2003 as Front end and Sql backend. I have a cross tab query
which spits sales by sales rep for month 1 to mth xx based on the date
selected in cmbox enddate. I want to restrict the data for for the past 6
mths from the date selected in the combobox enddate.

I tried using this :
Between DateAdd("m",-5,[Forms]![Utilization Reports]![EndDate]) And DateAdd
("m",0,[Forms]![Utilization Reports]![EndDate])

but for the months with 30 days, it starts the report from the 30th of the
month.

To make it clear, when I select June 30, 2009, it should give me the totals
for Jan 2009, Feb 2009 .....June 2009.

Can someone please advise at to what I am doing wrong here.

Thank you,

Gmen

--
Message posted via http://www.accessmonster.com

Between DateAdd("m",-5,[Forms]![Utilization
Reports]![EndDate])-Day([Forms]![Utilization Reports]![EndDate])+1 AND
DateAdd("m",1,[Forms]![Utilization
Reports]![EndDate])-Day([Forms]![Utilization Reports]![EndDate])

Make sure that you want -5 or -6.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"gm6243 via AccessMonster.com" wrote:

Hi,

I am using Access 2003 as Front end and Sql backend. I have a cross tab query
which spits sales by sales rep for month 1 to mth xx based on the date
selected in cmbox enddate. I want to restrict the data for for the past 6
mths from the date selected in the combobox enddate.

I tried using this :
Between DateAdd("m",-5,[Forms]![Utilization Reports]![EndDate]) And DateAdd
("m",0,[Forms]![Utilization Reports]![EndDate])

but for the months with 30 days, it starts the report from the 30th of the
month.

To make it clear, when I select June 30, 2009, it should give me the totals
for Jan 2009, Feb 2009 .....June 2009.

Can someone please advise at to what I am doing wrong here.

Thank you,

Gmen

--
Message posted via http://www.accessmonster.com