Select more items from ListBox to show the table with SQL

[chuanwang]

I am going to set some criteria to display table results. In my database (Microsoft Access) I have a table (tblResults) with the following fields

ID, contents, field1, field2, field3 as shown following

id contents Field1 Field2 Field3

1 C1 A1 B1 D1
2 C2 A2 B2 D2
3 C3 A1 B2 D1
4 C4 A3 B1 D3
5 C5 A2 B3 D2
6 C6 A3 B3 D3
7 C7 A1 B2 D4
8 C8 A4 B3 D3

On the form of Visual Basic I have

1. A DataControl (Data1) with its database property set to my database and its recordsource left blank

2. A Flexgrid (Flex1) with its datasource set to the datacontrol

3. List Box to select items

4. A command button (cmdTable) to do the search and display results

Now I can select two more records from Field1 by using List-box, for example, I can select (A1 and A2), or (A1 and A3) or (A1, A2 and A4) or (A2, A3 and A4) and so on from Field 1. What I want is when I press the command button, the corresponding table will appear. Now the problem is when I press the command button, it does not work. The codes I used is like following
----
Private Sub Command1_Click()
Dim I As Integer, WhereClause As String
Dim mySQL As String
For I = 0 To List1.ListCount - 1
If List1.Selected(I) = True Then
WhereClause = WhereClause & List1.List(I) & " OR "
End If
Next I
If Right(WhereClause, 3) = "OR " Then
WhereClause = Left(WhereClause, (Len(WhereClause) - 3))
End If

mySQL = "SELECT * FROM tblResults WHERE (Field1 = WhereClause)"
Data1.RecordSource = mySQL
Data1.Refresh
End Sub
-----

Could someone help me to check the code or suggest another method to solve this problem


Thanks a lot in advance!
Chuan Wang
Royal Institute of Technology
Stockholm, Sweden
Homepage:

http://chuanwang.tripod.com/

 

[chuanwang]

Does anyone know this? Chuan Wang
Royal Institute of Technology
Stockholm, Sweden
Homepage:

http://chuanwang.tripod.com/

 

[Schroeder]

I think there are a couple of problems there. The first is that Field1 appears to be a text field. You've got to surround your criteria with either double or single quotation marks. Single quotes are easier.

The other problem is that, as far as I know, you can't say "... WHERE Field1 = 'A1' OR 'A2' OR 'A3'". You've got to say "... WHERE Field1 = 'A1' OR Field1 = 'A2' OR Field1 = 'A3'".
So, while you're looping through the ListBox,

If List1.Selected(I) = True Then
    WhereClause = WhereClause & "Field1 = '" & List1.List(I) & "' OR "
End If

Then,

mySQL = "SELECT * FROM tblResults WHERE " & WhereClause

 

[chuanwang]

I tried, but it does not work. Chuan Wang
Royal Institute of Technology
Stockholm, Sweden
Homepage:

http://chuanwang.tripod.com/

 

[chuanwang]

Could someone read this and give me some idea? Chuan Wang
Royal Institute of Technology
Stockholm, Sweden
Homepage:

http://chuanwang.tripod.com/

 

[dnulgreb]

Have you tried to use CreateRecordset/OpenRecordset
Like
Data1.Recourdsource = Data1.OpenRecordset(mySQL)