Adding Fields together..

[Breecy via AccessMonster.com]

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!

 

[Bob Barrows]

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?

 

[KARL DEWEY]

Post the SQL of your update query.

 

[Breecy via 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;

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!

 

[Breecy via AccessMonster.com]

Yes, because that becomes the column name.

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?

 

[Breecy via 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.

 

[Bob Barrows]

Yes, because that becomes the column name.

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

 

[Bob Barrows]

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.

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

 

[Bob Barrows]

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.

 

[KARL DEWEY]

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.

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;

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!

 

[Breecy via AccessMonster.com]

I had previously added the column which contained no data.

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!

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.

 

[Breecy via AccessMonster.com]

I had previously added the column which contained no data.

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!

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.

 

[Breecy via AccessMonster.com]

I had previously added the column which contained no data.

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!

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.