How to change the format and content of a field

[4656rp]

I have a text field that contains numbers with a maximum length of 5. How can I place a period at position 4 on records with length >=4, i.e., v672 should read v67.2 or 80711 should read 807.11, but 462 should read 462. I've tried IIf statements and formating the field as &&@.&& and that doesn't work. Any ideas

Many thanks,

rp

 

[BillPower]

This should do the trick.

If InStr(Me!YourTextField, ".&quot = 0 Then 'ignore if already has a full stop ".".
If Len(Me!YourTextField) = 4 Then
Me!YourTextField = Me!YourTextField & "."
ElseIf Len(Me!YourTextField) = 5 Then
Me!YourTextField = Mid(Me!YourTextField, 1, 4) & "." & Mid(Me!YourTextField, 5, 1)
End If
End If

 

[MichaelRed]

On CAREFUL reading, I think you mean to NOT add the decimal point to strings where the length is less than 4 characters. BUT --- the example acttually shows either the decimal point - or the period to (use of 'proper' grammer? here?). Just to be sure, I included the case - but commented it out. I also 'wrapped' the logic in a more formal procedure -albeit w/o the expected niceity of error checking et al. There is no significant difference between this and billpower's routine - it is just a matter or style and -in my opinion- some degree of 'readability'.

Public Function basAddDecPt(strIn As String) As String

    'Michael Red 2/26/2006 Tek-Tips thread181-485593 for 4656rp

    If (InStr(strIn, ".") = 0) Then 'ignore if already has a full stop ".".

        Select Case Len(strIn)

'            Case Is < 4
'                basAddDecPt = strIn & &quot;.&quot;

            Case Is = 4
               basAddDecPt = strIn & &quot;.&quot;

            Case Is > 4
                    basAddDecPt = Mid(strIn, 1, 4) & &quot;.&quot; & Mid(strIn, 5, 1)

            Case Else
                basAddDecPt = strIn

        End Select

    End If
 
End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places