If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Adding Fields together..
Guys this is probably really simple, but I can't see what I am doing wrong.
This is all done in the query view in MS Access '02. I am trying to add together three columns to populate a third column: tot: nz([Fi_F],0)+nz([Fi_R],0)+nz([Fi_B],0) When I run this as a select query it works just fine. The problem comes in when I try and do an update query. These columns in the same table that I am trying to update. Does that make a difference? The column I am updating is Fi_Totals. When I run the query, it tells me that the field names are not correct, even though I built the query in access and didn't type out the names. Exact Error: 'nz([Fi_F],0)+nz([Fi_R],0)+nz([Fi_B],0)' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long. What am I missing.... Thanks in advance! -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
Adding Fields together..
Breecy via AccessMonster.com wrote:
Guys this is probably really simple, but I can't see what I am doing wrong. This is all done in the query view in MS Access '02. I am trying to add together three columns to populate a third column: tot: nz([Fi_F],0)+nz([Fi_R],0)+nz([Fi_B],0) When I run this as a select query it works just fine. The problem comes in when I try and do an update query. These columns in the same table that I am trying to update. Does that make a difference? The column I am updating is Fi_Totals. When I run the query, it tells me that the field names are not correct, even though I built the query in access and didn't type out the names. Exact Error: 'nz([Fi_F],0)+nz([Fi_R],0)+nz([Fi_B],0)' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long. Show us the sql statement (switch your query to SQL View) ... you're not leaving the "tot:" in the expression are you? -- HTH, Bob Barrows |
#3
|
|||
|
|||
Adding Fields together..
Post the SQL of your update query.
-- Build a little, test a little. "Breecy via AccessMonster.com" wrote: Guys this is probably really simple, but I can't see what I am doing wrong. This is all done in the query view in MS Access '02. I am trying to add together three columns to populate a third column: tot: nz([Fi_F],0)+nz([Fi_R],0)+nz([Fi_B],0) When I run this as a select query it works just fine. The problem comes in when I try and do an update query. These columns in the same table that I am trying to update. Does that make a difference? The column I am updating is Fi_Totals. When I run the query, it tells me that the field names are not correct, even though I built the query in access and didn't type out the names. Exact Error: 'nz([Fi_F],0)+nz([Fi_R],0)+nz([Fi_B],0)' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long. What am I missing.... Thanks in advance! -- Message posted via http://www.accessmonster.com . |
#4
|
|||
|
|||
Adding Fields together..
I can't because of the error for the update query. here it is for the select
query: SELECT nz([Fi_F],0)+nz([Fi_R],0)+nz([Fi_B],0) AS tot FROM tblComparisons; KARL DEWEY wrote: Post the SQL of your update query. Guys this is probably really simple, but I can't see what I am doing wrong. This is all done in the query view in MS Access '02. [quoted text clipped - 16 lines] What am I missing.... Thanks in advance! -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
Adding Fields together..
Yes, because that becomes the column name.
Bob Barrows wrote: Guys this is probably really simple, but I can't see what I am doing wrong. This is all done in the query view in MS Access '02. [quoted text clipped - 13 lines] name. Make sure that it does not include invalid characters or punctuation and that it is not too long. Show us the sql statement (switch your query to SQL View) ... you're not leaving the "tot:" in the expression are you? -- Message posted via http://www.accessmonster.com |
#6
|
|||
|
|||
Adding Fields together..
so here is what I did. I took the three calculated fields that I wanted and
created a new table. Then I did a table update into the columns that I wanted with the new table. it worked. I think mine didn't work because it was into the same table. If I am wrong on that, please some one let me know. Thanks. Breecy wrote: Guys this is probably really simple, but I can't see what I am doing wrong. This is all done in the query view in MS Access '02. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200912/1 |
#7
|
|||
|
|||
Adding Fields together..
Breecy via AccessMonster.com wrote:
Yes, because that becomes the column name. Bob Barrows wrote: Guys this is probably really simple, but I can't see what I am doing wrong. This is all done in the query view in MS Access '02. [quoted text clipped - 13 lines] name. Make sure that it does not include invalid characters or punctuation and that it is not too long. Show us the sql statement (switch your query to SQL View) ... you're not leaving the "tot:" in the expression are you? No, the column name already exists in your target table. You don't define a column name when you're updating a column -- HTH, Bob Barrows |
#8
|
|||
|
|||
Adding Fields together..
Breecy via AccessMonster.com wrote:
so here is what I did. I took the three calculated fields that I wanted and created a new table. Then I did a table update into the columns that I wanted with the new table. it worked. I think mine didn't work because it was into the same table. If I am wrong on that, please some one let me know. Thanks. Breecy wrote: Guys this is probably really simple, but I can't see what I am doing wrong. This is all done in the query view in MS Access '02. The sql should look like: update table set existingcolumnname = nz([Fi_F],0)+nz([Fi_R],0)+nz([Fi_B],0) Modify this so it has your table and column names and paste it into the SQL View. Then switch to Design View to see what you should have done in the query builder grid -- HTH, Bob Barrows |
#9
|
|||
|
|||
Adding Fields together..
Breecy via AccessMonster.com wrote:
Guys this is probably really simple, but I can't see what I am doing wrong. This is all done in the query view in MS Access '02. I am trying to add together three columns to populate a third column: tot: nz([Fi_F],0)+nz([Fi_R],0)+nz([Fi_B],0) When I run this as a select query it works just fine. The problem comes in when I try and do an update query. These columns in the same table that I am trying to update. Does that make a difference? The column I am updating is Fi_Totals. When I run the query, it tells me that the field names are not correct, even though I built the query in access and didn't type out the names. Exact Error: 'nz([Fi_F],0)+nz([Fi_R],0)+nz([Fi_B],0)' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long. What am I missing.... Thanks in advance! Oh wait, are you expecting an Update query to add a new column to your table?? Sorry, that will not happen. You have to add the empty column first. Update statements can only update existing data. -- HTH, Bob Barrows |
#10
|
|||
|
|||
Adding Fields together..
You have to test each one to find error or each combination --
SELECT nz([Fi_F],0) AS tot FROM tblComparisons; SELECT nz([Fi_R],0) AS tot FROM tblComparisons; SELECT nz([Fi_F],0)+nz([Fi_R],0) AS tot FROM tblComparisons; etc. -- Build a little, test a little. "Breecy via AccessMonster.com" wrote: I can't because of the error for the update query. here it is for the select query: SELECT nz([Fi_F],0)+nz([Fi_R],0)+nz([Fi_B],0) AS tot FROM tblComparisons; KARL DEWEY wrote: Post the SQL of your update query. Guys this is probably really simple, but I can't see what I am doing wrong. This is all done in the query view in MS Access '02. [quoted text clipped - 16 lines] What am I missing.... Thanks in advance! -- Message posted via http://www.accessmonster.com . |
|
Thread Tools | |
Display Modes | |
|
|