Inserting Decimals

[Florida Analyst]

I have these Medical codes that should have decimals two spaces from the
right but when I get the files from IT they don't include any decimals. How
would I tell access to insert decimals in that field. The data format is text
because some of the codes contain letters. Thank you.

 

[KARL DEWEY]

Try this substituting your field name for TITLE ---
Expr1: IIf(InStr([TITLE],".")>0,[TITLE],Left([TITLE],Len([TITLE])-2) & "." &
Right([TITLE],2))

 

[Florida Analyst]

Worked great! thank you

Try this substituting your field name for TITLE ---
Expr1: IIf(InStr([TITLE],".")>0,[TITLE],Left([TITLE],Len([TITLE])-2) & "." &
Right([TITLE],2))
--
KARL DEWEY
Build a little - Test a little

I have these Medical codes that should have decimals two spaces from the
right but when I get the files from IT they don't include any decimals. How
would I tell access to insert decimals in that field. The data format is text
because some of the codes contain letters. Thank you.

 

[Florida Analyst]

Ok to build on the question that I asked, the code below works, however when
the data is extracted from the file sent from IT, the file contains some
spaces that causes the decimal place to not work right, I thought that maybe
including a
Trim([TITLE]) would be easy to included, however everywhere I place the
command it gives me an error, do you think I would have to run two queries
include all he commands I want?

Try this substituting your field name for TITLE ---
Expr1: IIf(InStr([TITLE],".")>0,[TITLE],Left([TITLE],Len([TITLE])-2) & "." &
Right([TITLE],2))
--
KARL DEWEY
Build a little - Test a little

I have these Medical codes that should have decimals two spaces from the
right but when I get the files from IT they don't include any decimals. How
would I tell access to insert decimals in that field. The data format is text
because some of the codes contain letters. Thank you.

 

[KARL DEWEY]

If you want to remove all the spaces then use this ---
Expr1: IIf(InStr(Replace([TITLE]," ",""),".")>0,Replace([TITLE],"
",""),Left(Replace([TITLE]," ",""),Len(Replace([TITLE]," ",""))-2) & "." &
Right(Replace([TITLE]," ",""),2))


--
KARL DEWEY
Build a little - Test a little

Ok to build on the question that I asked, the code below works, however when
the data is extracted from the file sent from IT, the file contains some
spaces that causes the decimal place to not work right, I thought that maybe
including a
Trim([TITLE]) would be easy to included, however everywhere I place the
command it gives me an error, do you think I would have to run two queries
include all he commands I want?

Try this substituting your field name for TITLE ---
Expr1: IIf(InStr([TITLE],".")>0,[TITLE],Left([TITLE],Len([TITLE])-2) & "." &
Right([TITLE],2))
--
KARL DEWEY
Build a little - Test a little

I have these Medical codes that should have decimals two spaces from the
right but when I get the files from IT they don't include any decimals. How
would I tell access to insert decimals in that field. The data format is text
because some of the codes contain letters. Thank you.