Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #9102

Re: Subtract two field in update statement

Show all headers | View raw

The more specific you can describe what you have, what is happening, what 
the result is, and what you expected the result to be, then the higher the 
probability someone here will be able to help you. I'll ask some questions, 
suggest some approaches, and perhaps I can speculate as to _where_ your 
problem is.

What specifically do you mean by "The field is updated but the result is not 
correct."?  Give us a few examples of the table data, and the result.

Did you try using the Immediate Window to print (?) the SQL you are trying 
to create?  Look at the final SQL -- it may not be what you expected.

When I copy your SQL calculation into the Immediate Window, modify it into a 
complete string by eliminating the concatenation marks (_) and moving the 
parts onto one line, then run it, here's what I get.

? "UPDATE [tblprjCost] INNER JOIN (tblActualsElementvise INNER JOIN 
tblProjInfo ON tblActualsElementvise.[Project Number] = " & 
"tblProjInfo.OracleProjNo) ON ([tblprjCost].[ProjectNo] = 
tblProjInfo.[ProjectNo])" & "AND ([tblprjCost].Category = 
tblActualsElementvise.[Expenditure Category]) " & "SET [tblprjCost].[ITD] = 
[tblActualsElementvise].[ITD Cost], [tblprjCost]." & Format(Vdate, "mmm") & 
" = " & "[tblprjCost].[ITD] - [tblprjCost].[ITDPrvMonth];"

UPDATE [tblprjCost] INNER JOIN (tblActualsElementvise INNER JOIN tblProjInfo 
ON tblActualsElementvise.[Project Number] = tblProjInfo.OracleProjNo) ON 
([tblprjCost].[ProjectNo] = tblProjInfo.[ProjectNo])AND 
([tblprjCost].Category = tblActualsElementvise.[Expenditure Category]) SET 
[tblprjCost].[ITD] = [tblActualsElementvise].[ITD Cost], [tblprjCost]. = 
[tblprjCost].[ITD] - [tblprjCost].[ITDPrvMonth];

* Note that the entire Format Function Call has disappeared. It appears to 
me that you may have a problem with punctuation (quote marks) in the area 
where you are Formatting the Vdate. Since Vdate is the same for every 
record, why do you not create the month string value and use that in your 
SQL equation, rather than working to force the proper SQL to re-create it 
for each record?

It is difficult for me to know what you _intend_, so I can't offer you a 
specific suggestion for correcting the problem. That would depend on how you 
expect to use the text value of the month.  I hesitate to speculate, but as 
it is, it would _appear_ that you intend to use it as the name of a field in 
[tblprjCost] and, to me, it would seem quite unusual for a properly 
normalized database table to have a field with the month's name as the field 
name.

Can you create a select Query in the Query Builder to do the joins, and 
display the values you are using as factors in your calculation (You will 
likely have to provide Vdate as a Constant for testing)?

By the way, did you start with a Query from the Query Builder, or did you 
just sit down and start writing SQL? It generally saves a lot of time and 
effort to use the Query Builder, and gives you opportunity for easily 
testing what you are doing.

 -- 
 Larry Linson
  Microsoft Office Access MVP
  Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

<naushadafaf@gmail.com> wrote in message 
news:edb98b48-00b0-445d-88a4-3679820dc18d@googlegroups.com...
Hi,

I have written an update statement to subtract the two field. This update 
statement is inner join with the three tables. After enter the date in input 
box the result should update the field as per the month. The field is 
updated but the result is not correct. The VBA code are as follows.

Dim mysql As String
Dim Vdate As String
Vdate = InputBox("Enter the Date in (mm/dd/yyyy) Format:", "Enter Date")

mysql = "UPDATE [tblprjCost] INNER JOIN (tblActualsElementvise INNER JOIN 
tblProjInfo ON tblActualsElementvise.[Project Number] = " & _
        "tblProjInfo.OracleProjNo) ON ([tblprjCost].[ProjectNo] = 
tblProjInfo.[ProjectNo])" & _
        "AND ([tblprjCost].Category = tblActualsElementvise.[Expenditure 
Category]) " & _
        "SET [tblprjCost].[ITD] = [tblActualsElementvise].[ITD Cost], 
[tblprjCost]." & Format(Vdate, "mmm") & " = " & _
        "[tblprjCost].[ITD] - [tblprjCost].[ITDPrvMonth];"

DoCmd.RunSQL mysql

Please help me to solve the problem.

Thanks in advance.

Haider 

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

Subtract two field in update statement naushadafaf@gmail.com - 2014-03-03 21:50 -0800
  Re: Subtract two field in update statement "Access Developer" <accdevel@gmail.com> - 2014-03-04 17:53 -0600

csiph-web