How to show numbers in text format... 1

PaulChilds · Oct 24, 2002

Bit of an odd one this...

I have a table which records client information. From this I have a query which is used to create a report.

The report is for a bank mandate and has to show a payment amount in figures and text. At the minute the table has a field called 'Payment' (which is set as a currency) in which you would enter '150'(eg) and a text field called 'Payment (in words)' - in which you then enter 'One Hundred and Fifty'.

Is there any way that it can be set so that the database recognises '150' and puts the corresponding word/s in the next column?

If not, never mind - if so, it would save the users a lot of time when they are entering in lots of data.

Cheers.

Paul C

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

SgtJarrow · Oct 24, 2002

The below code will spell out dollar amounts liek on checks.......you can probably modify the output some to make it work......

''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function SpellDollar(dblValue As Double) As String

' Usage - To test it, open the debug window (ctrl+G) and type: ?SpellDollar(any number you want), and hit enter

Dim centspot As Integer, dollarlen As Integer
Dim dollars As Double, Cents As Integer
Dim Hundreds As Integer, Thousands As Integer
Dim Millions As Integer, Billions As Integer
Dim centtext As String, huntext As String, thoutext As String
Dim milltext As String, billtext As String, temptext As String

If dblValue < 0.01 Then
temptext = "Zero"
Else
centspot = InStr(dblValue, ".&quot

If centspot > 0 Then
Cents = Right(dblValue, 2)
dollars = Left(dblValue, centspot - 1)
Else
Cents = 0
dollars = dblValue
End If

dollarlen = Len(CStr(dollars))

If dollarlen > 0 Then
Select Case dollars
Case 1 To 999
Select Case dollars
Case 1 To 9
Hundreds = Right(dollars, 1)
Case 10 To 99
Hundreds = Right(dollars, 2)
Case 100 To 999
Hundreds = Right(dollars, 3)
End Select
Case 1000 To 999999
Hundreds = Right(dollars, 3)
Select Case dollars
Case 1000 To 9999
Thousands = Left(dollars, 1)
Case 10000 To 99999
Thousands = Left(dollars, 2)
Case 100000 To 999999
Thousands = Left(dollars, 3)
End Select
Case 1000000 To 999999999
Hundreds = Right(dollars, 3)
Thousands = Mid(dollars, (dollarlen - 5), 3)
Select Case dollars
Case 1000000 To 9999999
Millions = Left(dollars, 1)
Case 10000000 To 99999999
Millions = Left(dollars, 2)
Case 100000000 To 999999999
Millions = Left(dollars, 3)
End Select
Case 1000000000 To 999999999999#
Hundreds = Right(dollars, 3)
Thousands = Mid(dollars, (dollarlen - 5), 3)
Millions = Mid(dollars, (dollarlen - 8), 3)
Select Case dollars
Case 1000000000 To 9999999999#
Billions = Left(dollars, 1)
Case 10000000000# To 99999999999#
Billions = Left(dollars, 2)
Case 100000000000# To 999999999999#
Billions = Left(dollars, 3)
End Select
End Select
End If

If Hundreds > 0 Then huntext = GetDollarString("hundreds", Hundreds)
If Thousands > 0 Then thoutext = GetDollarString("thousands", Thousands)
If Millions > 0 Then milltext = GetDollarString("millions", Millions)
If Billions > 0 Then billtext = GetDollarString("billions", Billions)

If billtext = "" Then
If milltext = "" Then
If thoutext = "" Then
If huntext = "" Then
temptext = centtext
Else
temptext = huntext & " Dollars and " & Cents & "/100"
End If
Else
temptext = thoutext & " " & huntext & " Dollars and " & Cents & "/100"
End If
Else
temptext = milltext & " " & thoutext & " " & huntext & " Dollars and " & Cents & "/100"
End If
Else
temptext = billtext & " " & milltext & " " & thoutext & " " & huntext & " Dollars and " & Cents & "/100"
End If
End If

If Right(temptext, 1) = "d" Then temptext = Left(temptext, Len(temptext) - 4)
SpellDollar = Trim(temptext)
End Function

Function GetDollarString(strPart As String, intPart As Integer) As String
Dim strDollars As String

If intPart > 99 Then
Select Case Mid(intPart, 1, 1)
Case 1: strDollars = "One Hundred"
Case 2: strDollars = "Two Hundred"
Case 3: strDollars = "Three Hundred"
Case 4: strDollars = "Four Hundred"
Case 5: strDollars = "Five Hundred"
Case 6: strDollars = "Six Hundred"
Case 7: strDollars = "Seven Hundred"
Case 8: strDollars = "Eight Hundred"
Case 9: strDollars = "Nine Hundred"
End Select
End If

Select Case Right(intPart, 2)
Case 1: strDollars = strDollars & " One"
Case 2: strDollars = strDollars & " Two"
Case 3: strDollars = strDollars & " Three"
Case 4: strDollars = strDollars & " Four"
Case 5: strDollars = strDollars & " Five"
Case 6: strDollars = strDollars & " Six"
Case 7: strDollars = strDollars & " Seven"
Case 8: strDollars = strDollars & " Eight"
Case 9: strDollars = strDollars & " Nine"
Case 10: strDollars = strDollars & " Ten"
Case 11: strDollars = strDollars & " Eleven"
Case 12: strDollars = strDollars & " Twelve"
Case 13: strDollars = strDollars & " Thirteen"
Case 14: strDollars = strDollars & " Fourteen"
Case 15: strDollars = strDollars & " Fifteen"
Case 16: strDollars = strDollars & " Sixteen"
Case 17: strDollars = strDollars & " Seventeen"
Case 18: strDollars = strDollars & " Eighteen"
Case 19: strDollars = strDollars & " Nineteen"
Case 20: strDollars = strDollars & " Twenty"
Case 21: strDollars = strDollars & " Twenty One"
Case 22: strDollars = strDollars & " Twenty Two"
Case 23: strDollars = strDollars & " Twenty Three"
Case 24: strDollars = strDollars & " Twenty Four"
Case 25: strDollars = strDollars & " Twenty Five"
Case 26: strDollars = strDollars & " Twenty Six"
Case 27: strDollars = strDollars & " Twenty Seven"
Case 28: strDollars = strDollars & " Twenty Eight"
Case 29: strDollars = strDollars & " Twenty Nine"
Case 30: strDollars = strDollars & " Thirty"
Case 31: strDollars = strDollars & " Thirty One"
Case 32: strDollars = strDollars & " Thirty Two"
Case 33: strDollars = strDollars & " Thirty Three"
Case 34: strDollars = strDollars & " Thirty Four"
Case 35: strDollars = strDollars & " Thirty Five"
Case 36: strDollars = strDollars & " Thirty Six"
Case 37: strDollars = strDollars & " Thirty Seven"
Case 38: strDollars = strDollars & " Thirty Eight"
Case 39: strDollars = strDollars & " Thirty Nine"
Case 40: strDollars = strDollars & " Forty"
Case 41: strDollars = strDollars & " Forty One"
Case 42: strDollars = strDollars & " Forty Two"
Case 43: strDollars = strDollars & " Forty Three"
Case 44: strDollars = strDollars & " Forty Four"
Case 45: strDollars = strDollars & " Forty Five"
Case 46: strDollars = strDollars & " Forty Six"
Case 47: strDollars = strDollars & " Forty Seven"
Case 48: strDollars = strDollars & " Forty Eight"
Case 49: strDollars = strDollars & " Forty Nine"
Case 50: strDollars = strDollars & " Fifty"
Case 51: strDollars = strDollars & " Fifty One"
Case 52: strDollars = strDollars & " Fifty Two"
Case 53: strDollars = strDollars & " Fifty Three"
Case 54: strDollars = strDollars & " Fifty Four"
Case 55: strDollars = strDollars & " Fifty Five"
Case 56: strDollars = strDollars & " Fifty Six"
Case 57: strDollars = strDollars & " Fifty Seven"
Case 58: strDollars = strDollars & " Fifty Eight"
Case 59: strDollars = strDollars & " Fifty Nine"
Case 60: strDollars = strDollars & " Sixty"
Case 61: strDollars = strDollars & " Sixty One"
Case 62: strDollars = strDollars & " Sixty Two"
Case 63: strDollars = strDollars & " Sixty Three"
Case 64: strDollars = strDollars & " Sixty Four"
Case 65: strDollars = strDollars & " Sixty Five"
Case 66: strDollars = strDollars & " Sixty Six"
Case 67: strDollars = strDollars & " Sixty Seven"
Case 68: strDollars = strDollars & " Sixty Eight"
Case 69: strDollars = strDollars & " Sixty Nine"
Case 70: strDollars = strDollars & " Seventy"
Case 71: strDollars = strDollars & " Seventy One"
Case 72: strDollars = strDollars & " Seventy Two"
Case 73: strDollars = strDollars & " Seventy Three"
Case 74: strDollars = strDollars & " Seventy Four"
Case 75: strDollars = strDollars & " Seventy Five"
Case 76: strDollars = strDollars & " Seventy Six"
Case 77: strDollars = strDollars & " Seventy Seven"
Case 78: strDollars = strDollars & " Seventy Eight"
Case 79: strDollars = strDollars & " Seventy Nine"
Case 80: strDollars = strDollars & " Eighty"
Case 81: strDollars = strDollars & " Eighty One"
Case 82: strDollars = strDollars & " Eighty Two"
Case 83: strDollars = strDollars & " Eighty Three"
Case 84: strDollars = strDollars & " Eighty Four"
Case 85: strDollars = strDollars & " Eighty Five"
Case 86: strDollars = strDollars & " Eighty Six"
Case 87: strDollars = strDollars & " Eighty Seven"
Case 88: strDollars = strDollars & " Eighty Eight"
Case 89: strDollars = strDollars & " Eighty Nine"
Case 90: strDollars = strDollars & " Ninety"
Case 91: strDollars = strDollars & " Ninety One"
Case 92: strDollars = strDollars & " Ninety Two"
Case 93: strDollars = strDollars & " Ninety Three"
Case 94: strDollars = strDollars & " Ninety Four"
Case 95: strDollars = strDollars & " Ninety Five"
Case 96: strDollars = strDollars & " Ninety Six"
Case 97: strDollars = strDollars & " Ninety Seven"
Case 98: strDollars = strDollars & " Ninety Eight"
Case 99: strDollars = strDollars & " Ninety Nine"
End Select

ConstructString:
Select Case strPart
Case "cents"
GetDollarString = strDollars & "/100"
Case "hundreds"
GetDollarString = strDollars
Case "thousands"
GetDollarString = strDollars & " Thousand"
Case "millions"
GetDollarString = strDollars & " Million"
Case "billions"
GetDollarString = strDollars & " Billion"
End Select
End Function Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! [thumbsup2]

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

PaulChilds · Oct 24, 2002

Once I have copied and pasted the code into VB, how do I then make it work?

I seem to be a bit stuck with this.

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

SgtJarrow · Oct 24, 2002

OK...the code should have been copied into a module and saved...any name does not matter.....

The form you are inputing data through (yes I said form....shame on you if the users are/will be inputing data directly into the table...that's a definite no-no) has two fields...one for the numeric number and one for the text number....set the text number field's enabled property to no and the locked property to yes.

Using the afterupdate property of the numeric number field, place the following....

Me![name of text number field] = SpellDollar(Me![name of numeric number field])

That should be it....

If you are still having difficulties...shoot me an email and I will put together a sample database for you...... Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! [thumbsup2]

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

PaulChilds · Oct 24, 2002

Thanks!

Got it working, will adjust it to fit my database.

I'll give you a star!

CHeers

Paul C

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

PaulChilds · Nov 13, 2002

I seem to have got a bit stuck with this.

I want it to show the correct British grammar, so it says e.g 'One Hundred and One' rather than 'One Hundred One' and so on.

I've tried to fix this but keep getting my 'and's in the wrong place.

Any ideas?

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

How to show numbers in text format... 1

PaulChilds

Technical User

SgtJarrow

Programmer

PaulChilds

Technical User

SgtJarrow

Programmer

PaulChilds

Technical User

PaulChilds

Technical User

Similar threads

Part and Inventory Search

Sponsor