Dubble values in Combo Box even with DISTINCT

[HHaskel]

Hi All

I have eleven text-fields (S1-S11) where I can enter clothes-sizes like this:

[S1]   [S2]   [S3]   [S4]   [S5]   [S6]   [S7]...
S      M      L      XL
XS     S      M      L      XL     XXL
42     44     46     48     50

To make it easier for the users I have created a combobox so they can use any old sizecombination.


cboSelectSize Row Source: (the "*" is just used as a field separator)

SELECT DISTINCT ("*" & [S1] & "*" & [S2] & "*" & [S3] & "*" & [S4] & "*" & [S5] & "*" & [S6] & "*" & [S7] & "*" & [S8] & "*" & [S9] & "*" & [S10] & "*" & [S11] & "*") , [tbl1].S1, [tbl1].S2, [tbl1].S3, [tbl1].S4, [tbl1].S5, [tbl1].S6, [tbl1].S7, [tbl1].S8, [tbl1].S9, [tbl1].S10, [tbl1].S11
FROM [tbl1];


Private Sub cboSelectSize_Change()
    ' Set Sizefields from Combobox
    Me.S1 = cboSelectSize.Column(1)
    Me.S2 = cboSelectSize.Column(2)
    Me.S3 = cboSelectSize.Column(3)
    Me.S4 = cboSelectSize.Column(4)
    Me.S5 = cboSelectSize.Column(5)
    Me.S6 = cboSelectSize.Column(6)
    Me.S7 = cboSelectSize.Column(7)
    Me.S8 = cboSelectSize.Column(8)
    Me.S9 = cboSelectSize.Column(9)
    Me.S10 = cboSelectSize.Column(10)
    Me.S11 = cboSelectSize.Column(11)
    Me.Form.Refresh
End Sub

My problem is that most of the size combinations shows up twice in my combobox even if I use "DISTINCT". I have an idea that the problem has something to do with null values, but have not been able to solve it. Have any of you out there any ideas or suggestions?


Thanks in advance

Hakan Haskel

 

[EhWot]

I'm no expert, but have you tried DISTINCTROW instead of DISTINCT?

 

[Steve101]

I think the problem is related to the first expression

("*" & [S1] & "*" & [S2] & "*" & [S3] & "*" & [S4] & "*" & [S5] & "*" & [S6] & "*" & [S7] & "*" & [S8] & "*" & [S9] & "*" & [S10] & "*" & [S11] & "*&quot

What is the purpose of this. Please explain ???
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[HHaskel]

Thanks for your reply

I tried DISTINCTROW right now, but then I get all rows I have in tbl1.

tbl1 consists of more fields than [S1] to [S11], I want my combobox to just show one row for each unique combination of [S1] to [S11] (hope this makes sense).


Hakan Haskel

 

[HHaskel]

To Steve101

The purpose was to make one unique string of all eleven fields that would look somthing like this:

*XS*S*M*L*XL*XXL******
or
*42*44*46*48*50*******

In my combobox I have set the column width to 0 for this value so it should not be shown to the users.
I thought this could be a solution.

Maybe I don't need it at all?


Hakan Haskel

 

[allanon]

Have you tried replacing the "*" with something else such as "/". The * is the wildcard for SQL. Another thing that you might try is to just run the query in a query window without the distinct just to see what it is returning. This should let you know right away why you are getting duplicate rows. If that does not help then post a few complete lines of the table and I am sure someone will take a look.

 

[HHaskel]

I replaced the "*" with "W" that did not make any difference. I creatad a query and got the same result. But then I found out that if I edited the values directly in the table ,the dubble row disappeared. The problem don't seems to be in the combobox. It propobly has to do with some invisible characters in my tables.

Now I have to check where this characters are comming from. Maybe its just some old data in the tables or maybe they are created somwhere in this application.


Thanks for all replies

Hakan Haskel