Help with ado.recordset.filter syntax, please!

[dwsteyl]

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

 

[SBerthold]

strYear & " LIKE '" & cboNames.Text & "'"
or
strYear & " LIKE " & cboNames.Text

The second example is assuming that the year column is numeric, which it normally should be.

Also, aside from this, the db table shouldn't be built like that (column names being the years) as it is not Normalized. But rather built like:

ID Data1 Year

so that the data in the table looks like:

1 SomeData1 2002
2 SomeData1 2003
3 SomeData1 2004

4 SomeData2 2002
5 SomeData2 2003

6 SomeData3 2002
7 SomeData3 2003
8 SomeData3 2004

 

[dwsteyl]

SBerthold

Thank you for your reply.

Please ignore the first post as that was a very bad example. This might be more enlightning:

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

 

[SBerthold]

Well, I think you have seriously gone about this wrong anyways (the way the table is built), as I mentioned in my first post.

But, to add to the problem, you have declared column names beginning with a digit.

That is your problem.

A solution to this, which I was hesitent to even offer, considering a bad db design lacking normalization and a bad column naming, would be:

= "[" & strYear & "] LIKE ...

 

[dwsteyl]

Thank you for your help!

The following works fine:

adoAtlete.Recordset.Filter = "[" & ActiveYear & "_Reg_Nr] LIKE '" & cboNumber.Text & "'"

I know that there's some issues with the database build. I just made a tester to see how everything's going to fit together and to get some ideas on how to plan the layout of the database.

dws

 

[BobRodes]

The way to plan the layout of the database is to set everything in one-to-many relationships. You are struggling with resolving a many to many relationship. The way to do this is to create an associative entity that is in one to many relationship with each of the kernel entities that are in many to many relationship with each other.

I'll give an example to clarify this. You have a library. The library has books and patrons. A book may be borrowed many times over its lifetime. A patron may borrow many books. Your solution is analogous to putting in the patrons table the fields book1, book2, book3, book4 and so on, which is obviously unworkable in this context.

The right way to do this is to have a loans table as well. So, like this:

Patrons
Library Card Number
(other info about patron)

Books
Book Number
(other info about book)

Loans
Library Card Number
Book Number
(date of loan, etc)

Ok? See if you can plug this logic into your situation.

HTH

Bob