SQL statement help!

[jacko168]

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

[QVeen72]

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

[jacko168]

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??

[aktharshaik]

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

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

[jacko168]

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

[aktharshaik]

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

[aktharshaik]

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

[jacko168]

Hi Please find attached file.

Thanks

[aktharshaik]

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

[aktharshaik]

Also checkout this link once. may get some tips

http://www.msaccesstips.com/2008/05/selected-list-box-items-and-dynamic.shtml

[jacko168]

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