Hi All,

Kind of clueless here,

I want to order by company names but in the table they exist as foreign keys and ultimately numbers.

I am considering this but I am wondering if it would work?



Private Sub Form_Open(Cancel As Integer)
Dim Strsql As String

strSQL = "SELECT tbl_ShipOrders.ship_order_id, tbl_ShipOrders.receiver_id, tbl_ShipOrders.sender_id, Sender.company AS Sender_name, Receiver.company AS Receiver_name, " & _
"FROM (tbl_ShipOrders " & _
"INNER JOIN tbl_Customers AS Sender " & _
"ON Sender.custid = tbl_ShipOrders.Sender_ID) " & _
"INNER JOIN tbl_Customers AS Receiver " & _
"ON Receiver.custid = tbl_ShipOrders.Receiver_ID " & _

strOrder = Request.QueryString("order")
strDirection = UCase(Request.QueryString("direction"))

If strOrder = "" Then
strOrder = "Idate"
strDirection = "ASC"
End If

Select Case strOrder

Case "ship_id":
Strsql = Strsql & " ORDER BY ship_order_id " & strDirection
Case "sender":
Strsql = Strsql & " ORDER BY LOWER(Sender_Name)" & strDirection
Case "Receiver":
Strsql = Strsql & " ORDER BY LOWER(Receiver_Name) " & strDirection

End Sub


Actually, I have taken this from an asp form so some parts obviously won't work, not quite sure how to adapt it to VBA

Hello ben,

If you don't need it to be variably ordered and there will be no parameters, I'd take the query out of VBA and store it as an actual query, where updating it will be much easier. With that, you could use the following:
After it's defined, use DAO with the current database to open it into a recordset and process.

Regards,

Ax

Thanks Ax

I managed it in the end, thanks for your reply

Ben