Public Function convert Text w colons to currency 1

[jpl458]

Working with a data base that, for some reason stores currency as text fields separated with semi-colons(. A field (column) will look somthing like this: 7.50;0;0;0;0;0;0;0;0;0 , where the first 4 characters represent a dollar amount. The value will either be a zero or some dollar value, the string never starts with a semi-colon I am only interested in looking and the first dollar value-$7.50 in the example.

I have created a function that spins through the field till it hits the first ; , then returns the value as currency. Here is the code:

Public Function RecFees(Fees As String) As Currency

Dim L As Integer
L = 1
For L = 1 To Len(Fees)
'Look for the colon
If Left(Fees, L) = ";" Then
RecFees = Left(Fees, L - 1)
Exit For
Else
End If
Next L
End Function

When I test in the immediate window I get 0, which is wrong.

If I try to run a query i get undfined function RecFees.

I think I neet to convert the text to currency in the function, bit I can't make it work.

Any help wiuld be greatly appreciated.

Thanks

jpl

 

[PHV]

What about s this ?

Public Function RecFees(Fees As String) As Currency
RecFees = Split(Fees, ";")(0)
End Function

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

 

[strongm]

Or

[blue]Public Function RecFees(Fees As String) As Currency
    RecFees = Val(Fees)
End Function[/blue]

although of course you shoud be able to use Val directly in the query (er, assuming that you are working in Access)