Hi Steve,
Originally Posted by
ssanfu
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.
Code:
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