Microsoft Excel 2007 Query based on date parameter

printmedia used Ask the Experts™
Hi all.

I have an excel 2007 worksheet whose datasource is a sql table. The fields in that table are: ItemNumber, QtySold, Month, Year

I want to be able to have the user enter the following parameters: Month1, Year1, Month2, Year2

So if the user enters: 10, 2010, 12, 2010 the results would display data from Oct 2010 through December 2010.

If the user enters: 11, 2010, 02, 2011 the resuls would display data from Nov 2010 through February 2011

How can I do this using the query option in excel?

I tried doing this using the code below, it worked fine with my first example but when I got to the second example it didn't work.
WHERE (myTable.Month1>=? And myTable.Month2<=?) AND (myTable.Year1>=? And myTable.Year2<=?)

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Professional
Try to use something like:

where CAST ( Year + Month + '01' AS datetime ) >=  CAST ( @Year1 + @Month1 + '01' AS datetime )
      AND CAST ( Year + Month + '01' AS datetime ) <=  CAST ( @Year2 + @Month2 + '01' AS datetime )

Assumptions: Year , Moth are the column names in your table.
Year1, Month1, Year2, Month2 are program variables holding user inputs.

Hope it whelps you.

Thanks, SB

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial