Split words in textbox? (1 Viewer)

B

BackatchaBandit

Guest
Hello!

I've just managed to add a button to my Access 2000 based sales ledger to write out cheques (I can never find my biro)...

The problem I'm gettting is that the 'amount in words' textbox splits words (does a carrige return mid word), e.g:

"One Hundred and Fif
ty Pounds"

Whereas obviously I'd like it to keep words together.

I've looked at the various options in the alignment property of the textbox on the report, but no joy.

To print the amount as words I used the following module:

Function ConvertCurrencyToEnglish (ByVal MyNumber)
Dim Temp
Dim Pounds, Pence
Dim DecimalPlace, Count

ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "

' Convert MyNumber to a string, trimming extra spaces.
MyNumber = Trim(Str(MyNumber))

' Find decimal place.
DecimalPlace = InStr(MyNumber, ".")

' If we find decimal place...
If DecimalPlace > 0 Then
' Convert Pence
Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
Pences = ConvertTens(Temp)

' Strip off Pences from remainder to convert.
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If

Count = 1
Do While MyNumber <> ""
' Convert last 3 digits of MyNumber to English Pounds.
Temp = ConvertHundreds(Right(MyNumber, 3))
If Temp <> "" Then Pounds = Temp & Place(Count) & Pounds
If Len(MyNumber) > 3 Then
' Remove last 3 converted digits from MyNumber.
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop

' Clean up Pounds.
Select Case Pounds
Case ""
Pounds = "No Pounds"
Case "One"
Pounds = "One Pound"
Case Else
Pounds = Pounds & " Pounds"
End Select

' Clean up Pence.
Select Case Pence
Case ""
Pence = " And No Pence"
Case "One"
Pences = " And One Pence"
Case Else
Pences = " And " & Pence & " Pence"
End Select

ConvertCurrencyToEnglish = Pounds & Pence
End Function

Private Function ConvertHundreds (ByVal MyNumber)
Dim Result As String

' Exit if there is nothing to convert.
If Val(MyNumber) = 0 Then Exit Function

' Append leading zeros to number.
MyNumber = Right("000" & MyNumber, 3)

' Do we have a hundreds place digit to convert?
If Left(MyNumber, 1) <> "0" Then
Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "
End If

' Do we have a tens place digit to convert?
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & ConvertTens(Mid(MyNumber, 2))
Else
' If not, then convert the ones place digit.
Result = Result & ConvertDigit(Mid(MyNumber, 3))
End If

ConvertHundreds = Trim(Result)
End Function

Private Function ConvertTens (ByVal MyTens)
Dim Result As String

' Is value between 10 and 19?
If Val(Left(MyTens, 1)) = 1 Then
Select Case Val(MyTens)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else
' .. otherwise it's between 20 and 99.
Select Case Val(Left(MyTens, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select

' Convert ones place digit.
Result = Result & ConvertDigit(Right(MyTens, 1))
End If

ConvertTens = Result
End Function

Private Function ConvertDigit (ByVal MyDigit)
Select Case Val(MyDigit)
Case 1: ConvertDigit = "One"
Case 2: ConvertDigit = "Two"
Case 3: ConvertDigit = "Three"
Case 4: ConvertDigit = "Four"
Case 5: ConvertDigit = "Five"
Case 6: ConvertDigit = "Six"
Case 7: ConvertDigit = "Seven"
Case 8: ConvertDigit = "Eight"
Case 9: ConvertDigit = "Nine"
Case Else: ConvertDigit = ""
End Select
End Function


Anyone got any ideas?

Thanks in advance. :)

BB
 
B

BackatchaBandit

Guest
If only it were that simple, Rich...

The text box has to be the same size as the space on the blank cheque that I feed into the printer, besides, each 'value as english' is a different length...

Anyone el
se got an idea? ;)
 

oihjk

Registered User.
Local time
Today, 02:01
Joined
Feb 13, 2003
Messages
51
Would a smaller font work here? Maybe I'm missing the idea of what you want.

Eric
 
B

BackatchaBandit

Guest
Hi Eric

A standard cheque here in the UK has two lines for the 'amount in words'

I have got the line spacing right (at 1cm) for it to spill onto the second line if required, but the words are often split:

One hundred and ten pou
nds and thirty pence

What I want is:

One hundred and ten
pounds and thirty pence

Basically, I don't want the words to be split at the end of a line (carridge returns mid-word) rather only where there is a space - something most text boxes seem to do automatically.

It's probably something to do with the module above.

Cheers.
 
R

Rich

Guest
Does that indicate that you have two textboxes, or just one with the can grow set to yes?
 

Fornatian

Dim Person
Local time
Today, 07:01
Joined
Sep 1, 2000
Messages
1,396
Hmmm....

I wonder if specify the Function as a string would solve it?

Function ConvertCurrencyToEnglish (ByVal MyNumber) As String

Seems very wei
rd because my t
extboxes don'
t split acro
ss words
 

Fornatian

Dim Person
Local time
Today, 07:01
Joined
Sep 1, 2000
Messages
1,396
Does me too Rich, just wondering whether the text box would read the function return differently and apply wraps if it's a string - probably way off.
 

Users who are viewing this thread

Top Bottom