divide by 100

[SITCFanTN]

I have a table with 50,000 rows. The value in the col are currency and all
need to be divided by 100 to show the correct currency amount. The column
name is "Payment". I'm thinking this is easy but I'm just not able to figure
it out. Below is an example from my table. Thank you in advance for your
help.

$34,200.00 should show as 342.00
$87,700.00 should show as 877.00

 

[Jerry Whittle]

MAKE A BACKUP FIRST!!!!

UPDATE TableName
SET [TableName].[Payment] = [TableName].[Payment] / 100 ;

 

[John W. Vinson]

I have a table with 50,000 rows. The value in the col are currency and all
need to be divided by 100 to show the correct currency amount. The column
name is "Payment". I'm thinking this is easy but I'm just not able to figure
it out. Below is an example from my table. Thank you in advance for your
help.

$34,200.00 should show as 342.00
$87,700.00 should show as 877.00

Do you want to SHOW $34,200.00 as 342.00, say on a form or report?
Or do you want to permanently and irrevokable (well other than by running
another update query to multiply by 100) CHANGE the stored value in the table?

Either is easy to do - but they're different tasks!

 

[SITCFanTN]

CHANGE the stored value in the table is exactly what I need to do. Thanks so
much for your help....I'm anxious to hear how to do this.

 

[John W. Vinson]

CHANGE the stored value in the table is exactly what I need to do. Thanks so
much for your help....I'm anxious to hear how to do this.

Read Jerry's reply in this thread. If you can't see it... he said (and I
quote)

MAKE A BACKUP FIRST!!!!

UPDATE TableName
SET [TableName].[Payment] = [TableName].[Payment] / 100 ;


using your own table and fieldnames of course.

 

[SITCFanTN]

Where do I put this code, is it a format or a query? Thanks again

CHANGE the stored value in the table is exactly what I need to do. Thanks so
much for your help....I'm anxious to hear how to do this.

Read Jerry's reply in this thread. If you can't see it... he said (and I
quote)

MAKE A BACKUP FIRST!!!!

UPDATE TableName
SET [TableName].[Payment] = [TableName].[Payment] / 100 ;


using your own table and fieldnames of course.

 

[John W. Vinson]

Where do I put this code, is it a format or a query? Thanks again

It is the SQL view of a Query. Open a new query; select View... SQL; copy and
paste, and edit table and fieldnames as needed.

Or, create the query in the query grid based on your table. Select the field
(and any criteria fields if you don't want to update all the records). Change
it to an Update query using the Query Type tool in the toolbar, or the Query
menu option. Put

[Payment] / 100

on the Update To row under [Payment]; do include the square brackets or Access
will think you're trying to divide the text string "Payment" by 100 and will
complain.

Run the query by cliciking the ! icon.