Drop decimals but keep all numbers? (1 Viewer)

[Therat]

Question:

I work in healthcare and a vender wants me to send ICD9 codes without a decimal. Is there a function in access to drop a decimal, ex.98.98, and result in 9898?

Thanks in advance!

 

[Fornatian]

Use an update query to update the field to it's own value x 100.

 

[Therat]

I'm sorry. I didn't explain that codes can have the decimal in different places, so multiplying by one number won't work.

ex:
Code Needs to be
31.1 311
51.10 5110
37.22 3722
64.0 640

 

[Mile-O]

I think you could use the CInt(), InStr(), Left() and Mid() functions to get what you want.


=CInt(Left([txtStart],InStr(1,[txtStart],".")-1) & Mid([txtStart],InStr(1,[txtStart],".")+1))

Just a thought...

 

[Therat]

Challenge: I am still having problems....

I couldn't get the previous poster's expression to work.

Here's my revised expression.

=Mid([txt],1,instr([txt],".")-1)+Mid([txt],instr([txt],".")+1,len([txt])-instr([txt],"."))

This expression works with codes that have a decimal, but it drop codes without a decimal. Can someone help me write an expression that will return the [txt] if there isn't a decimal in the data.

Example of data
289.43
V76.0
E1232
550.3
450.43


Thanks
TheRAT

 

[KenHigg]

Question:

I work in healthcare and a vender wants me to send ICD9 codes without a decimal. Is there a function in access to drop a decimal, ex.98.98, and result in 9898?

Thanks in advance!

your healthcare field = ophthalmologist?

 

[Therat]

Not sure how it is relevant to the question, but ...

I am part of a planning department for a large healthcare organization.

 

[KenHigg]

Just a funi - your text was so large....

sorry...

 

[Fornatian]

Hmm, create a function maybe...

I've been out of the game for a while so am a bit rusty. The solution I came up with was to create a user defined function as in:

Function RemoveCharacter(strSubject, strChar) As String
RemoveCharacter = Excel.WorksheetFunction.Substitute(strSubject, strChar, "")
End Function

I then called the function in a calculated column as in:

Expr1: RemoveCharacter([Text1],".")

I tested mine a standard query but you do it in an update query once convinced that it works.

Note, you also need to include Excel in your VBA references for this to work tho.

 

[Therat]

Looks good, but I have a problem...

I saved a module with the new function and wrote a test query that called the function, but there was a run time error '424'. The VB code line "removecharacter = ..." is highlighted. Do I need to have a certain reference library checked for an excel function call? I am using windows XP & have Access 2002. I have the following references (in priority order): Visual Basic for Applications, Microsoft Access 10.0 Object Library, OLE Automation, Microsoft ActiveX Data Object 2.5 library, and Microsoft Office XP Web components.

Thanks for helping!

 

[Therat]

Ok almost working now, but what about nulls....

I added the reference to microsoft excel and got it to work on my small test file. I ran into a run-time erro '94' invalid use of null when I tried to run the update query on my table (~200,000) across several columns. Some columns don't have a code.

I thought I could the following IIF statement, but it didn't work.

IIF([txt] is null,"",removecharacter([txt],"."))

any thoughts?

 

[Fornatian]

Base the update on a query that only pulls non-null records. Or include that criteria as an condition in another column.

 

[Fornatian]

or just change the function to:

Function RemoveCharacter(strSubject, strChar) As String

If strSubject = "" then
RemoveCharacter = ""
Else
RemoveCharacter = Excel.WorksheetFunction.Substitute(strSubject, strChar, "")
End if
End Function

whcih should catch nulls, as they are converted to empty strings I think.

 

[Mike375]

Question:

I work in healthcare and a vender wants me to send ICD9 codes without a decimal. Is there a function in access to drop a decimal, ex.98.98, and result in 9898?

Thanks in advance!

I have attached a table and query in Access 95 that is an extract of what I use to clean up phone numbers with brackets, spaces and dashes.

The field SearchNum has had a . added as I use that field when the phone numbers might not have characters that I have things set up for. As you can see it works when no decimal is present by using IIf for Result field.

PS. For those who might criticise for having fields that are not one word such as CL Ph Home, the table was from when I was first doing Access and was from an insurance company to match up with them. Blame the big time institution programmers

Mike

 

[Mile-O]

Why use an Excel worksheet function when Access has the Replace() function?

 

[Fornatian]

If so, just proves how rusty I am