Month from date query issue

[WineSnob]

I have a field in a query that gets the month from the date field ---- Month: Month([Date]) using the criteria
Between [forms]![frmMiscExpenseInput]![cmbSTARTMonthtxt] And [forms]![frmMiscExpenseInput]![CmbENDMonthtxt]. The control on the form cmbSTARTMonthtxt is a combo box that is bound to a number 1 for january 2 for February etc.
When I select any start or end month that contains a 1 it returns all months with a 1 in it.
Example I select February through March and I get months 2 and 3. Correct
I select January through March and I get months 1,2,3,10,11 and 12. It must be a format issue somewhere????/

 

[Alansidman]

In your combo box on your form, are your ID fields for the months, text or numbers? They look like numbers, but how are they formatted in the underlying table.

 

[WineSnob]

Month is number format integer

 

[Alansidman]

Would you post a copy of your database with some sample data so that we can have a look directly at it. Otherwise, we are guessing as to what the issues may be. Be sure to run a compact and repair to make sure the size is reduced. Dummy up any confidential data.

Alan

 

[WineSnob]

Here is a stripped version. Thanks.

 

[Galaxiom]

Aside from the problem you have asked about the technique you are using is very slow because it must apply a function to every record in the table.

You will get much better performance by generating the corresponding dates for the beginning and end months and apply that criteria in the criteria. This query only applies functions to the criteria and the query can use the index on the date field to return the matching records.

This approach can easily be hundreds of times faster than extracting the month from the records expecially as the size of the table increases.

 

[WineSnob]

Thanks for the response. I not sure how to generate the corresponding dates for the beginning and end months. I will try to figure it out though. I makes sense just not sure how to do it. HAPPY HOLIDAYS.

 

[Brianwarnock]

First day of month
Dateserial(year(date()),[cmbstarmth],1)
last day
Dateserial(year(date()),[cmbendmonth]+1,0)

Brian

 

[WineSnob]

I am getting an error. Expression to complex. Not sure what to with it. See attached mdb. Thanks for looking and your help.

 

[Galaxiom]

The problem is the RecordSource query of the form is based on values in the controls in the form so the query cannot access them. Hence the too complex error.

I have attached one way to delay the query. The form opens with an empty recordsource and applies it when you press the button I added. After that it uses the events you included on the combos.

 

[WineSnob]

Thanks. I understand that now. I mofified it a little by setting default values for the control show when the form opens it show results then the user can filter the results on change events. Happy New Year!