Filter query

[Directlinq]

Hi can someone help me with this conumdrum?

I Have a table with 4 fields
ID, Month, Year, Total

1, January, 2009 , 55

2, February, 2009 , 53

13, January, 2010 , 44
etc

I have a form with 2 text box's. Startdate and enddate
In the startdate i put 2009 and in the end date i put 2010.
How can i filter the query so Only records with April 2009, May 2009, June 2009, July 2009, August 2009, September 2009, October 2009, November 2009, December 2009, January 2010, February 2010, March 2010. Show in the query?

if 2010 and 2011 was put in the text box's Only records with April 2010, May 2010, June 2010, July 2010, August 2010, September 2010, October 2010, November 2010, December 2010, January 2011, February 2011, March 2011. Show in the query?

So it will show from april startdate through to march enddate.

Many Thanks

 

[dcb]

Hang on
I know there was another post about this table that I didnt really look at BUT
Month and Year are reserved words in Access - I suggest you change them

As for the query this should work:

SELECT Table3.MyYear, Table3.MyMonth, Format("01 " & [MyMonth] & " " & [MyYear],"Short Date") AS exDate
FROM Table3
WHERE (((Format("01 " & [MyMonth] & " " & [MyYear],"Short Date"))>"01/03/2009" And (Format("01 " & [MyMonth] & " " & [MyYear],"Short Date"))<"01/04/2010"));

Depending on your regional settings you may need to use the US format ie
01/04/2010 = 04/01/2010

 

[Directlinq]

Ok ive changed the Month to TaxMonth and Year To TaxYear. Although my table does not have any dates in it. It only has the month and the year. The first field is just a random number key field.
Taking that into account your query will not work.

Any other ideas
Many Thanks for your help

 

[dcb]

Have a look at my query:

I was using MyYear and MyMonth anyway - it was to give you a idea of what you could do....

I am creating a date by " 01 & Month & Year " Then using the format function to convert to short date and filter on that

So change the MyYear MyDate into TaxMonth and TaxYear in the query