VBA Between/And Query Criteria

[JPR]

Hello friends,

I have a list box populated by queries which get their criteria from the selection users make in a combo box.

Simple criteria like "Hello" gives me no problem in the After Update event code of the combo:

Me.mycombo = "Hello" Then
Me.mylistbox.RowSource = "qryHello"

I have now added in the combo a list of years which will be used as criteria to another query. There are quite a lot of years, about 100:

I will need some sort of code that will run the query according to the year selected. Obviously I cannot write a piece of code as the one above for each year, so I was thinking of something similar to the code below (which does not work).

If Me.mycbo = Between #1901# and #2050# Then
Me.mylist.RowSource = "qryYOB"

Me.mylist.requery

Thank you for your help.

 

[arnelgp]

combobox can only have 1 result.
make them two combos, one for start year, the other for the end year.
create new table and fill them with the years.
set the rowsource of your combos to this table.
requery the list on each combos afterupdate event.

you only need 1 query for the list:

"select * from yourTable Where Year(dateField) Between Forms!yourFormName!comboStartDate And Forms!yourFormName!comboEndDate;"

 

[JPR]

Thank you. Will follow your advise.