Calculation on a report 1

[McFestoe]

I have a field on a report that dispalys 2.x 2.A 2.B 2.C 3.A 3.B ans so on, is there a way on a report to have a unbound control that looks at the above field and just displays the letter so x,A,B,C and on that just displayes the number.

 

[dhookom]

just displays the letter" and "displayes the number" are there two different displays that you want?
You can display only the Number portion by using
=Val([YourUnnamedField])
You can use Instr() with Mid() to display everything to the right of the "."
=Mid([YUF],Instr([YUF],".")+1)

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]

 

[sdk]

Hi

I think you want 2.x 2.A 2.B 2.C 3.A 3.B to come out as either:
x, A, B, C, A, B
or
2, 2, 2, 2, 3, 3
is this correct?

There is no built-in functions to do this, but the code below, inserted into a module, can give you these results. Pls note I have made the length of the string optional in the StripNumbers, but this might be overkill.

Cheers

S

[b]
Public Function stripNumbers(inputStr As String, Optional strLength As Integer) As String
[/b]
    If strLength = 0 Then strLength = 1
    'inputStr = "2.x 2.A 2.B 2.C 3.A 3.B" -> x, A, B, C, A, B

    
    Do
        Do While ((VBA.UCase(VBA.Left(inputStr, 1)) < "A" Or _
                  VBA.UCase(VBA.Left(inputStr, 1)) > "Z")) And inputStr <> ""
            inputStr = VBA.Mid(inputStr, 2)
        Loop
        If inputStr = "" Then
            Exit Do
        Else
            If stripNumbers = "" _
                Then stripNumbers = VBA.Left(inputStr, strLength) _
                Else stripNumbers = stripNumbers & ", " & VBA.Left(inputStr, strLength)
            inputStr = VBA.Mid(inputStr, strLength + 1)
        End If
    Loop
    
    
End Function

[b]
Public Function stripText(inputStr As String) As String
[/b]
    Dim intValue As Double
    'inputStr = "2.x 2.A 2.B 2.C 3.A 3.B" -> 2, 2, 2, 2, 3, 3
    
    Do
        Do While (Not VBA.IsNumeric(VBA.Left(inputStr, 1)) And inputStr <> "")
            inputStr = VBA.Mid(inputStr, 2)
        Loop
        
        If inputStr = "" Then
            Exit Do
        Else
            intValue = VBA.Val(inputStr)
            If stripText = "" _
                Then stripText = VBA.Val(intValue) _
                Else stripText = stripText & ", " & VBA.Val(intValue)
            inputStr = VBA.Mid(inputStr, VBA.Len(intValue) + 1)
        End If
    Loop
    
    
End Function

 

[McFestoe]

Thanks Guys

dhookom, thanks for the simple solution, you must have some vast knowledge.

 

[dhookom]

vast knowledge experience

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]