Hi there people!

I'm working in VB 6, using an ADO control to connect to an Access 2000 database. The access database has 4 columns. Each column represents a different year, for ex. "2001", "2002" etc.

At startup a combobox (cboNames) is populated with all the names from only one of these columns.

The reason I can't use the column name directly in the filter is because the user can select which year they want to filter (they do this via another combobox). Therefore it needs to be dynamic, which is the reason for variable strYear (see below).

Code example:

dim strYear as string

strYear = "2002"                'normally assigned via user selection

ado1.recordset.movefirst

do while not ado1.recordset.eof()
       cboNames.AddItem strYear
       ado1.recordset.movenext
loop

Now I want to filter the recordset, using strYear and cboNames.text and this is where my problem lies.

ado1.Recordset.Filter = "strYear LIKE '" & cboNames.Text & "'"

The program keeps crashing at strYear. It is searching for a column with the name strYear, instead of using it's value to select the appropriate column.

I've changed the syntax more than I can count and I just can't seem to get it right.

Any help would be appreciated!

Thanks
dwsteyl

Please use this post to clarify my first post. That was a bad example to use.

I'm writing a program to contain athlete information. The field to be filtered actually has the name "2001_Reg_Nr" or "2002_Reg_Nr" etc. which stands for "Registration Number" for that particular year. That is obviously the athlete's registration number for the year.

The recordset is populated from the database using SQL. The different years come from different tables.

The user selects the particular year that they want to filter from a combobox populated with the available years. All the years stay visible in the datagrid. The combobox's name is cboYearToFilter. When they've made their selection, I assign the column to filter by using the following:

strYear = ltrim(rtrim(cboYearToFilter)) & "_Reg_Nr"

The recordset might then look like this

Name,"2001_Reg_Nr","2002_Reg_Nr","2003_Reg_Nr"

where the value in strYear is the column that the filter should use.

I then have an autocomplete combobox with the name cboNumber, which is populated with the available registration numbers from the year selected in cboYearToFilter. All of this is done in cboYearToFilter.Change.

The following statement keeps crashing on me:

adoAthlete.Recordset.Filter = strYear & " LIKE '" & cboNumber.Text & "'"

This statement should be used in cboNumber.Change, which executes every time it changes and therefore show only the athletes with that particular number taken from cboNumber.Text. (I have another filter on another form that works perfectly. On that form however, I use the column name directly in the filter)

The message VB6 gives me is:
"Run-time error "3001": Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

My conclusion is that VB6 does not recognise the value in strYear and is therefore looking for a column by that name.

Unfortunately the program needs to be dynamic (meaning the client must be able to just change the column to filter).

How to make VB6 recognise the value in strYear as the column to filter is the question? I've tried everything I can think of.

dwsteyl