Solved

VB6 Access 2003 SQL Update Statement

Posted on 2011-09-07
6
279 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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

19 Experts available now in Live!

Get 1:1 Help Now