How remove decimal and 2 trailing zeros from multiple values in fi

P

Phil

Hello,

I have a table that had values that were NUMERIC (eg. 2506.00) and have now
been converted to TEXT properties. Now I need to remove the decimal and the
2 trailing zeros. The field name is Group_Index. The table is called
Parcel_Info. Also, in case you need to know, the values are not all 4
digits. They actually start at 1.00, then go all the way to 2506.00.

So I want 2506.00 to be changed to 2506.

Would I do this using an update query? How should it be written? If you
can provide me with the SQL that would be even better!

Using Access 97, FWIW.

Thanks!
 
O

Ofer Cohen

First back up your data.

You can use update query
UPDATE Parcel_Info SET Group_Index = Round(Val([Group_Index]))
 
P

Phil

Ofer,

I may have not been clear enough on the fact that the values are now TEXT
not NUMERIC. IOW, the properties of the field have been changed from NUMERIC
to TEXT. I don' think ROUND will work, will it?

Ofer Cohen said:
First back up your data.

You can use update query
UPDATE Parcel_Info SET Group_Index = Round(Val([Group_Index]))

--
Good Luck
BS"D


Phil said:
Hello,

I have a table that had values that were NUMERIC (eg. 2506.00) and have now
been converted to TEXT properties. Now I need to remove the decimal and the
2 trailing zeros. The field name is Group_Index. The table is called
Parcel_Info. Also, in case you need to know, the values are not all 4
digits. They actually start at 1.00, then go all the way to 2506.00.

So I want 2506.00 to be changed to 2506.

Would I do this using an update query? How should it be written? If you
can provide me with the SQL that would be even better!

Using Access 97, FWIW.

Thanks!
Click to expand...
Click to expand...
 
O

Ofer Cohen

This is why I added the Val, to convert it back to number before the round

--
Good Luck
BS"D


Phil said:
Ofer,

I may have not been clear enough on the fact that the values are now TEXT
not NUMERIC. IOW, the properties of the field have been changed from NUMERIC
to TEXT. I don' think ROUND will work, will it?

Ofer Cohen said:
First back up your data.

You can use update query
UPDATE Parcel_Info SET Group_Index = Round(Val([Group_Index]))

--
Good Luck
BS"D


Phil said:
Hello,

I have a table that had values that were NUMERIC (eg. 2506.00) and have now
been converted to TEXT properties. Now I need to remove the decimal and the
2 trailing zeros. The field name is Group_Index. The table is called
Parcel_Info. Also, in case you need to know, the values are not all 4
digits. They actually start at 1.00, then go all the way to 2506.00.

So I want 2506.00 to be changed to 2506.

Would I do this using an update query? How should it be written? If you
can provide me with the SQL that would be even better!

Using Access 97, FWIW.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
P

Phil

Ofer,

Couldn't get it to work. Here is the exact SQL from my update query:

UPDATE Parcel_Info SET Parcel_Info.Group_Index = Round(Val([Group_Index]));

Here is the error:

Undefined function 'Round' in expression

Please advise.

Thanks.

Phil
Ofer Cohen said:
This is why I added the Val, to convert it back to number before the round

--
Good Luck
BS"D


Phil said:
Ofer,

I may have not been clear enough on the fact that the values are now TEXT
not NUMERIC. IOW, the properties of the field have been changed from NUMERIC
to TEXT. I don' think ROUND will work, will it?

Ofer Cohen said:
First back up your data.

You can use update query
UPDATE Parcel_Info SET Group_Index = Round(Val([Group_Index]))

--
Good Luck
BS"D


:

Hello,

I have a table that had values that were NUMERIC (eg. 2506.00) and have now
been converted to TEXT properties. Now I need to remove the decimal and the
2 trailing zeros. The field name is Group_Index. The table is called
Parcel_Info. Also, in case you need to know, the values are not all 4
digits. They actually start at 1.00, then go all the way to 2506.00.

So I want 2506.00 to be changed to 2506.

Would I do this using an update query? How should it be written? If you
can provide me with the SQL that would be even better!

Using Access 97, FWIW.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
D

datAdrenaline

Try this:

UPDATE Parcel_Info SET Parcel_Info.Group_Index = Int(Val([Group_Index]));
--
Regards,
Brent Spaulding
datAdrenaline


Phil said:
Ofer,

Couldn't get it to work. Here is the exact SQL from my update query:

UPDATE Parcel_Info SET Parcel_Info.Group_Index = Round(Val([Group_Index]));

Here is the error:

Undefined function 'Round' in expression

Please advise.

Thanks.

Phil
Ofer Cohen said:
This is why I added the Val, to convert it back to number before the round

--
Good Luck
BS"D


Phil said:
Ofer,

I may have not been clear enough on the fact that the values are now TEXT
not NUMERIC. IOW, the properties of the field have been changed from NUMERIC
to TEXT. I don' think ROUND will work, will it?

:

First back up your data.

You can use update query
UPDATE Parcel_Info SET Group_Index = Round(Val([Group_Index]))

--
Good Luck
BS"D


:

Hello,

I have a table that had values that were NUMERIC (eg. 2506.00) and have now
been converted to TEXT properties. Now I need to remove the decimal and the
2 trailing zeros. The field name is Group_Index. The table is called
Parcel_Info. Also, in case you need to know, the values are not all 4
digits. They actually start at 1.00, then go all the way to 2506.00.

So I want 2506.00 to be changed to 2506.

Would I do this using an update query? How should it be written? If you
can provide me with the SQL that would be even better!

Using Access 97, FWIW.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer Cohen

The Int should work, but if that gives you problem, you can try

UPDATE Parcel_Info SET Group_Index=
IIf(InStr([Group_Index],"."),Left([Group_Index],InStr([Group_Index],".")-1),[Group_Index])

--
Good Luck
BS"D


Phil said:
Ofer,

Couldn't get it to work. Here is the exact SQL from my update query:

UPDATE Parcel_Info SET Parcel_Info.Group_Index = Round(Val([Group_Index]));

Here is the error:

Undefined function 'Round' in expression

Please advise.

Thanks.

Phil
Ofer Cohen said:
This is why I added the Val, to convert it back to number before the round

--
Good Luck
BS"D


Phil said:
Ofer,

I may have not been clear enough on the fact that the values are now TEXT
not NUMERIC. IOW, the properties of the field have been changed from NUMERIC
to TEXT. I don' think ROUND will work, will it?

:

First back up your data.

You can use update query
UPDATE Parcel_Info SET Group_Index = Round(Val([Group_Index]))

--
Good Luck
BS"D


:

Hello,

I have a table that had values that were NUMERIC (eg. 2506.00) and have now
been converted to TEXT properties. Now I need to remove the decimal and the
2 trailing zeros. The field name is Group_Index. The table is called
Parcel_Info. Also, in case you need to know, the values are not all 4
digits. They actually start at 1.00, then go all the way to 2506.00.

So I want 2506.00 to be changed to 2506.

Would I do this using an update query? How should it be written? If you
can provide me with the SQL that would be even better!

Using Access 97, FWIW.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
P

Phil

Your solution worked PERFECTLY!

THANK YOU!

BTW, I tried to click the YES button to indicate that this was the answer to
the question, but it is not letting me do that for some reason. Does it show
anything on your end that I did click the YES button?

Regards,

Phil

Ofer Cohen said:
The Int should work, but if that gives you problem, you can try

UPDATE Parcel_Info SET Group_Index=
IIf(InStr([Group_Index],"."),Left([Group_Index],InStr([Group_Index],".")-1),[Group_Index])

--
Good Luck
BS"D


Phil said:
Ofer,

Couldn't get it to work. Here is the exact SQL from my update query:

UPDATE Parcel_Info SET Parcel_Info.Group_Index = Round(Val([Group_Index]));

Here is the error:

Undefined function 'Round' in expression

Please advise.

Thanks.

Phil
Ofer Cohen said:
This is why I added the Val, to convert it back to number before the round

--
Good Luck
BS"D


:

Ofer,

I may have not been clear enough on the fact that the values are now TEXT
not NUMERIC. IOW, the properties of the field have been changed from NUMERIC
to TEXT. I don' think ROUND will work, will it?

:

First back up your data.

You can use update query
UPDATE Parcel_Info SET Group_Index = Round(Val([Group_Index]))

--
Good Luck
BS"D


:

Hello,

I have a table that had values that were NUMERIC (eg. 2506.00) and have now
been converted to TEXT properties. Now I need to remove the decimal and the
2 trailing zeros. The field name is Group_Index. The table is called
Parcel_Info. Also, in case you need to know, the values are not all 4
digits. They actually start at 1.00, then go all the way to 2506.00.

So I want 2506.00 to be changed to 2506.

Would I do this using an update query? How should it be written? If you
can provide me with the SQL that would be even better!

Using Access 97, FWIW.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer Cohen

Your THANK YOU is enough, so thank you for that.

--
Good Luck
BS"D


Phil said:
Your solution worked PERFECTLY!

THANK YOU!

BTW, I tried to click the YES button to indicate that this was the answer to
the question, but it is not letting me do that for some reason. Does it show
anything on your end that I did click the YES button?

Regards,

Phil

Ofer Cohen said:
The Int should work, but if that gives you problem, you can try

UPDATE Parcel_Info SET Group_Index=
IIf(InStr([Group_Index],"."),Left([Group_Index],InStr([Group_Index],".")-1),[Group_Index])

--
Good Luck
BS"D


Phil said:
Ofer,

Couldn't get it to work. Here is the exact SQL from my update query:

UPDATE Parcel_Info SET Parcel_Info.Group_Index = Round(Val([Group_Index]));

Here is the error:

Undefined function 'Round' in expression

Please advise.

Thanks.

Phil
:

This is why I added the Val, to convert it back to number before the round

--
Good Luck
BS"D


:

Ofer,

I may have not been clear enough on the fact that the values are now TEXT
not NUMERIC. IOW, the properties of the field have been changed from NUMERIC
to TEXT. I don' think ROUND will work, will it?

:

First back up your data.

You can use update query
UPDATE Parcel_Info SET Group_Index = Round(Val([Group_Index]))

--
Good Luck
BS"D


:

Hello,

I have a table that had values that were NUMERIC (eg. 2506.00) and have now
been converted to TEXT properties. Now I need to remove the decimal and the
2 trailing zeros. The field name is Group_Index. The table is called
Parcel_Info. Also, in case you need to know, the values are not all 4
digits. They actually start at 1.00, then go all the way to 2506.00.

So I want 2506.00 to be changed to 2506.

Would I do this using an update query? How should it be written? If you
can provide me with the SQL that would be even better!

Using Access 97, FWIW.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trailing zeros 5
Decimal Trailing Zero Formating 13
Import decimal numbers with trailing zeros into Excel 1
Add trailing zeros 5
Decimal places in forms and tables 5
Remove trailing spaces from Excel? 1
How do I keep leading zeros and remove the decimal point when expo 2
Adding in leading zeros and a space to a text field in a table 4

Top