If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Inserting a period into a string
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 |
#2
|
|||
|
|||
Inserting a period into a string
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 "TLuebke" wrote: 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 |
#3
|
|||
|
|||
Inserting a period into a string
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 '================================================= === TLuebke wrote: 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 |
Thread Tools | |
Display Modes | |
|
|