# Thread: Dlookup ultimate

1. Registered User
Join Date
May 2009
Posts
72

## Unanswered: Dlookup ultimate

HI
I have a table tblgeography that contains the following fields
country....................usa
currency..................dollar
currencyformat.........\$
currencytowords.......dollartowords

the data is there for lots of other countries also

the currencytowords field have dollartowords as its value for usa, eurotowords for Europe, poundstowords for Britain

in the module, I have created the following function
=dollartowords()
=eurotowords()
=gbptowords()
I put a neumerical value in the brackets and it converts it to words appropriately wrt to the currency
example
=dollartowords(1.50) gives one dollar and 50 cents only

on a report I have a field called paycurrency that displays the currency
The issue
I have to convert the number in a text box to words depending on the paycurrency value

what I did
=DLookUp("CURRENCYTOWORDS"," TBLGEOGRAPHY","GEOCURRENCY ='" & [PAYCURRENCY] & "'")
THIS RETURNS A VALUE OF ..eurotowords

from here iam not able to take it forward
using the value returned, I want to convert the number to words

any help is appreciated
Hemanth

2. Registered User
Join Date
Apr 2014
Location
Kentucky
Posts
630
You need a recursive function like this:

Code:
```'This function spells out the number given (like on a check)
'DO NOT USE COMMAS

Public Function SpellNum(Optional ByVal psNum)
Dim vTxt, vNum, vWord, vN1, vN2
Dim X As Integer, l As Integer, i As Integer
Dim vCents

If IsMissing(psNum) Then
SpellNum = ""
Exit Function
End If

If InStr(psNum, ".") > 0 Then
i = InStr(psNum, ".")
vCents = Mid(psNum, i + 1)
psNum = Left(psNum, i - 1)
End If

If Len(psNum) = 1 And Val(psNum) = 0 Then
SpellNum = "ZERO"
Exit Function
End If

If Val(psNum) = 0 Then
i = -1
Else
i = Len(psNum)
End If

Select Case i
Case -1
SpellNum = ""
Exit Function

Case 1
X = 0
Select Case Val(psNum)
Case 0
vTxt = ""
Case 1
vTxt = "ONE "
Case 2
vTxt = "TWO "
Case 3
vTxt = "THREE "
Case 4
vTxt = "FOUR "
Case 5
vTxt = "FIVE "
Case 6
vTxt = "SIX "
Case 7
vTxt = "SEVEN "
Case 8
vTxt = "EIGHT "
Case 9
vTxt = "NINE "
End Select

Case 2
X = 0
Select Case Val(psNum)
Case 10
vTxt = "TEN "
Case 11
vTxt = "ELEVEN "
Case 12
vTxt = "TWELVE "
Case 13
vTxt = "THIRTEEN "
Case 14
vTxt = "FORTEEN "
Case 15
vTxt = "FIFTEEN "
Case 16
vTxt = "SIXTEEN "
Case 17
vTxt = "SEVENTEEN "
Case 18
vTxt = "EIGHTEEN "
Case 19
vTxt = "NINETEEN "
Case Else
Select Case Val(Left(psNum, 1))
Case 2
vTxt = "TWENTY "
Case 3
vTxt = "THIRTY "
Case 4
vTxt = "FORTY "
Case 5
vTxt = "FIFTY "
Case 6
vTxt = "SIXTY "
Case 7
vTxt = "SEVENTY "
Case 8
vTxt = "EIGHTY "
Case 9
vTxt = "NINETY "
End Select

X = Val(Right(psNum, 1))
vTxt = vTxt & SpellNum(X)
X = 0
End Select

Case 3
X = 2
vTxt = psNum
vWord = "HUNDRED "

Case 4, 5, 6
X = 3
vWord = "THOUSAND "

Case 7, 8, 9
X = 6
vWord = "MILLION "

Case 10, 11, 12
X = 9
vWord = "BILLION "
End Select

If X > 0 Then
l = Len(psNum) - X
vN1 = Left(psNum, l)
vN2 = Mid(psNum, l + 1)
vTxt = SpellNum(vN1) & vWord
vTxt = vTxt & SpellNum(vN2)
End If

If Len(vCents) > 0 Then
SpellNum = vTxt & " and " & vCents & " cents"
Else
SpellNum = vTxt
End If
End Function```

3. Registered User
Join Date
May 2009
Posts
72
thanks for the reply ranman
but I have already created a function to convert to words

my only requirement is to change the currency type based on a parameter

quote from my previous post

in the module, I have created the following function
=dollartowords()
=eurotowords()
=gbptowords()
I put a neumerical value in the brackets and it converts it to words appropriately wrt to the currency
example
=dollartowords(1.50) gives one dollar and 50 cents only

on a report I have a field called paycurrency that displays the currency
The issue
I have to convert the number in a text box to words depending on the paycurrency value

what I did
=DLookUp("CURRENCYTOWORDS"," TBLGEOGRAPHY","GEOCURRENCY ='" & [PAYCURRENCY] & "'")
THIS RETURNS A VALUE OF ..eurotowords

from here iam not able to take it forward
using the value returned, I want to convert the number to words

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
you would be better off passing the task into a handler function, which in turn calls the appropriate currency formatter

public sub HandleCurrencyToWords(Amount as double, CurrencyCode as string, CountryCode as String)
select case CurrencyCode
case is "USD"
.....insert call to USdollartowords using whatever parameters you need
case is "CAD","AUD","ECD" .......
.....insert call to nonUSdollartowords using whatever parameters you need
case is "GBP"
.....insert call to GBPtowords using whatever parameters you need
case is "EUR"
select case CountryCode
case is "IRL" ...select the Irish handler
case is...... repeat for the other 29 odd countries in th4e EUro
end select
end select
end sub

bear in mind inside the EURO zone some countries use "." to separator thousands, "," decimals and some use the UK/US convention
not all countries use thousands, some use hundreds

eg 1234.56 could be
one thousand, two hundred and thirty four <currency> and fifty six
twelve hundred and thirty four <currency> and fifty six

others such as India use a different base altogether

5. Registered User
Join Date
May 2009
Posts
72
thanks healdem for the reply
can you please post one example code
it would be great and appreciated

thanks

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Code:
```public sub HandleCurrencyToWords(Amount as double, CurrencyCode as string, CountryCode as String)
select case CurrencyCode
case is "USD"
.....insert call to USdollartowords using whatever parameters you need
case is "CAD","AUD","ECD" .......
.....insert call to nonUSdollartowords using whatever parameters you need
case is "GBP"
.....insert call to GBPtowords using whatever parameters you need
case is "EUR"
select case CountryCode
case is "IRL" ...select the Irish handler
case is...... repeat for the other 29 odd countries in th4e EUro
end select
end select
end sub```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•