UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Update Queries, How Do They Work?, Access 2016    
 
   
airplayne
post Apr 7 2020, 09:09 PM
Post#1



Posts: 603
Joined: 17-February 09
From: West Texas


I am just trying to do something simple. I want to add three values together and put them in a third field. Seems simple, right? But for some reason I cannot get this to work. Here is an example of what I using:

CODE
UPDATE tblWellTests SET tblWellTests.TotalFluidRate = [OilRate]+[WaterRate];


There are values in both the OilRate and WaterRate fields, but when I test the query all I am getting is zeroes. They are all number fields.
Go to the top of the page
 
MadPiet
post Apr 7 2020, 09:56 PM
Post#2



Posts: 3,704
Joined: 27-February 09



Where are the values coming from?

[OilRate]+[WaterRate]…

Are they coming from the same table, or from a form? If they're coming from a form, you'd need to use

Forms!MyForm![OilRate] + Forms!MyForm![WaterRate]
Go to the top of the page
 
airplayne
post Apr 7 2020, 09:57 PM
Post#3



Posts: 603
Joined: 17-February 09
From: West Texas


They are coming from the same table. Tried specifically calling them by the table name and field name and still came up zeroes. Also tried removing the default value in the table and now I just get blanks.
Go to the top of the page
 
kfield7
post Apr 7 2020, 10:02 PM
Post#4



Posts: 1,068
Joined: 12-November 03
From: Iowa Lot


Try building with a SELECT query first, to see the results.
Also, why even do this (write the result back to the table)? A SELECT query can always produce the resulting field.
Go to the top of the page
 
airplayne
post Apr 7 2020, 10:49 PM
Post#5



Posts: 603
Joined: 17-February 09
From: West Texas


I had tried building it in a select query first where I just put a function together adding the fields together that I wanted which seemed to work just fine, it was just when I converted to an update query things went south.

However, I was able to go back to my original solution which was to append the information directly into the table from the datasource I was using. Somehow the way the field was setup was causing problems, so I deleted it from the table and added a new field. Everything works fine now.
Go to the top of the page
 
tina t
post Apr 8 2020, 12:31 AM
Post#6



Posts: 6,589
Joined: 11-November 10
From: SoCal, USA


generally speaking, you shouldn't store calculated values in a table. as kfield said, a calculated field in a query (or form, or report) would provide the sum value at runtime, with none of the disadvantages of storing calculated values as hard data.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th May 2020 - 10:30 PM