I have a table with 4 columns: yr1(type:text), yr2(text), yr3(text), avg(number); I tried to calculate the value of "avg" by using the following query:
    UPDATE tbl_test SET avg = Val((yr1 + yr2 + yr3)/3);
The Access database couldn't update all the records. Does anyone tell me why I failed? Thanks so much!


flyfish

__________________
flyfish

It looks like you are trying to calculate a number using text values, which would result in a type mismatch error.

You need field types like this:
yr1(number), yr2(number), yr3(number), and then store the average in a query. At least it is a number too.

You should be able to convert them all to numbers.

mmcdonal

I tried to change the field type into number, but I got a error message reminding me I will lost some my data in corresponding field.

flyfish

Is there a chance that some of of the values might contains alphabet characters? If so, that will cause errors no matter what you do. You could make a copy of the table, and then try the conversion, and see if there is any data that is actually lost. Or if you want to get fancy, you could export the table to Excel, make sure that all the values are numeric and then import them into a new table in Access.

Mike

Mike
EchoVue.com

The message that you will/may lose data if you convert the field type is a default message. You will get that message whether you will actually lose data or not.

Sort the field and see if you get letters coming to the top (z to a). If not, then convert and don't worry about the lost data message. Or take echovue's advice about a backup table.



mmcdonal