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

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

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

.

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

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

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

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

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

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

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

.