Help turn select case code into a for loop 2

[skyline666]

Hi,

I have this code below which is used to show what the highest denomination a value is (meaning the number 45's highest denomination is 10 (the ten figure), 852's is 100 (the hundred figure)).

Function denom(lngField As String) As Long

Dim intLen As Long
Dim lngDenominator As Long
    
    intLen = Len(lngField)

    Select Case intLen
    Case 1
        lngDenominator = 1
    Case 2
        lngDenominator = 10
    Case 3
        lngDenominator = 100
    Case 4
        lngDenominator = 1000
    End Select
    
    denom = lngDenominator

End Function

Here is the two examples above in the immediate window:

?denom(45)
10
?denom(852)
100

What I would like to do is turn the select case into a for loop, so if say I want it to go to 1,000,000,000 or more, then I don't need to have 10 or more case statements in. Also it would look much tidier.

Any help on this will be much appreciated,

Andrew

PS. Did there used to be a Preview Post button before aswell as the submit button?

 

[combo]

Why loop? denom = 10 ^ (Len(lngField) -1)


combo

 

[skyline666]

Wow cool, I wouldn't of even thought of doing it like that, thanks, have a star.

Andrew

 

[Andrzejek]

Just watch out for the fractions:

lngField = "400.5"

denom = 10 ^ (Len(lngField) -1)

denom = 10000



Have fun.

---- Andy

 

[SkipVought]

...therefore

lngField = "400.5"

denom = 10 ^ (Len(Clng(lngField)) -1)

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[skyline666]

alrite, i'll take a look at this tomorrow as im at home at the moment, thanks.

Andrew

 

[skyline666]

Hi Skip,

I tried putting that CLng part in but it came up with a compile error "Variable required - cant assign to this expression" when I tried ?denom(400.5). If the CLng way wont work, is there a way to tell it to only look at the whole number part, and ignoring anything from the decimal place?

Many thanks,

Andrew

 

[PHV]

Function denom(lngField As String) As Long
  denom = 10 ^ (Len(Int(Val(lngField)) & "") - 1)
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[skyline666]

Thanks PHV, that worked great.

 

[combo]

Andrew,
depending on your input data format and needs, there may be different formulas required, for instance:

- extract left (to '.' decimal separator) part of number:
Len(Split(lngField, ".")(0)

- clean input string by removing thousand separator currency sign etc.:
Replace(lngField, "$", "")

- work with fractions (i.e. 0.1 for 0.15):
work with numbers, use log (Log(lngField) / Log(10#))


combo

 

[skyline666]

combo,

I'll take a look into those see which ones I need, thankyou.

Andrew

 

[skyline666]

Hi combo,

I tried them, but a couple didn't work. The first one didn't work but PHV's way does work though. The third one comes up with an error, the same one before about the variable not defined, and it highlights the division sign.

The second one works. The code I have for them all (made three sep functions now):

Function denomDecimal(lngField As String) As Long

    denomDecimal = 10 ^ (Len(Int(Val(lngField)) & "") - 1)
    
'- extract left (to '.' decimal separator) part of number:
'Len(Split(lngField, ".")(0)

End Function

Function denomCurrency(lngField As String) As Long

    denomCurrency = 10 ^ (Len(Replace(lngField, "£", "")) - 1)
    
'- clean input string by removing thousand separator currency sign etc.:
'Replace(lngField, "$", "")

End Function

Function denomFraction(lngField As String) As Long

    denomFraction = (Len(Log(lngField) / Log(10#)))
    
'- work with fractions (i.e. 0.1 for 0.15):
'work with numbers, use log (Log(lngField) / Log(10#))

End Function

Private Sub currency_AfterUpdate()

    MsgBox denomCurrency(Me.currency)

End Sub

That sub is just how I test it.

 

[combo]

I used different input/output types:

Function denomDecimal(lngField As String) As Long
    denomDecimal = 10 ^ (Len(Int(Val(lngField)) & "") - 1)
End Function

Function denomDecimal2(lngField As String) As Long
    denomDecimal2 = 10 ^ (Len(Split(lngField, ".")(0)) - 1)

'- extract left (to '.' decimal separator) part of number:
'Len(Split(lngField, ".")(0))
End Function


Function denomCurrency(lngField As String) As Long
    denomCurrency = 10 ^ (Len(Replace(lngField, "$", "")) - 1)

'- clean input string by removing thousand separator currency sign etc.:
'Replace(lngField, "$", "")
End Function

Function denomFraction(lngField As Double) As Double
    denomFraction = 10 ^ Int(Log(lngField) / Log(10#))
   
'- work with fractions (i.e. 0.1 for 0.15):
'work with numbers, use log (Log(lngField) / Log(10#))
End Function

Private Sub currency_AfterUpdate()
    MsgBox denomCurrency("$150"), vbOKOnly, "currency"
    MsgBox denomDecimal("450.6"), vbOKOnly, "decimal 1"
    MsgBox denomDecimal2("450.6"), vbOKOnly, "decimal 2"
    MsgBox denomFraction(0.15), vbOKOnly, "fraction"
End Sub

combo

 

[skyline666]

Woohoo its working now. I made one that combines the currency and decimal, as I found that if you put in a decimal number in the currency, it doesn't give the right answer (puts in extra zeros in place of the decimal point, and decimal figures). Here it is:

Function denomWork(lngField As String) As Long

Dim denom1 As Long
Dim denom2 As Long

    denom1 = Replace(lngField, "£", "")
    denom2 = Len(Int(Val(denom1)) & "")
    denomWork = 10 ^ (denom2 - 1)
    
End Function

Thanks again combo and PHV.

Andrew