Query for a month range based on combobox entry

[sponge]

Hi,

I currently have a form that creates a report based on a query that takes in a start date and an end date.
What I would like to do is to create comboboxes that will let the user choose a month and year for the report.
Is there a simple way to choose an entire month in access? I've tried using the Month() function to no avail.

The SQL statement for where I select the start and end date is below:

WHERE ((([tblData].[EntryDate])
Between [Forms]![frmByRange]![BeginningDate] And [Forms]![frmByRange]![EndingDate])

Any help would be much appreciated!

 

[FoFa]

If there is a lot data, you can ask for the month/year and calculate the begin/end date yourself. But if you only have a few thousand rows, you can use something like (this assumes your EntryDate is a date datatype):
WHERE Year([tblData].[EntryDate]) = Forms!Myform!YearIn
AND Month([tblData].[EntryDate]) = Forms!Myform!MonthIn
Either will work, but performance is typically better without data conversion on each row

 

[goldmedallist]

Followup question on Date SQL

Hi all,

I am thinking of having a drop down box that has the month and years listed ie Jan 2004, Feb 2004 etc. Is there a way to have those values extracted from a Calendar like control? I have tried the Calender Control 10.0 but it has also the date which I do not require.

As I was searching for a solution, I came by the following SQL suggested by FOFA. In the example below, YearIn and MonthIn are seperate fields, what amendments should I make to the SQL if I would want it to be something like the mmm yyyy that checks both the month and the year at the same time?

By FOFA
WHERE Year([tblData].[EntryDate]) = Forms!Myform!YearIn
AND Month([tblData].[EntryDate]) = Forms!Myform!MonthIn

Thanks in advance,

TK