Not displaying some VBA SQL criteria

Private Sub LBS(ocity As String, oprov As String, userinput As String)

  Dim sSQL As String, xcrit As String
   
    sSQL = "INSERT INTO [PROVINCE TO COUNTRY RATES] ([DESTIN CITY],"
    sSQL = sSQL & " [DESTIN PROVINCE], [MIN], LTL, 500, 1M, 2M, 5M, 10M, 20M)"
    sSQL = sSQL & " SELECT P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.[DESTIN PROVINCE],"
    sSQL = sSQL & " P2CSameTx65Table.SERVICE, P2CSameTx65Table.Class, [P2CSameTx65Table]![MIN]+[getDestinBeyond]![MIN] AS [MIN],"
    sSQL = sSQL & " [P2CSameTx65Table]![LTL]+[getDestinBeyond]![LTL] AS LTL,"
    sSQL = sSQL & " [P2CSameTx65Table]![500]+[getDestinBeyond]![500] AS [500],"
    sSQL = sSQL & " [P2CSameTx65Table]![1M]+[getDestinBeyond]![1M] AS [1M],"
    sSQL = sSQL & " [P2CSameTx65Table]![2M]+[getDestinBeyond]![2M] AS [2M],"
    sSQL = sSQL & " [P2CSameTx65Table]![5M]+[getDestinBeyond]![5M] AS [5M],"
    sSQL = sSQL & " [P2CSameTx65Table]![10M]+[getDestinBeyond]![10M] AS [10M],"
    sSQL = sSQL & " [P2CSameTx65Table]![20M]+[getDestinBeyond]![20M] AS [20M]"
    sSQL = sSQL & " FROM getDestinBeyond INNER JOIN P2CSameTx65Table ON getDestinBeyond.[DESTIN CITY] = P2CSameTx65Table.[DESTIN CITY]"
    sSQL = sSQL & " WHERE [P2CSameTx65Table].TCOMBO =  [getDestinBeyond].[TCOMBI] & getDestinBeyond.[TCOMBI] "
    sSQL = sSQL & " AND getDestinBeyond.TCOMBI = " & Chr(34) & userinput & Chr(34) & " And P2CSameTx65Table.[Class] = " & [Forms]![Prov2Country]![classcombo] & ""
    
    sSQL = sSQL & " ORDER BY P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.Class;"

    
    CurrentDb.Execute sSQL, dbFailOnError

End Sub

Open in new window


Hi,

I have the above function which apends to a table. Now I want it to sort using class and order using class but I do not want to display the 'Class' column (or hide it)

Could you please let me know how to do this via VBA?

Thanks!
LVL 13
Shanan212Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
    sSQL = "INSERT INTO [PROVINCE TO COUNTRY RATES] ([DESTIN CITY],"
    sSQL = sSQL & " [DESTIN PROVINCE], [MIN], LTL, 500, 1M, 2M, 5M, 10M, 20M)"
    sSQL = sSQL & " SELECT P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.[DESTIN PROVINCE],"
    sSQL = sSQL & " [P2CSameTx65Table]![MIN]+[getDestinBeyond]![MIN] AS [MIN],"
    sSQL = sSQL & " [P2CSameTx65Table]![LTL]+[getDestinBeyond]![LTL] AS LTL,"
    sSQL = sSQL & " [P2CSameTx65Table]![500]+[getDestinBeyond]![500] AS [500],"
    sSQL = sSQL & " [P2CSameTx65Table]![1M]+[getDestinBeyond]![1M] AS [1M],"
    sSQL = sSQL & " [P2CSameTx65Table]![2M]+[getDestinBeyond]![2M] AS [2M],"
    sSQL = sSQL & " [P2CSameTx65Table]![5M]+[getDestinBeyond]![5M] AS [5M],"
    sSQL = sSQL & " [P2CSameTx65Table]![10M]+[getDestinBeyond]![10M] AS [10M],"
    sSQL = sSQL & " [P2CSameTx65Table]![20M]+[getDestinBeyond]![20M] AS [20M]"
    sSQL = sSQL & " FROM getDestinBeyond INNER JOIN P2CSameTx65Table ON getDestinBeyond.[DESTIN CITY] = P2CSameTx65Table.[DESTIN CITY]"
    sSQL = sSQL & " WHERE [P2CSameTx65Table].TCOMBO =  [getDestinBeyond].[TCOMBI] & getDestinBeyond.[TCOMBI] "
    sSQL = sSQL & " AND getDestinBeyond.TCOMBI = " & Chr(34) & userinput & Chr(34) & " And P2CSameTx65Table.[Class] = " & [Forms]![Prov2Country]![classcombo] & ""
    
    sSQL = sSQL & " ORDER BY P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.Class;"

Open in new window

0
 
Dale FyeConnect With a Mentor Commented:
just remove the reference to P2CSameTx65Table.Class from line #8.

If you ONLY want to sort by class then change line 20 to:

    sSQL = sSQL & " ORDER BY P2CSameTx65Table.Class;"

0
 
Shanan212Author Commented:
Thanks!
0
All Courses

From novice to tech pro — start learning today.