In Access VBA, how do I get DISTINCT values from multiple columns of a table to be the row source of a combo box?

Question

I have a ComboBox combo1 and 3 fields area1, area2 and area3. The fields can be null or duplicates. I want combo1 to only display those values which are NOT duplicates and Not nulls. My current code displays all the values from the fields including all duplicates and Nulls. Its as though the DISTINCT is ignored completely. I am definitely getting it all wrong. I would be grateful if someone helps me with this. tblMain:

+-----------+----------+-------------+
+ area1     + area2    +    area3    +
+---------- +----------+-------------+
+ Islington + Barnet   +             +
+-----------+----------+-------------+
+ Stratford +          + Islington   +
+-----------+----------+-------------+
+   603     +    501   + Islington   +
+-----------+----------+-------------+

Expected Results in combo1:

501
603
Barnet
Islington
Stratford

Below is the state of my current code:

Private Sub Form_Load()
Dim rowS As String

rowS = "SELECT DISTINCT area1, area2, area3 FROM tblMain"

With Me.combo1
Me.combo1.RowSource = rowS
End With

End Sub

user_5009293 · Answer

Here you go, try this and make sure it works for you.

Private Sub Form_Load()
    Dim rowS As String

    rowS =  "SELECT area1 as Area FROM tblMain WHERE area1 IS NOT NULL UNION "
    rowS = rowS & "SELECT area2 as Area FROM tblMain  WHERE area2 IS NOT NULL UNION "
    rowS = rowS & "SELECT area3 as Area FROM tblMain WHERE area3 IS NOT NULL "

    Me.combo1.RowSource = rowS
End Sub

EDIT - I have replciated your issue and I can verify that you must have empty strings in your columns. Please verify for us will you?

In Access VBA, how do I get DISTINCT values from multiple columns of a table to be the row source of a combo box?

1 Answers