InStr 1

[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

 

[BSman]

Is your intention to convert the text stored in every record, or to only add the "and" when you print the check? If you are converting only when you print, then set up a control to hold the text stored in the record (let's call it me.Amount_in_Words_original). This control would be hidden on your report. Then change the first line of your code to read:
FullAmount = Me.Amount_in_Words_original

If you are converting as a check is entered (I assume the user would enter the amount and that gets converted to text without the "and&quot, follow the same method.

If you want to convert all records in a table, then you must also verify that the "and" doesn't already exist in a record before adding it. In that case it might be easier to use an updatable recordset.

 

[Hargreavesk]

I'm sorry I don't think I explained correctly.

The words are converted when the cheque is printed but if I have something like "£155,355.00" It converts to "One Hundred and Fifty Five Thousand Three Hundred Fifty Five Pounds"

The and is inserted after the first hundred is found but not after the second hundred in the sentence.

Can anyone help????

BSMan, thanks for trying... )

Kate

 

[BSman]

Actually, that makes it simpler.
Do the Position statement a second time, but change it to start after the position of the first "hundred", something like this:

Position2 = InStr(Position,FullAmount, "Hundred&quot + Len("Hundred&quot
If Position2 > 0 then
Position2 = Position + Position2
' now add a variation of your statement to insert the "and"
' a second time if Position2 is greater than 0 (meaning
' that a second "hundred" exists)
else
' here is where you use your current "and" insertion statement
end if

 

[Hargreavesk]

BSMan,

Thanks for your help but I am still having problems trying to get it to work. Do you have a full layout I can have a look at as some things are working and others are not. I think I may have a few things the wrong way around...

sorry

Kate )

 

[BSman]

OK, here's a variation on the approach I suggested:

Dim LeftAmount2 as String
FullAmount = Me.Amount_in_Words
Position = InStr(FullAmount, "Hundred&quot + Len("Hundred&quot
LeftAmount = Left(FullAmount, Position)
RightAmount = Right(FullAmount, Len(FullAmount) - Position)
Position = InStr(RightAmount, "Hundred&quot + Len("Hundred&quot
if Position > 0 then
LeftAmount = LeftAmount & Left(RightAmount, Position)
RightAmount = Right(RightAmount, Len(RightAmount) - Position)
end if
NewAmount = LeftAmount & "and " & RightAmount
Me.Amount_in_Words = NewAmount

 

[Hargreavesk]

BSman,

Thanks, I seem to have got most things working but if I put in an amount say £155.00 then it is still picking up the extra "hundred" despite there only being 1. Any ideas???

Thanks

Kate

 

[BSman]

After the statement that looks for the second hundred by doing the instring on RightAmount, temporarily put in a statement
msgbox "Position = " & Position & " in " & RightAmount

This way you can see what is being returned and what the contents of RightAmount are. That should provide guidance in what to do to fix the problem.

You might also put this statement just before the second hundred Instr statement:
Position = 0

That will make sure that the previous value of Position doesn't carry over.

You could also change the second If Position to read:
If Position > 0 AND Len(RightAmount) > 6 then

This is because the length of "hundred" is 7 characters, so RightAmount must be at least 7 characters to contain the word.

 

[jebry]

Hi Kate!

It might be easier to look in the function that converts the numbers to words and add your 'And' there. If you want to do it this way then you need to do it in a loop:

Public Function AddAnds(strInitial As String) As String

Dim lngPosition As Long
Dim strReturn As String

strReturn = Trim(strInitial)
lngPosition = InStr(strReturn, "Hundred&quot
Do Until lngPosition = 0
strReturn = Left(strReturn, lngPosition + 6) & " And" & Mid(strReturn, lngPosition + 7)
lngPostion = InStr(lngPosition + 7, strReturn, "Hundred&quot
Loop
If Right(strReturn, 3) = "And" Then
strReturn = Left(strReturn, Len(strReturn) - 4)
End If

AddAnds = strReturn

This will add an And after every hundred. It will also remove the and in cases where you have &100.

hth
Jeff Bridgham
bridgham@purdue.edu