Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by Phil, Jul 28, 2006.

  1. Phil

    Phil
    Expand Collapse
    Guest

    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!
     
  2. Loading...


  3. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    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" wrote:

    > 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!
     
  4. Phil

    Phil
    Expand Collapse
    Guest

    RE: How remove decimal and 2 trailing zeros from multiple values i

    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" wrote:

    > 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" wrote:
    >
    > > 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!
     
  5. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    RE: How remove decimal and 2 trailing zeros from multiple values i

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

    --
    Good Luck
    BS"D


    "Phil" wrote:

    > 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" wrote:
    >
    > > 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" wrote:
    > >
    > > > 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!
     
  6. Phil

    Phil
    Expand Collapse
    Guest

    RE: How remove decimal and 2 trailing zeros from multiple values i

    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" wrote:

    > This is why I added the Val, to convert it back to number before the round
    >
    > --
    > Good Luck
    > BS"D
    >
    >
    > "Phil" wrote:
    >
    > > 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" wrote:
    > >
    > > > 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" wrote:
    > > >
    > > > > 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!
     
  7. datAdrenaline

    datAdrenaline
    Expand Collapse
    Guest

    RE: How remove decimal and 2 trailing zeros from multiple values i

    Try this:

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


    "Phil" wrote:

    > 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" wrote:
    >
    > > This is why I added the Val, to convert it back to number before the round
    > >
    > > --
    > > Good Luck
    > > BS"D
    > >
    > >
    > > "Phil" wrote:
    > >
    > > > 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" wrote:
    > > >
    > > > > 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" wrote:
    > > > >
    > > > > > 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!
     
  8. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    RE: How remove decimal and 2 trailing zeros from multiple values i

    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" wrote:

    > 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" wrote:
    >
    > > This is why I added the Val, to convert it back to number before the round
    > >
    > > --
    > > Good Luck
    > > BS"D
    > >
    > >
    > > "Phil" wrote:
    > >
    > > > 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" wrote:
    > > >
    > > > > 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" wrote:
    > > > >
    > > > > > 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!
     
  9. Phil

    Phil
    Expand Collapse
    Guest

    RE: How remove decimal and 2 trailing zeros from multiple values i

    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" wrote:

    > 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" wrote:
    >
    > > 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" wrote:
    > >
    > > > This is why I added the Val, to convert it back to number before the round
    > > >
    > > > --
    > > > Good Luck
    > > > BS"D
    > > >
    > > >
    > > > "Phil" wrote:
    > > >
    > > > > 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" wrote:
    > > > >
    > > > > > 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" wrote:
    > > > > >
    > > > > > > 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!
     
  10. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    RE: How remove decimal and 2 trailing zeros from multiple values i

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

    --
    Good Luck
    BS"D


    "Phil" wrote:

    > 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" wrote:
    >
    > > 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" wrote:
    > >
    > > > 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" wrote:
    > > >
    > > > > This is why I added the Val, to convert it back to number before the round
    > > > >
    > > > > --
    > > > > Good Luck
    > > > > BS"D
    > > > >
    > > > >
    > > > > "Phil" wrote:
    > > > >
    > > > > > 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" wrote:
    > > > > >
    > > > > > > 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" wrote:
    > > > > > >
    > > > > > > > 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!
     

Share This Page