Solved

VB6 Access 2003 SQL Update Statement

Posted on 2011-09-07
6
283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Copy data to New Year 9 33
IF SQL Query 12 29
SSRS Page Header from Group Data 2 27
8 hour Continual coverage based on Time in and Time Out 9 21
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

733 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