Year Dependent on Combo Box

[hawkins]

I have a form in which you can select a year from a combo box in a subform. I want to create a query to be a subform which uses that combo box to select the year between 1/1/Year and 1/31/Year OR between 12/1/Year and 12/31/Year. The year combo box is in the sub form called YearSubForm and is called Yearcmb. I used Between #01-01-2010# And #01-31-2010# Or Between #12-01-2010# And #12-31-2010# in the query view as a criteria when I wanted 2010, but I want the year from the combo box. Any suggestions will be greatly appreciated!

Also, in the form I have a subform that is located on top of a text box. I was wondering how I can have the text box show over the subform. I tried 'send to front' but that did not work. f

[ketbdnetbp]

One way I have done this in the past was to create a new column in the underlying query YR: DatePart("yyyy",[DateField]). Then, reference the combo as a criteria [Forms]![YourFormName]![comboname] in that field.

Hope this helps,

Jim

[hawkins]

that will work good cause I already have a year column but how shoud i set up the criteria for the date ranges? is there a way to only set the criteria for the month and day?

[ketbdnetbp]

Not sure I follow -

"how shoud i set up the criteria for the date ranges?"

If you use [Forms]![MainFormName]![YearSubForm].Form![Yearcmb] as a criteria in the YR field (YR: DatePart("yyyy",[DateField]) all records which have any date within that year will be returned. I thought that is what you wanted.

If not, please advise.

Jim

[hawkins]

sorry for not explaining it more, the form is going to display 4 months of data in different rows. The table is set up such that each of the months is in a different record. I am looking to use the query to select the different months so that I can update the information if needed. The reason why I am needing the date ranges as criteria is select the correct month and year. I could make a new column which takes out the month and uses that for the criteria but I figured id see if there was a way without creating another column.

[ketbdnetbp]

IMO, this looks like either a date range situation (startdate and ending date textboxes) which would preclude the need for a yearcombo and/or a monthcombo OR, a combination of Monthcombo and yearcombo. If the later, I would ask which 4 months? Would that be standardized (following predictable logic), such as: the current month + the next three? Or, Last 4, or previous + current + next two, etc? Much would depend on those answers.

Jim

[hawkins]

I was hoping to be able to just use the Year combo because for each year and name there is 4 records, 1 for each of the desired months. The months arent necessarily only 1 month either. January can actually be december but there is only 1 record for that grouping. Same for Apr could be March etc.. I think I am just going to create a new column with the months and run the query with criteria based on the month. I am bad at explaining what exactly I am needing so sorry for the confusions. Thanks for your help, its helped me a lot

[ketbdnetbp]

You're welcome, happy to help.

BTW, I just noticed you mentioned Name along with the year combo. Is the Yearcmb multiple columns, including one with the name? If so, you might be able to reference the Yearcmb.Column(?) and use that as a criteria in the name field?

Just a thought.

All the best,

Jim