Need help with function in query not working in 2015

[Angel69]

Hi,

I have a query that the user puts in the start and end date for the month they want the report on the main menu form. The query then pulls data for the month they entered plus the two prior months so they see a trend.

I have the following funtion in my query for the date:

Between DateSerial(Year(Now()),Month([Forms]![Main Menu]![StartDate])-2,Day([Forms]![Main Menu]![StartDate])) And [Forms]![Main Menu]![EndDate]

I think the Year(Now(()) is messing things up because there is nothing in there for 2015. How do I modify the function so that if the user puts in 12/1/2014 to 12/31/2014 and we are on 1/5/2015 that it will pull October 2014, November 2014 and December 2014 data?

TIA

 

[namliam]

Are your users only entering 12/1 and 12/31 or adding the 2014 as well?

Is it always the previous month or can a user also enter 6/1 (june 1)

 

[Angel69]

They enter the month day and year in the main menu form. They can enter any dates in 2013 through 12/31/14 and get the data for the month they entered plus the previous two months. At the end of this month they should be able to enter 1/1/15-1/31/15 data and get January 2015, Nov 2014 and Dec 2014. Thanks.

 

[namliam]

That case simply use the dateadd function instead of this complicated dateserial

For your startdate use:
Dateadd("M", [Forms]![Main Menu]![StartDate], -2)
or is it
Dateadd("M", -1,[Forms]![Main Menu]![StartDate])

I forget, lookup the dateadd function if you cant figure it out...

 

[Angel69]

Thank you! I used this and it worked:

Between DateAdd("m",-2,([Forms]![Main Menu]![StartDate])) And [Forms]![Main Menu]![EndDate]

 

[namliam]

I mistyped the -1 instead of obviously -2, good fix on your part