Select more options in one field then display the table and SOL

[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. 2 Combo Box (Combo1 and Combo2) to select the criteria

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

I can set two criteria to two different fields, then get the table results by the following codes. For example select A1 in Field1, and selcet B2 in Field2, then press the commond button to get the table results.
-------------------------------------------------
mySQL = "SELECT * FROM tblResults WHERE ((tblResults.field1) like '" & Combo1.Text & "*' And (tblResults .field2) like '" & Combo2.Text & "*')"
---------------------------------------------------

But now I am going to select two more options in one field first, for example, select (A1 and A2), or (A1 and A3) or (A1, A2 and A4) or (A2, A3 and A4) and so on from Field 1, then select only one record(B1, B2 or other) from Field2, then press common button to show the table. How can I select more options in one field then display the table? Dose it mean that I must add more Combo Box, but even for me, I do not know how many I should add because the option number is not fixed.

I hope someone can solve this problem.

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

http://chuanwang.tripod.com/

 

[mattKnight]

Didn't this get answered in
thread222-331995??

 

[chuanwang]

mattKnight,

No, it did not get answered in Thread222-331995. Because what I want is not only two options, it is more than two options, you can select whatever how many options in Field1 you like, then you can display the talbe. Maybe I can make it clear like this:
when you select A1 in Field1, you get one table, when you select A2 again, then you get another table, but this table include the first table, and then when you select A4 or more, you get the table added to original table to become one big table... I hope this time it is clear or not. And how to realize this?

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

http://chuanwang.tripod.com/

 

[chuanwang]

And also if you can make two tables(with same fields) added together, it can realize this goal. But how? Chuan Wang
Royal Institute of Technology
Stockholm, Sweden
Homepage:

http://chuanwang.tripod.com/

 

[sunaj]

You can add the result of 2 SELECT (that returns the same fields) by using the UNION keyword. Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[chuanwang]

Sunaj, can you explain it in detail. This question is quite difficult for me, I have spent a lot of time on it. But still I do not know how to solve it. Thanks Chuan Wang
Royal Institute of Technology
Stockholm, Sweden
Homepage:

http://chuanwang.tripod.com/

 

[sunaj]

Read about JET SQL at the MSDN sit.
Specifically read about the UNION operator at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[chuanwang]

I looked through the website above, but I did not find UNION operator.

I do think that the key solution to this quesion is question is how to set two more criteria or option to one same field, and how to express it in textbox or combo-box, or how to link the different table together. By the way, I use the Flexgrid to display the table.

By the way, I look Union up in VB Help, the syntax is like following:

query1 UNION [ALL]

query2 [UNION [ALL]

queryn [ ... ]]

But I do not know how to use it. Also I am not sure that it can solve my question or not.

Thanks


Chuan Wang
Royal Institute of Technology
Stockholm, Sweden
Homepage:

http://chuanwang.tripod.com/