Sorting

[JDU]

I have a field in a table with data type text with values such as BB-1, BB-2, BB-3 etc. I am presenting these values in a combo box in a form. I would like to sort it. Therefore this is the code I wrote. The problem is that the SQL statement is being evaluated as a parameter query. As long as I supply the correct value for the parameter query it works great. How can I modify it so that it recognizes the value of x in the SQL statement and not pop up a parameter query input box. Thanks.

cboSOP.RowSource = "Select distinct txtProcedureNo from tblProcedures where txtGroup=" & Chr(34) & cboGroup & Chr(34)

If cboSOP.ListCount <> 0 Then
strSOP = cboSOP.ItemData(0)

For i = 1 To Len(strSOP) Step 1
If Mid(strSOP, i, 1) = &quot;-&quot; Then
Exit For
End If
Next
End If

x = i + 1

cboSOP.RowSource = &quot;Select txtProcedureNo from tblProcedures where txtGroup=&quot; & Chr(34) & cboGroup & Chr(34) _
& &quot;order by CInt(Mid(txtProcedureNo, x))&quot;

cboSOP.Requery

 

[Nikita6003]

Hiya,

try changing to:

  cboSOP.RowSource = "Select txtProcedureNo from tblProcedures where txtGroup=" & _
  Chr(34) & cboGroup & Chr(34)  & "order by CInt(Mid(txtProcedureNo, " & x & "))"

You're making the variable x part of your SQL instead of its value

HTH

Cheers
Nikki *<|

 

[JDU]

THANK YOU VERY MUCH. THAT MAKES SENSE