[RESOLVED] SQL DISTINCT only works for the first item in my listbox

[cfd33]

This is a 2 part question:

1. I use the following sql statement fill my second listbox (this works fine):

strSQL = "SELECT DISTINCT * FROM Vehicle"

The problem is that only the first item in the first listbox will be affected by the DISTINCT in the sql, but the remaining items in the first listbox will not.

Ex.. If the user selects "BrassTag" from the listbox, then the second listbox will populate with the numbers for "BrassTag" with no duplicates.

When the user selects the next item (ClassCode) from the same listbox, then the second listbox will populate with the data for "ClassCode",but with duplicates.

2. How to get the blank fields out of my listbox? Certain fields in my database have nothing in them and when the sql retrieves the data for my listbox I get blank spaces.

I tried using trim, but it didnt work

[RobDog888]

When loading your listbox, just create an If Then cod block to test for a nullstring "". Skip .additem if its nullstring etc.

Now for the non-distinct problem: If you are only using a single field in your select query then dont call all fields as the distinct is for all fields at once. So if field1 and field2 combined are distinct then your good but if field1 is distinct but field2 is a different value then its still a distinct row.

Ie: First record distinct
Field1
"Dog"

Field2
"Cat"

Second record distinct but will have a dup value for one of the fields:
Field1
"Dog"

Field2
"Cow"

You see the two rows are distinct as its a combination of the two fields that when compared are distinct, not individual fields compared.


So to solve it just do a ...

strSQL = "SELECT DISTINCT Field1 FROM Table1;"

[cfd33]

Rob,

Thanks for responding.

I'm not quite sure that I follow you. Here is my code for the lstboxes:

Option Explicit
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim strQuery As String
Dim strClassCode As String
 
Private Sub Form_Load()
    openConnection
    
    With lstQuery
        .Clear
        .AddItem "BrassTag"
        .AddItem "ClassCode"
        .AddItem "MfgYear"
        .AddItem "Bureau"
        .AddItem "Mfg"
        .AddItem "AquPrice"
        .AddItem "Model"
        .AddItem "Misc"
        .AddItem "PermAssign"
        .AddItem "CurrAssign"
        .AddItem "StaTakeHome"
        .AddItem "TakeHome"
        .AddItem "Hours"
        .AddItem "CurOdometer"
        .AddItem "ReadDate"
        
    End With
    'rs.Close
   
    
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
    'rs.Close
    Set rs = Nothing
    closeConnection
    Unload Me
    frmVMD.Enabled = True
    
End Sub
 
Private Sub lstQuery_Click()
     Set rs = New ADODB.Recordset
     
    If lstQuery.ListIndex <> -1 Then
        strQuery = lstQuery.List(lstQuery.ListIndex)
        rs.CursorLocation = adUseClient
        
        strSQL = "SELECT DISTINCT * FROM Vehicle"
        'rs.Close
        
        rs.Open strSQL, DBCon, adOpenDynamic, adLockOptimistic
        
        With lstQueryResults
            .Clear
            Do While Not rs.EOF
                .AddItem Trim(rs.Fields(strQuery).Value) & ""
                rs.MoveNext
            Loop
        End With
    
    Else
        If (rs.BOF Or rs.EOF) Then
            MsgBox "No matches found; Please check your criteria", vbInformation + vbOKOnly, "Vehicle Query"
        End If
    End If
 
    
    
End Sub

[RobDog888]

The listbox's contents are to be used as the field to select or as a criteria to filter byu on a single field?

strSQL = "SELECT DISTINCT " & lstQuery.List(lstQuery.ListIndex) & " FROM Vehicle"

Or

strSQL = "SELECT DISTINCT Field1 FROM Vehicle WHERE Field1 = '" & lstQuery.List(lstQuery.ListIndex) & "';"

[cfd33]

Rob,

The first code works like a charm (funny thing is; I had that written like your code in the beginning but couldnt get mine to work and now I see why; I used '" & strquery & "' -- those little things (') get me all the time,lol)

I tried to write the If block for the IsNull, but didnt see a .skip anywhere?

With lstQueryResults
            .Clear
            Do While Not rs.EOF
                If IsNull(rs.Fields(strQuery).Value) Then
                    
                Else
                    .AddItem rs.Fields(strQuery).Value & ""
                    rs.MoveNext
                End If
            Loop
        End With

Does this look correct (with the exception of missing the .skip)?


LOL, I am a complete moron and I'm really tired; I just notice that you didnt write .skip, but Skip the additem. Man, I'm going to bed.

[RobDog888]

Not an actual skip method but by code like this...

With lstQueryResults
    .Clear
    Do While Not rs.EOF
        If Not IsNull(rs.Fields(strQuery).Value) Then
            .AddItem rs.Fields(strQuery).Value & ""
        End If
        rs.MoveNext
    Loop
End With

[cfd33]

Rob,

The code will take out most of the blank spaces in the listbox, but still leaves one blank at the very beginning.

[cfd33]

Got it!

Thanks for all of your help RobDog!