complex problem

[aqif]

Hi

In my table I have a stage field and the maximum length of Stage field is 6. The stage can be like

IA, IIA, IVa etc

I want to export the field in an old database where they extract the field by defining the String Position. Therefore They want me to write stage in a way that if the stage is null then have six spaces and if stage is 2 characters rhen 4 spaces should follow after that and then rest of the field. The data should look smthng like this

IA + 4 spaces + next field

I am trying something like this

IIF(Len([Stage]=0)," ",IIF(Len([Stage]=1),[Stage] & " ") and so on.........

Is there any better way to do the thngs. By the way none of my stage is 6 characters long

Cheers!
Aqif

 

[antomack]

The following should help. It pads out a string with whatever character you wish to make the string the length you require. Thus to pad out your string with spaces to left to length 6 use
LPad([Stage]," ",6)

or to pad out your string with spaces to right to length 6 use
RPad([Stage]," ",6)

Function LPad(S, ByVal C As String, N As Integer) As String
'
' Adds character C to the left of S to make it right-justified
'
  If Len(C) = 0 Then C = " "
  If N < 1 Then
    LPad = ""
  Else
    LPad = Right$(String$(N, left$(C, 1)) & S, N)
  End If
End Function

Function RPad(S, ByVal C As String, N As Integer) As String
'
' Adds character C to the right of S to make it left-justified.
'
  If Len(C) = 0 Then C = " "
  If N < 1 Then
    RPad = ""
  Else
    RPad = left$(S & String$(N, left$(C, 1)), N)
  End If
End Function