1 as 1st in report. 1

[lars7]

Hi,
I would like to display 1 as 1st, 2 as 2nd and 3 as 3rd ect; in a report can this be done without changing the values in the table. I thought maybe an "if statement" but I can't find an example of it, or is there an easier way?

Thanks

Tom.

 

[mp9]

Let's say your data field is called dayNum and the text box you want to display it in is called txtDay, then try a select statement something like:

Select Case Right(dayNum, 1)
Case "1"
    txtNum = dayNum & "st"
Case "2"
    txtNum = dayNum & "nd"
Case "3"
    txtNum = dayNum & "rd"
Case Else
    txtNum = dayNum & "th"
End Select

http://www22.brinkster.com/accessory/

 

[lars7]

Hi mp9,
I'm not sure where I should attach the code.

 

[mp9]

In the OnFormat event of the report's Detail section, assuming the txtNum text box is in the Detail section.

http://www22.brinkster.com/accessory/

 

[lars7]

Hi again,
I am getting an error message saying that "you can't assign a value to this object" here is the code I used:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case Right([ADDIT PAYNO], 1)
Case "1"
txtnum = [ADDIT PAYNO] & "st"
Case "2"
txtnum = [ADDIT PAYNO] & "nd"
Case "3"
txtnum = [ADDIT PAYNO] & "rd"
Case Else
txtnum = [ADDIT PAYNO] & "th"
End Select
End Sub

I change the name of my text box to "txtnum"

 

[mp9]

Clear the ControlSource property of the txtNum text box, so that it isn't bound to the [ADDIT PAYNO] field. The ControlSource property of txtNum should be blank.

http://www22.brinkster.com/accessory/

 

[lars7]

Hi,
I did that but now I am getting a message "Microsoft access can't find the field '|' referred to in your expression" and the debugger is highlighting the first line of the code:

Select Case Right([ADDIT PAYNO], 1)

 

[dhookom]

I would create a new standard module with this function:

Public Function GetOrdinal(lngItem As Long) As String
    Dim intOnesDigit As Integer
    Dim strReturn As String
    Dim intTemp As Integer
    ' All numbers 11-19 are "th"
    intTemp = lngItem Mod 100
    If intTemp >= 11 And intTemp <= 19 Then
        strReturn = "th"
    Else
        ' Get that final digit
        intOnesDigit = lngItem Mod 10
        Select Case intOnesDigit
        Case 1
            strReturn = "st"
        Case 2
            strReturn = "nd"
        Case 3
            strReturn = "rd"
        Case Else
            strReturn = "th"
        End Select
    End If
    GetOrdinal = lngItem & strReturn
End Function

Save the module with the name "modStringFunctions". Then, any where you need to display an ordinal value, use:
GetOrdinal([Your Number])
For instance, you can set a control source in a form or report to:
=GetOrdinal([ADDIT PAYNO])
Or in a query you can create a new column that calculates the ordinal value:
OrdinalAdditPayNo: GetOrdinal([ADDIT PAYNO])


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mp9]

Lars, sounds like you have a typo somewhere where you have left a | in that you don't want....

http://www22.brinkster.com/accessory/

 

[dhookom]

There might not be a typo in the code. The solution proposed by mp9 requires that the field [ADDIT PAYNO] must be bound to a control on the report. It is not "good enough" to only have the field in the report's record source.

I would not place code in an event in a report when the code is "generic" and could be used elsewhere. Consider creating the generic function as I suggested so that you learn to write code most efficiently. If you create another report (or form) where you want to display "11" as "11th", you don't want to have to write the same code again.

Make your programming easier by creating functions only once and then using them where ever needed.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lars7]

Hi Dhookom,
Your code works great, I would love to say I understand it all but I will try to work out and I like your tip about modules that makes sense.

Thanks

Tom.