Solved

VB6 Access 2003 SQL Update Statement

Posted on 2011-09-07
6
282 Views
Last Modified: 2012-06-22
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

0
Comment
Question by:KimD2
  • 3
  • 3
6 Comments
 
LVL 11

Accepted Solution

by:
kbirecki earned 500 total points
ID: 36496158
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
 

Author Comment

by:KimD2
ID: 36496519
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
 

Author Comment

by:KimD2
ID: 36496811
Changing the fields to Double corrected the Type Mismatch error

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

Open in new window

0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Closing Comment

by:KimD2
ID: 36496814
Worked perfectly -- thank you
0
 
LVL 11

Expert Comment

by:kbirecki
ID: 36496831
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
 
LVL 11

Expert Comment

by:kbirecki
ID: 36496845
OK, I see you got it working, just as I was posting.  Happy to help.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question