VBA Function

[antoncata]

1) Order by the Calculated field - Difficult to do this directly as "ORDER BY C" below :
SELECT
a,
b,
a + b AS C
FROM
mytable
ORDER BY a + b



In your case the ORDER BY will become pretty complicated if you try to replace all the aliases.

2) Access the calculated field in another query & then use the ORDER BY:

I have listened to advice, and made another query that is sorting.
It works perfectly!
Now I can go with application development.
Thank you for time lost in my favor.

Sincerely,
Catalin

[antoncata]

Hi Steve,

I've been looking at your database and have a couple of concerns.
You are using special characters, spaces and reserved words in your object names .

I have read and corrected as directed.
Since I developed with another colleague SQL version, I have not returned to the page in VBA.
But to close this issue I upgraded the version in VBA to do the same.

Public Sub CalculPret()
    
    Dim db As Database
    Dim rec As Recordset
    Dim PolCboV As String
    Dim PodCboV As String
    Dim strSQL As String
    Dim GrossWeight As Double
    Dim VolumeWeight As Double
    Dim CalcWeight As Double
    Dim CalcWeightScGw As Double
    Dim CalcPrice As Double
    Dim TotalPrice As Double
    
    PolCboV = [Forms]![DimensionsQry]![PolCbo]
    PodCboV = [Forms]![DimensionsQry]![PodCbo]
    
    strSQL = "SELECT Prices_List.ID, " & _
            "Prices_List.AgCODE, " & _
            "Prices_List.AGENT, " & _
            "Prices_List.PolC, " & _
            "Prices_List.POL, " & _
            "Prices_List.PodC, " & _
            "Prices_List.POD, " & _
            "Prices_List.IATA, " & _
            "Prices_List.AIRLINE, " & _
            "Prices_List.REVISE, " & _
            "Prices_List.[EXPIRY DATE], " & _
            "Prices_List.EXCHANGE, " & _
            "Prices_List.Mm, " & _
            "Prices_List.LT45, " & _
            "Prices_List.GT45, " & _
            "Prices_List.GT100, " & _
            "Prices_List.GT300, " & _
            "Prices_List.GT500, " & _
            "Prices_List.GT1000, " & _
            "Prices_List.FSC, " & _
            "Prices_List.SSC, " & _
            "Prices_List.ScGw, " & _
            "Prices_List.FREQUENCY, " & _
            "Prices_List.TT, " & _
            "Prices_List.Ts "
    strSQL = strSQL & " FROM Prices_List"
    strSQL = strSQL & " WHERE (((Prices_List.[PolC])='" & PolCboV & "') " & _
             "AND ((Prices_List.[PodC])='" & PodCboV & "'));"
    
    Set db = CurrentDb
    Set rec = db.OpenRecordset(strSQL)
    
        If rec.RecordCount = 0 Then
            rec.Close
            Exit Sub
        Else
                GrossWeight = [Forms]![DimensionsQry]![GW]
                VolumeWeight = [Forms]![DimensionsQry]![VW]
            
            If GrossWeight > VolumeWeight Then
                CalcWeight = GrossWeight
            Else
                CalcWeight = VolumeWeight
            End If
            rec.MoveFirst
                Do Until rec.EOF
                    Select Case CalcWeight
                      Case 1 To 44
                        CalcPrice = Nz(rec![LT45], 0)
                      Case 45 To 99
                        CalcPrice = Nz(rec![GT45], 0)
                      Case 100 To 299
                        CalcPrice = Nz(rec![GT100], 0)
                      Case 300 To 499
                        CalcPrice = Nz(rec![GT300], 0)
                      Case 500 To 999
                        CalcPrice = Nz(rec![GT500], 0)
                      Case Is >= 1000
                        CalcPrice = Nz(rec![GT1000], 0)
                    End Select
                    
                    If CalcPrice = 0 Then
                        TotalPrice = 0
                    Else
                        If CalcWeight = GrossWeight Then
                            CalcPrice = CalcPrice + rec!FSC + rec!SSC
                            TotalPrice = CalcPrice * CalcWeight
                        Else
                            If rec!ScGw = True Then
                                TotalPrice = (CalcPrice * CalcWeight) + ((rec!FSC + rec!SSC) * GrossWeight)
                            Else
                                TotalPrice = ((CalcPrice + rec!FSC + rec!SSC) * CalcWeight)
                            End If
                        End If
                    End If
                MsgBox rec!AGENT & " - " & TotalPrice & " " & rec!EXCHANGE & " " & rec!Airline
                rec.MoveNext
                Loop
        End If
    rec.Close
    
    End Sub

Now go both versions, and offers the same results.

Thanks for good advice.

Sincerely,
Catalin