Inserting a period into a string

[TLuebke]

I'm having a braincramp. I want to count from the right 2 spaces and insert a
"." so 12345 becomes 123.45

Also in the case where a dollar amount has dropped a zero i.e. 12.5 how do i
add a trailing zero i.e. 12.50

thanks,

Todd

 

[ND Pard]

Let's assume the string is in a text field named: MyString of the a table
named: Table1.

Create a new query, put the following as its SQL.

UPDATE Table1 SET Table1.MyString =
IIf(Len([MyString])>2,Left([MyString],Len([MyString])-2) & "." &
Right([MyString],2),"." & [MyString]);

This SQL will add a "." into the string before the last two characters of a
string unless the string is less than three (3) characters in length; in that
case, it puts a "." in front of the string. IE, it will convert the string
12345 into 123.45.

To change the string 12.5 to 12.50, use the following SQL:

UPDATE Table1 SET Table1.MyString = [MyString] & "0";

Good Luck.



Len

 

[John Spencer]

Left(SomeString,Len(SomeString)-2) & "." & Right(SomeString,2)

Of course that will give an error results if the length of the string is
less than 2 characters long.

You will need to force the trailing zero by using the format function.

Format(SomeNumber, "#,###.00")

Be aware that using the format string turns the result into a string.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================