InStr Query

[Hargreavesk]

Hi,

I have a cheque which converts it's amount from £513.00 to "Five Hundred Thirteen Pounds". I then use the following code:

FullAmount = Me.Amount_in_Words
Position = InStr(FullAmount, "Hundred&quot + Len("Hundred&quot
LeftAmount = Left(FullAmount, Position)
RightAmount = Right(FullAmount, Len(FullAmount) - Position)
NewAmount = LeftAmount & "and " & RightAmount
Me.Amount_in_Words = NewAmount

This inserts "And" to make the NewAmount "Five Hundred And Thirteen Pounds"

I would like the InStr feature to search and add "And" after all instances of "Hundred" but it's only doing this after the first.

Does anyone know how I can do this?

Many thanks

Kate

 

[grnzbra]

Create this as a function and set the field on the check equal to the function.

Public Function AmtInWordsWAnd(AmtInWords as string) as string

your code starting with:

FullAmount = AmtInWords

and ending with AmtInWordsWAnd = NewAmount

Then set your field equal to :

AmountInWordsWAnd(Me.Amount_In_Words)

Amount_In_Words_with_And = AmtInWordsWAnd(Me.Amount_In_Words)