Hi Jeff
Thanks for your interest in helping me. I finally sorted out after many hours of heavy work.
I am not sure if is the best way, but it works.
Here is the code, maybe you see something I shouldn´t be doing (or a shorter, faster way to achieve it)
' x(1), x(2), x(3), x(4), x(5) are variables which represent the 5 different columns. It first did a search to see if any field of each column there was value. If NULL then the variable = "Use column" else stays empty.
If x(1) <> "" Then
massql1 = "[Global Buying Rates].Freight1, [Global Buying Rates].Freight6,"
sort1 = "+nz([Global Buying Rates].[Freight1])"
sort6 = "+nz([Global Buying Rates].[Freight6])"
End If
If x(2) <> "" Then
massql2 = " [Global Buying Rates].Freight2, [Global Buying Rates].Freight7,"
sort2 = "+nz([Global Buying Rates].[Freight2])"
sort7 = "+nz([Global Buying Rates].[Freight7])"
End If
If x(3) <> "" Then
massql3 = " [Global Buying Rates].Freight3, [Global Buying Rates].Freight8,"
sort3 = "+nz([Global Buying Rates].[Freight3])"
sort8 = "+nz([Global Buying Rates].[Freight8])"
End If
If x(4) <> "" Then
massql4 = " [Global Buying Rates].Freight4, [Global Buying Rates].Freight9,"
sort4 = "+nz([Global Buying Rates].[Freight4])"
sort9 = "+nz([Global Buying Rates].[Freight9])"
End If
If x(5) <> "" Then
massql5 = " [Global Buying Rates].Freight5, [Global Buying Rates].Freight10,"
sort5 = "+nz([Global Buying Rates].[Freight5])"
sort10 = "+nz([Global Buying Rates].[Freight10])"
End If
massql = massql1 & massql2 & massql3 & massql4 & massql5
sortby20total = "nz([20Freight])+nz([20BAF])" & sort1 & sort2 & sort3 & sort4 & sort5
sortby40total = "nz([40Freight])+nz([40BAF])" & sort6 & sort7 & sort8 & sort9 & sort10
sortby20total = sortby20total & "- ([Global Buying Rates].FAC * ([Global Buying Rates].[20Freight] / 100)) - [Global Buying Rates].Incentive "
sortby40total = sortby40total & "- ([Global Buying Rates].FAC * ([Global Buying Rates].[40Freight] / 100)) - [Global Buying Rates].Incentive "
sqlline = "SELECT [Global Buying Rates].ShippingID, [Global Buying Rates].ToDate, [Global Buying Rates].POL, [Global Buying Rates].POD, "
sqlline = sqlline & sortby20total & " AS 20Total," & sortby40total & " AS 40Total,"
sqlline = sqlline & "[Global Buying Rates].[20Freight], [Global Buying Rates].[40Freight], [Global Buying Rates].[20BAF], [Global Buying Rates].[40BAF],"
sqlline = sqlline & massql
sqlline = sqlline & "[Global Buying Rates].Commodity, [Global Buying Rates].Service, [Global Buying Rates].Frequency,[Global Buying Rates].FAC, [Global Buying Rates].Incentive INTO ExporttoExcel "
sqlline = sqlline & " FROM [Global Buying Rates] "
sqlline = sqlline & " WHERE ((([Global Buying Rates].RouteNo)='NAE') AND (([Global Buying Rates].Type)='F'))"
sqlline = sqlline & "ORDER BY [Global Buying Rates].POL, [Global Buying Rates].POD, " & sortby20total
DoCmd.RUNSQL sqlline