Strip Decimal

[Notaguru]

I have two different tables that have ICD codes, its just that in the
selection list the codes do not contain the decimal, but in the master list
they do. How do I remove that decimal? Thanks a bunch

 

[Beetle]

Use the Replace function;

Replace([YourField], ".", "")

For example, you could run an Update query;

Update [YourTable] Set [YourField] = Replace([YourField], ".", "");

*Make a backup before you do the above.*

 

[John W. Vinson]

I have two different tables that have ICD codes, its just that in the
selection list the codes do not contain the decimal, but in the master list
they do. How do I remove that decimal? Thanks a bunch

What's the datatype of the field (in both tables)? Text (probably the best
choice) or Number? Could you post some examples of the data in the table?

This can be done but the best technique may be different for number or text
fields.

 

[Notaguru]

Hi Beetle and thanks for your quick response.
Ok, This is what I wrote:
"Update [ICD Codes] Set [UICD Codes]"=Replace("ICD Code]",".","")
ICD Codes=Table
UICD Codes would be the field I would want the result post to
ICD Code is the field that contains the decimal.
Both are text fields
But when I run it, I get the message that the Query must contain a
destination field, which I thought was UICD Codes.

Use the Replace function;

Replace([YourField], ".", "")

For example, you could run an Update query;

Update [YourTable] Set [YourField] = Replace([YourField], ".", "");

*Make a backup before you do the above.*

--
_________

Sean Bailey

I have two different tables that have ICD codes, its just that in the
selection list the codes do not contain the decimal, but in the master list
they do. How do I remove that decimal? Thanks a bunch

 

[Notaguru]

Hi John, Thanks for responding.
They are both text. The field in our health information system looks like
235.45, where as the field in the select looks like 23545

 

[Beetle]

Are you trying to remove the decimal from the existing records in a table,
or are you trying to insert new records into another table and remove
the decimal in the process?

Or something else?
--
_________

Sean Bailey

Hi Beetle and thanks for your quick response.
Ok, This is what I wrote:
"Update [ICD Codes] Set [UICD Codes]"=Replace("ICD Code]",".","")
ICD Codes=Table
UICD Codes would be the field I would want the result post to
ICD Code is the field that contains the decimal.
Both are text fields
But when I run it, I get the message that the Query must contain a
destination field, which I thought was UICD Codes.

Use the Replace function;

Replace([YourField], ".", "")

For example, you could run an Update query;

Update [YourTable] Set [YourField] = Replace([YourField], ".", "");

*Make a backup before you do the above.*

--
_________

Sean Bailey

I have two different tables that have ICD codes, its just that in the
selection list the codes do not contain the decimal, but in the master list
they do. How do I remove that decimal? Thanks a bunch

 

[Notaguru]

I would like to take the exisitng table, strip the decimals then use the
updated table in my query where the select fields are that I need to use to
pull records from. I hope I am explaining myself clearly. If the government
supplied a table that used the decimals, this would not be a problem. So I
could add the decimal to the other table if that would be easier.

Are you trying to remove the decimal from the existing records in a table,
or are you trying to insert new records into another table and remove
the decimal in the process?

Or something else?
--
_________

Sean Bailey

Hi Beetle and thanks for your quick response.
Ok, This is what I wrote:
"Update [ICD Codes] Set [UICD Codes]"=Replace("ICD Code]",".","")
ICD Codes=Table
UICD Codes would be the field I would want the result post to
ICD Code is the field that contains the decimal.
Both are text fields
But when I run it, I get the message that the Query must contain a
destination field, which I thought was UICD Codes.

Use the Replace function;

Replace([YourField], ".", "")

For example, you could run an Update query;

Update [YourTable] Set [YourField] = Replace([YourField], ".", "");

*Make a backup before you do the above.*

--
_________

Sean Bailey


:

I have two different tables that have ICD codes, its just that in the
selection list the codes do not contain the decimal, but in the master list
they do. How do I remove that decimal? Thanks a bunch

 

[John W. Vinson]

Hi John, Thanks for responding.
They are both text. The field in our health information system looks like
235.45, where as the field in the select looks like 23545

If you want to permanently and irrevokably remove all the decimals from your
table (note: this will mess you up badly if you have both 235.45 and 2354.5 in
the table!!!!) then run an Update query. BACK UP YOUR DATABASE first - this is
irreversible and you may find you need the old copy!!

Assuming that your table is named [ICD Codes] and you want to remove all
periods from [UICD Codes], create a new Query in the query design window.
Select only the UICD Codes field. Change it into an Update query using the
Query menu option or the query type tool on the toolbar; on the Update To line
put

Replace([UICD Codes], ".", "")

Run the query by clicking the ! icon.

It looks like you're trying to apply folks' advice in the wrong place hence
the step by step explanation.

 

[Beetle]

Is the field [UICD Codes] in the ICD Codes table, or in another table?

If the latter, what is the name of that other table?
--
_________

Sean Bailey

I would like to take the exisitng table, strip the decimals then use the
updated table in my query where the select fields are that I need to use to
pull records from. I hope I am explaining myself clearly. If the government
supplied a table that used the decimals, this would not be a problem. So I
could add the decimal to the other table if that would be easier.

Are you trying to remove the decimal from the existing records in a table,
or are you trying to insert new records into another table and remove
the decimal in the process?

Or something else?
--
_________

Sean Bailey

Hi Beetle and thanks for your quick response.
Ok, This is what I wrote:
"Update [ICD Codes] Set [UICD Codes]"=Replace("ICD Code]",".","")
ICD Codes=Table
UICD Codes would be the field I would want the result post to
ICD Code is the field that contains the decimal.
Both are text fields
But when I run it, I get the message that the Query must contain a
destination field, which I thought was UICD Codes.
:

Use the Replace function;

Replace([YourField], ".", "")

For example, you could run an Update query;

Update [YourTable] Set [YourField] = Replace([YourField], ".", "");

*Make a backup before you do the above.*

--
_________

Sean Bailey


:

I have two different tables that have ICD codes, its just that in the
selection list the codes do not contain the decimal, but in the master list
they do. How do I remove that decimal? Thanks a bunch

 

[Notaguru]

Yes, it is in the ICD Codes table.

Is the field [UICD Codes] in the ICD Codes table, or in another table?

If the latter, what is the name of that other table?
--
_________

Sean Bailey

I would like to take the exisitng table, strip the decimals then use the
updated table in my query where the select fields are that I need to use to
pull records from. I hope I am explaining myself clearly. If the government
supplied a table that used the decimals, this would not be a problem. So I
could add the decimal to the other table if that would be easier.

Are you trying to remove the decimal from the existing records in a table,
or are you trying to insert new records into another table and remove
the decimal in the process?

Or something else?
--
_________

Sean Bailey


:

Hi Beetle and thanks for your quick response.
Ok, This is what I wrote:
"Update [ICD Codes] Set [UICD Codes]"=Replace("ICD Code]",".","")
ICD Codes=Table
UICD Codes would be the field I would want the result post to
ICD Code is the field that contains the decimal.
Both are text fields
But when I run it, I get the message that the Query must contain a
destination field, which I thought was UICD Codes.
:

Use the Replace function;

Replace([YourField], ".", "")

For example, you could run an Update query;

Update [YourTable] Set [YourField] = Replace([YourField], ".", "");

*Make a backup before you do the above.*

--
_________

Sean Bailey


:

I have two different tables that have ICD codes, its just that in the
selection list the codes do not contain the decimal, but in the master list
they do. How do I remove that decimal? Thanks a bunch

 

[Beetle]

Just to re-iterate, you have a table named [ICD Codes] which has fields
[ICD Code] and [UICD Codes]. The field [ICD Code] has the values with
decimals and you want to put these values into the [UICD Codes] field
but without the decimals.

If the above is all true, then the following query should work. Again,
BE SURE TO MAKE A BACKUP BEFORE YOU RUN THIS QUERY!

Update [ICD Codes] Set [UICD Codes] = Replace([ICD Code], ".", "");
--
_________

Sean Bailey

Yes, it is in the ICD Codes table.

Is the field [UICD Codes] in the ICD Codes table, or in another table?

If the latter, what is the name of that other table?
--
_________

Sean Bailey

I would like to take the exisitng table, strip the decimals then use the
updated table in my query where the select fields are that I need to use to
pull records from. I hope I am explaining myself clearly. If the government
supplied a table that used the decimals, this would not be a problem. So I
could add the decimal to the other table if that would be easier.

:

Are you trying to remove the decimal from the existing records in a table,
or are you trying to insert new records into another table and remove
the decimal in the process?

Or something else?
--
_________

Sean Bailey


:

Hi Beetle and thanks for your quick response.
Ok, This is what I wrote:
"Update [ICD Codes] Set [UICD Codes]"=Replace("ICD Code]",".","")
ICD Codes=Table
UICD Codes would be the field I would want the result post to
ICD Code is the field that contains the decimal.
Both are text fields
But when I run it, I get the message that the Query must contain a
destination field, which I thought was UICD Codes.
:

Use the Replace function;

Replace([YourField], ".", "")

For example, you could run an Update query;

Update [YourTable] Set [YourField] = Replace([YourField], ".", "");

*Make a backup before you do the above.*

--
_________

Sean Bailey


:

I have two different tables that have ICD codes, its just that in the
selection list the codes do not contain the decimal, but in the master list
they do. How do I remove that decimal? Thanks a bunch