How to divide a column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello
I have an access table that has account numbers ex 45208 (all are four
digits.. I want to change to account number to ex 4520.8 I need to break
up the accounts numbers with the period. Is there a way to do this in a
query? I have over a 100,000 rows of data to change. Any help will be
greatly appreciated. Thank you.
 
Hello
I have an access table that has account numbers ex 45208 (all are four
digits.. I want to change to account number to ex 4520.8 I need to break
up the accounts numbers with the period. Is there a way to do this in a
query? I have over a 100,000 rows of data to change. Any help will be
greatly appreciated. Thank you.
Click to expand...

Gee, I count 45208 as Five digits. :-(
To just display the value in a query?
NewValue:[Fieldname]/10

To permanently change the value in your table, you can use an Update
Query:
Update YourTable Set YourTable.[FieldName] = [FieldName]/10;
 
Hi Mac,

Account 'numbers', Zip codes and the like should be stored in text
fields (number fields are for values on which one might do
arithmetic). To insert your period, use an update query: update the
field in question to

Left ([FFF], Len([FFF])-1) & "." & Right([FFF], 1)

where FFF is the name of the field.
 
Hello
I have an access table that has account numbers ex 45208 (all are four
digits.. I want to change to account number to ex 4520.8 I need to break
up the accounts numbers with the period. Is there a way to do this in a
query? I have over a 100,000 rows of data to change. Any help will be
greatly appreciated. Thank you.
Click to expand...

Is the datatype Number or Text? If number, is it the default Long Integer
(which will NOT support decimal places), or a datatype such as Currency,
Decimal, Float or Double which will?

Account numbers should be in a Text field; you'll never be doing math with
them after all. If the field is already Text (and at least 6 characters in
length), run an Update query updating the field to

Left([field], 4) & "." & Right([field], 1)

If it's Number of any description, create a new Text field; update it to the
number field; and process as above.


John W. Vinson [MVP]
 
Back
Top