Change in StrSQL with condition (1 Viewer)

[syedadnan]

regards i have a Sql below i want it to be little bit conditional ;


Private Sub Form_Close()
Dim StrSql As String

StrSql = "UPDATE (SELECT Purchase.Product, Purchase.PQty, Purchase.PRunTot "
StrSql = StrSql & "FROM Purchase ORDER BY Purchase.Product, Purchase.PDate, "
StrSql = StrSql & "Purchase.BatchNo) AS PurchaseSorted SET PurchaseSorted.PRunTot "
StrSql = StrSql & "= UpdateRunSum([product],[PQty]);"



BeginRunSumUpdate

CurrentDb.Execute StrSql

End Sub


i want a condition that if PQTY > "0.000" then it should sum on Product and PQTY else if PQTY =0.000 and weight >0.000 the it should sum Product and weight

 

[JHB]

Is UpdateRunSum a UDF, then show the code?

 

[syedadnan]

Is UpdateRunSum a UDF, then show the code?

Option Compare Database

Global ToLineSum As Double
Global Pr As Long

Public Function BeginRunSumUpdate()
ToLineSum = 0
Pr = 0
End Function


Public Function UpdateRunSum(Prd As Double, ivalue) As Double
If Pr = Prd Then
ToLineSum = ToLineSum + ivalue
UpdateRunSum = ToLineSum
Else
ToLineSum = 0
Pr = Prd
ToLineSum = ivalue
UpdateRunSum = ToLineSum
End If
End Function

 

[JHB]

I would include the weight in the parameter for the UDF and do the test in the UDF.

UpdateRunSum([product],[PQty],weight);"