I am a beginner in Accsess 2007.
I have a accdb database . It's really only one table with several columns and several thousand rows.
I need to make a single change to the database.
I want to multiply by 1,73 the values in two columns called "Amp" and "Ixx" but only in those rows where the value of the third column "Napon" is "660". In the other rows the values must remain the same.
Is there a simple way that I do it.
Thanks in advance.
Zlatkodo

Use an Update query.

UPDATE Tablename SET [AMP] = [AMP]* 1.73, [Ixx] = [Ixx]*1.73 WHERE [Napon] = 660;

Change the tablename to what you have. Also test on copy of the table first.

JR

Please, a little more details for the querry-design .
Where to locate your code? Is this the whole code?
Zlatkodo

1. Open a new query
2. Don't Select any tables
3. Up in the left corner you see "SQL View", click on it
4. Paste my sql sting in the open window, just replace "Tablename" with the actual tablename
5. Swich to designview and see how the query is build.

JR

Or if you want to design it yourself:

1. Open a new query
2. Select the table you wish to update
3. Select the fields, AMP, Ixx and Napon
4. Change it to a Update Query
5. In the Query Designer's row "Update To" you type in
amp = amp*1.73 under the amp field and do the same for Ixx
ixx = ixx*1.73
6. In the criteria row for Napon, just type in 660

Done

JR

I tried on second way but I get strange results ( " 0" or "-1" ) on right places in database.
Zlatkodo

Instead:
[Amp] = [Amp]* 1.73
in "Update to" must be only:
[Amp]* 1,73 and then is OK. I must only find the way to round results in 1 decimal place.
Thanks for helping.
Zlatkodo

you can try:

Round([Amp]* 1,73),1)

I found that Round sometimes doesn't give you what you expect so I use this function sometimes (http://www.mvps.org/access/modules/mdl0054.htm).

Hi Bob and others,
If I use: "Round(([Amp]*1,73),1)" I get the error:
Microsoft Office Access can not update all the records in the update query.
.........due to a type conversion failure, ......
If I ignore this error I get blank fields.
I declare numbers in those columns as "general number".
What should I do ?
Zlatkodo

What's the datatype of the field? Also, you didn't put quotes around it like you show in your post, did you?

Data type is number, field size is double, format is general number and dec. places is auto.
Yes, I put exactly this: "Round(([Amp]*1,73),1)"
Without " I get the error.
Zlatkodo

NO QUOTES! The error is probably due to null fields. So you need to either set the criteria on that field (that is being updated) as

Is Not Null

or use:

Round((Nz([Amp],0)*1,73),1)

I have not found a way to rounding. I'll give up and do it manually.

Zlatkodo

dont do that - surely its worth a few minutes to understand the principle of an update query