Search Code does not work, anyone able to find the problem?

[Behedwin]

Hi

I need some help find a error in my code.
I have been banging my head against this for some time now.

I have a textbox that is called txtSearch
It is supposed to search in a listbox. So when the user typse in the textbox, the result filters down in the listbox. This code have worked many times but i can not figure out why this one is not working now.
Listbox is called: ListPicker

My SQL for the listbox is this when form is being loaded.

SELECT SelecBuss_Query.ElevFirstname, SelecBuss_Query.ElevLastname, SelecBuss_Query.Arskurs, SelecBuss_Query.BussElev_ID, SelecBuss_Query.Expression1
FROM SelecBuss_Query
GROUP BY SelecBuss_Query.ElevFirstname, SelecBuss_Query.ElevLastname, SelecBuss_Query.Arskurs, SelecBuss_Query.BussElev_ID, SelecBuss_Query.Expression1
ORDER BY SelecBuss_Query.Arskurs;

As you can see it is based on a Query
The Query looks like this:

SELECT BussElev_Table.ElevFirstname, BussElev_Table.ElevLastname, BussElev_Table.Arskurs, BussElev_Table.BussElev_ID, Max([Ansokningsdate] & " " & [kommentar]) AS Expression1
FROM BussElev_Table LEFT JOIN BussA_Table ON BussElev_Table.BussElev_ID = BussA_Table.BussElev_ID_SK
GROUP BY BussElev_Table.ElevFirstname, BussElev_Table.ElevLastname, BussElev_Table.Arskurs, BussElev_Table.BussElev_ID
ORDER BY BussElev_Table.Arskurs;

Then finaly i have the VBA code that runs the search.
It looks like this

Private Sub txtSearch_Change()
Application.Echo False

  Dim strSource As String
  
  
  
   strSource = "SELECT SelecBuss_Query.ElevFirstname, SelecBuss_Query.ElevLastname, SelecBuss_Query.Arskurs, SelecBuss_Query.BussElev_ID, SelecBuss_Query.Expression1 " & _
    "FROM SelecBuss_Query " & _
    "Where (SelecBuss_Query.ElevFirstname Like '*" & Me.txtSearch.Text & "*' " _
    & "Or SelecBuss_Query.ElevLastname Like '*" & Me.txtSearch.Text & "*' " _
    & "Or SelecBuss_Query.Arskurs Like '*" & Me.txtSearch.Text & "*' " _
    & "Or SelecBuss_Query.BussElev_ID Like '*" & Me.txtSearch.Text & "*' " _
    & "Or SelecBuss_Query.Expression1 Like '*" & Me.txtSearch.Text & "*') " _
    & "ORDER BY SelecBuss_Query.Arskurs; " _
    & "GROUP BY SelecBuss_Query.ElevFirstname, SelecBuss_Query.ElevLastname, SelecBuss_Query.Arskurs, SelecBuss_Query.BussElev_ID, SelecBuss_Query.Expression1 "

      Me.ListPicker.RowSource = strSource
      Me.ListPicker.Requery

Me!ListPicker = ""
Application.Echo True
End Sub

Anyone able to help me solve this.

I have other codes exaclty like this that do work.
But this one just cleares my listbox and it is empty.
I can not understand why.

[isladogs]

Paste the code into Sql view of a query, change to design view and see if it works.
If not remove all where filters and repeat.
Then restore the filters one at a time checking it still works,
When done paste the sql back into the vbe

You may all find this utility useful from my website http://www.mendipdatasystems.co.uk/s...ain/4594398120

[kevinjoseph]

I have different codes exaclty like this that do work. In any case, this one just cleares my listbox and it is unfilled. I can not comprehend why.

[isladogs]

No ... but you can systematically identify the issue like I suggested.

[June7]

ORDER BY clause should follow GROUP BY clause.

But why have GROUP BY since there are no aggregate functions? Try SELECT DISTINCT instead.