Hi there,

I pulling my hair out with a SQL statement I am using for a query in Access.

I have a list box which pulls data from my table. I am using the list box to allow the user to select a record which will then be pulled up in detail in the main form. I have a search text box which refines the records. However I want to also use a combo box to sort the records too. See my statement below.

In ORDER BY, if I manually type a field, it sorts fine. But when I try and insert the combo box [sort] value from the form, nothing happens...
--------

SELECT tblMain.ID, tblMain.Type, tblMain.Site, tblMain.Room, tblMain.Area, *
FROM tblMain
WHERE (((tblMain.Type) Like "*" & Forms!frmMain!Search2 & "*")) Or (((tblMain.Site) Like "*" & Forms!frmMain!Search2 & "*")) Or (((tblMain.Room) Like "*" & Forms!frmMain!Search2 & "*")) Or (((tblMain.Area) Like "*" & Forms!frmMain!Search2 & "*"))
ORDER BY '& [Forms]![frmMain]![sort] &';
--------

Any help would be appreciated

Hi,

Remove the Single Quotes around Combo selection..

dim strSQL As String 
strSQL = "SELECT tblMain.ID, tblMain.Type, tblMain.Site, " _
  & " tblMain.Room, tblMain.Area, * FROM tblMain WHERE " _
& " (((tblMain.Type) Like "*" & Forms!frmMain!Search2 & "*")) Or " _
& " (((tblMain.Site) Like "*" & Forms!frmMain!Search2 & "*")) Or " _
& "(((tblMain.Room) Like "*" & Forms!frmMain!Search2 & "*")) Or " _
& " (((tblMain.Area) Like "*" & Forms!frmMain!Search2 & "*")) " _
& " ORDER BY " & [Forms]![frmMain]![sort]  & " ; "

Regards
Veena

Hi,

Remove the Single Quotes around Combo selection..

dim strSQL As String 
strSQL = "SELECT tblMain.ID, tblMain.Type, tblMain.Site, " _
  & " tblMain.Room, tblMain.Area, * FROM tblMain WHERE " _
& " (((tblMain.Type) Like "*" & Forms!frmMain!Search2 & "*")) Or " _
& " (((tblMain.Site) Like "*" & Forms!frmMain!Search2 & "*")) Or " _
& "(((tblMain.Room) Like "*" & Forms!frmMain!Search2 & "*")) Or " _
& " (((tblMain.Area) Like "*" & Forms!frmMain!Search2 & "*")) " _
& " ORDER BY " & [Forms]![frmMain]![sort]  & " ; "

Regards
Veena

Thanks for this. I tried it and it still wont sort the list box with the combo. Do you think I need to convert it to a string or add my table to the statement??

Is it in VB or MSAccess forms u r writing the code?

Please post the entire procedure which is not giving u the result.

I am writing the SQL into a MS Access Query using SQL view. I have included the code from the form. As you can see there are two search methods, a text box [Search] and the combo [sort]. I have also tried passing the combo text into a variable which is passed to the SQL statement via another text box. This works for the text search ok.

I hope this is clear. I can email the database if this helps??

Thanks.

Private Sub ClearIt_Click()
On Error GoTo Err_ClearIt

Me.Search = ""
 Me.Search2 = ""
  Me.quicksearch.Requery
   Me.quicksearch.SetFocus

Exit_ClearIt_Click:
    Exit Sub

Err_ClearIt:
    MsgBox Err.Description
    Resume Exit_ClearIt_Click
End Sub





Private Sub QuickSearch_AfterUpdate()
    Dim rs As Object

    Set rs = Me.RecordsetClone
    rs.FindFirst "[ID] = " & Str(Me![quicksearch])
    Me.Bookmark = rs.Bookmark
End Sub


Private Sub Search_Change()
Dim vSearchString As String

 vSearchString = Search.Text
 Search2.Value = vSearchString
 Me.quicksearch.Requery

End Sub



Private Sub sort_AfterUpdate()
Dim vSearchString As String

 vSearchString = sort.Text
 sort_text.Value = vSearchString
 Me.quicksearch.Requery
 
End Sub

Not much clear with ur exact requirement. If u don't mind, plz upload the DB here.

Also i am unable to have a picture of your form and your exact requirement.
ZIP the file and Upload

Hi Please find attached file.

Thanks

Hi! At Last came up with some solution. Try this. Checkout the Attachment.

Wow, thankyou so much for all your help. Its works great. I'll check out that link as well.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.