Label paragraphs (a, b,c, d) 1

[kporters]

I've created a report to have group headers based on product id. I want to give the first product id an "A" in the header, the next a "B" in the header, and so on and if the product id's are over 26, to start the 27th product id w/"AA", then "AB". Is there a way of doing this w/code and not having to create a table?

 

[mpastore]

Function getNextPara(psCurrPara As String)
' examples
' ?getNextPara("&quot ' returns "A"
' ?getNextPara("A&quot ' returns "B"
' ?getNextPara("Z&quot ' returns "AA"
' ?getNextPara("AA&quot ' returns "AB"
' ?getNextPara("AZ&quot ' returns "AAA"

Dim sCurrPara As String
sCurrPara = UCase(Trim(psCurrPara))

If sCurrPara = "" Then
getNextPara = "A"
Exit Function
End If

Dim sRightChar As String
Dim iCharLen As Integer

Dim sRet As String
Dim sCharStr As String
sCharStr = "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"

sRightChar = Right(sCurrPara, 1)
iCharLen = Len(sCurrPara)

Select Case sRightChar
Case "Z"
sRet = Left(sCharStr, iCharLen) & "A"
Case Else
sRet = Left(sCurrPara, iCharLen - 1) & Chr(Asc(sRightChar) + 1)
End Select

getNextPara = sRet


End Function

 

[kporters]

thanks,
i tested your code, it stays in a loop on the first if all para's are A's

 

[mpastore]

There is no loop in the code.

 

[kporters]

in my report, getnextpara remains an "A" for all product id's, any suggestions where i might have gone wrong?
thanks

 

[mpastore]

Ok, I understand. What you want to do is create a variable in your form that is global to the whole form.
Go into the VBA module of your report and put this there:

Option Compare Database
Dim msPara As String

Now on your header print event you will put this. This code assumes that your text box on the report is named txtPara:

Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)
msPara = getNextPara(msPara)
Me.txtPara = msPara
End Sub

The var msPara will get incremented each time to A, B ... AA, AB ... etc

 

[kporters]

thanks,
& sorry about the initial confusion

 

[mpastore]

Another question...are you emulating the Excel column format? In other words, after AZ would you want BA or AAA?

 

[kporters]

yes, I'm attempting to emulate the Excel column format, I did try to change the code to go to BA after AZ but have been unsuccessful.

 

[mpastore]

Use this function istead:

Function getNextPara(piColIndex As Integer) As String

'* returns the alpha representation of an Excel column from a numeric
'* sample calls:
' ?getNextPara(1) ' A
' ?getNextPara(256) ' IV
' ?getNextPara(53) ' BA

Dim sRightPart As String
Dim sLeftPart As String

' base 26, sort of
sRightPart = Chr(64 + IIf(piColIndex Mod 26 = 0, 26, piColIndex Mod 26))
sLeftPart = ""
If piColIndex > 26 Then
sLeftPart = Chr(64 + IIf(piColIndex Mod 26 = 0, (piColIndex / 26 - 1), Int(piColIndex / 26)))
End If
getNextPara = sLeftPart & sRightPart

End Function
----------------------------------

Now you need to make some slight modifications to the VBA code in your report:

Option Compare Database
Dim miPara As Integer

Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)
miPara = miPara + 1
Me.txtPara = getNextPara(miPara)
End Sub

 

[kporters]

thanks