Part of the criteria for this update query is a calculation. Currently this figure 'liTotalCost' is made just once. This number needs to be calculated for each record. How do I integrate these calculations into the query? I am working with VB6, Access 2003. Thank you for your help.
Dim strSQL5 As String
Dim liTotalCost As Long
Dim liBalMD As Long
Dim liEstMD As Long
Dim liActMD As Long
liBalMD = (CDbl([A]) + CDbl([B]) + CDbl([C])
liEstMD = (CDbl([X]) + CDbl([Y]) + CDbl([Z])
If liBalMD < 0 Then
liActMD = (liEstMD + liBalMD)
liActMD = liEstMD
liTotalCost = (CDbl([ActMDCost]) - liActMD)
strSQL5 = "Update DISTINCTROW [ProjOverview] o Left Join (SELECT ProjID, SUM(Amount) AS SumOfAmount From JONProjJoins GROUP BY ProjID) s ON s.ProjID = o.IDNmbr Set [Status] = 'Complete', [Complete] = Now , [TotalServicesAct] = [TotalServicesEst] Where (([Status] = 'Funded') AND (" & liTotalCost & " > Nz(s.SumOfAmount, 0))) OR (([Status] = 'Funded') AND (" & liTotalCost & ") > ([TotalEstimate]))"