Solved

VB6 Access 2003 SQL Update Statement

Posted on 2011-09-07
6
280 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now