Remove Decimal in text format

[Leesha]

Hi,

I have a table that contains diagnosis ICD-9 numbers. The format is text. The numbers may or may not have a decimal in and to compound matters, the decimal may be anywhere. The limit to the length is 5 figures. So, a number may be 401.9 or 40.29 or 401.92 etc. There are thousands. I need to be able to convert the in a query to not have the decimal. So in the above example, they would return as 4019, 4029, 40192 and so on. I've been trialing a variety of different formula's I've gotten over the years to split or break up a string but nothing is working, probably because the decimal is never in the same place.

Thanks,
Leesha

[HansV]

Let's say that the field is named ICD9.
Create a query in design view, based on the table.
Change it to an update query (in Access 2003 or before, select Query | Update Query; in Access 2007 or later, activate the Design tab of the ribbon and click Update).
Add the ICD9 field to the query grid.
In the Update To line, enter the expression

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

Run the query.

[Leesha]

OMG Hans, it worked like a charm! I've never used "Replace" before but it will be new best friend just as "trim" has become!!!

You're the best,
Leesha

[Leesha]

Hi,

The code did exactly as I needed it to do however when I link the two tables in the attached database, it shows no fields that are the same. However there are. For example, the code of 2500 exists in both tables. I'm not sure why its not working.

Leesha

[HansV]

Do you mean 25000? Neither table contains 2500.

[HansV]

The ICD9 fields contain varying numbers of spaces after the values.

One way to get around this problem is to run an update query on both tables that changes the field to Trim([fieldname]) with the correct field name substituted.

Another solution is to open the query and switch to SQL view and change the line

FROM [tblCRRM1022DX-Chosen25] INNER JOIN tblCRRDXParameterGuide ON [tblCRRM1022DX-Chosen25].M1022DXICD9Code = tblCRRDXParameterGuide.code

to

FROM [tblCRRM1022DX-Chosen25] INNER JOIN tblCRRDXParameterGuide ON Trim([tblCRRM1022DX-Chosen25].M1022DXICD9Code) = Trim(tblCRRDXParameterGuide.code)

You won't be able to view the query in design view after that.

[Leesha]

Hi Hans,

You were right on the 2500. I was trying to get the database updated before I left work and was typing fast. Sorry for the the time is caused you to waste.

How can you tell there are varying numbers of spaces after the values? If I knew how to do that it would be a hhuge help.

Thanks,
Leesha

[HansV]

The 2500 > 25000 wasn't really a problem.

If you open one of the tables and click in the relevant field, you'll notice there are one or more spaces between the end of the code and the insertion point for many records:

x133.png