VB6 Access 2003 SQL Update Statement

One of the conditions on this SQL Update statement depends on a total that must be found first.  How can I find my "liTotal" and integrate it with the update statement?  Thank you in advance for your help.
Dim strSQL As String
Dim liTotal As Long
Dim liBalance As Long
Dim liEstimate As Long
Dim liActual As Long

liBalance = ([A] + [B] + [C])
liEstimate = ([X] + [Y] + [Z])

If liBalance < 0 Then
    liActual = (liEstimate + liBalance)
Else
    liActual = liEstimate
End If

liTotal = ([PreviouslySavedCost] - liActual)

strSQL = "Update [ProjOverview] Set [Status] = 'Complete', [Complete] = Now, [TotalServicesAct] = [TotalServicesEst] Where (([Status] = 'Funded') AND (liTotal > 0) AND ((SELECT Sum([JONProjJoins].[Amount]) From [ProjOverview] LEFT JOIN [JONProjJoins] ON [ProjOverview].[IDNmbr] = [JONProjJoins].[ProjID] GROUP BY [JONProjJoins].[ProjID]) <= 0))"
DoCmd.RunSQL strSQL

Open in new window

KimD2Asked:
Who is Participating?
 
kbireckiConnect With a Mentor Commented:
You would basically embed it in the string like this:

strSQL = "Update [ProjOverview] Set [Status] = 'Complete', [Complete] = Now, [TotalServicesAct] = [TotalServicesEst] Where (([Status] = 'Funded') AND (" & liTotal & " > 0) AND ((SELECT Sum([JONProjJoins].[Amount]) From [ProjOverview] LEFT JOIN [JONProjJoins] ON [ProjOverview].[IDNmbr] = [JONProjJoins].[ProjID] GROUP BY [JONProjJoins].[ProjID]) <= 0))"

Open in new window


The portion that changes is:

     liTotal

...becomes...

     " & liTotal & "

...so you are concatenating the first part of the string before liTotal with the actual liTotal value, along with the remainder portion of the string.
0
 
KimD2Author Commented:
Thank you.  I am getting the error "Type Mismatch" on my first line (liBalance) where the variable is set to long.  In the Access 2003 database the fields are Currency.  I've tried setting the variables to integer, currency and double -- all give me the same error.  What am I missing?  
0
 
KimD2Author Commented:
Changing the fields to Double corrected the Type Mismatch error

liBalance = (CDbl([A]) +CDbl([B]) +CDbl([C]))

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
KimD2Author Commented:
Worked perfectly -- thank you
0
 
kbireckiCommented:
Are
[A], [B], and [C]

Open in new window

the obfuscated field names?  You don't show a recordsource here.

Try setting your field types to Number (Long Integers).
You could also set the right side of your equation to
(CLng([A]) + CLng([B]) + CLng([C]))

Open in new window


I assume you may have the same problem with the next line in your code, so check those fields as well.
0
 
kbireckiCommented:
OK, I see you got it working, just as I was posting.  Happy to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.