Results 1 to 12 of 12
  1. #1
    Join Date
    May 2009
    Posts
    72

    Unanswered: dlookup complicated

    Hi
    Iam stuck with dlookup

    I have 3 modules THAT CONVERTS number to words
    GBPTOWORDS =gbptowords(actextboxvalue)... convert to pounds and cents
    USDTOWORDS =usdtowords(actextboxvalue)... convert to dollars and cents
    INRTOWORDS =inrtowords(actextboxvalue)... convert to rupees and paise

    it basically converts numbers to words .
    used in reports

    I have a table geographycurrency with the following field
    curtowords and currency below is the data in this field
    gbptowords gbp
    usdtowords usd
    inrtowords inr

    on the report I have a field cuspaycurrency showing the currency used by the customer

    now to convert number to words what I used to do was
    =inrtowords(amount)

    now since more than one currency is used, iam doing this
    =DLookUp("CURTOWORDS","GEOGRAPHYCURRENCY","CURRENC Y ='" & [CUSPAYCURRENCY] & "'")

    I put the above dlookup expression in a textbox named txtbox1
    then on another textbox I put the expression like this
    =textbox1(AMOUNT)

    but I get #error

    where am I wrong
    please help
    Hemanth

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    GBPTOWORDS =gbptowords(actextboxvalue)... convert to pounds and cents
    I hope not, the UK doens't use cents its Pounds and Pence

    what is textbox1? is it a function or sub routine?
    if so what is the code for that function

    dlookup returns a the first value (column) that matches the criteria. its doesn't return the value form a function

    frankly the way you are doing this baffles me. you are limiting it to 3 currencies (any additional currencies requires more code. in my books it can and should be done as a lookup to a linked SQL table. truth to be told any financial system that is worth doing will be perfectly adequately satisfied by using the ISO 3 digit currency code eg GBP, INR,USD,CAD and so on. there is no need to expand further. the only complications are:-
    where the localisation of the decimal point and thousands separator is different (eg the English speaking world uses 123,456.78, Francophone uses 123.456,78)
    The Indian sub continent which uses the Indian Numbering System separating on 100's (above 10 thousand) rather than the thousands of the Arabic system

    when you say you have a function that converts to words do you actually mean that?

    isa this a cheque writing app that task, say 1,234.56 (or 1.234,56) and converts that to:-
    English
    one thousand, two hundred and thrioty four pounds and fifty six pence
    US
    twelve hundred and thirty four dollars and 56 cents
    OR just use the major and minoir currency symbols
    1,234.56p
    $1,234.56
    however the $ is only truly valid if you prefix the $ with at least two country identifieerrs as plenty of countries use a form of the dollar (USA, Canada, New Zealand and Australia to name 4)

    if you have a function then call that function where required
    if you place the function call in SQL then you won't be able to sum that column in a report as its no longer numeric
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2009
    Posts
    72
    Dear Healdem
    let me rephrase myself
    the table geographycurrency has got all info about a country including its currency, currency format.
    COUNTRY CURRENCY CURFORMAT CURTOWORDS
    BRITAIN GBP #,##0.00 GBPTOW
    EUROPE EURO #,##0.00 EUROTOW
    INDIA INR CURRENCY INRTOW
    USA DOLLAR $#,##0.00 USDTOW
    Last edited by krishnanhemanth; 01-08-14 at 03:04.

  4. #4
    Join Date
    May 2009
    Posts
    72

    dlookup

    I have written separate vba modules for each currency to be converted to words
    usdtow is a vba module to convert numbers to words using dollars and cents.

    for example
    in a report, I have a calculated textbox called "grandtotal"
    now to convert this grandtotal to words, what I do is
    in a textbox the control source is : =usdtow(grandtotal)

    now if different currencies are used by different customer, on the report there is a field called currency which shows the currency used by the customer.
    what I need is to change the conversion to words wrt currency used
    that means, if dollar is used then it is usdtow
    euro is used then it is eurotow and so on
    for this to happen
    on a unbound textbox1 I use this code
    =DLookUp("CURTOWORDS","GEOGRAPHYCURRENCY","CURRENC Y ='" & [CUSPAYCURRENCY] & "'")
    the result displayed for example will be usdtow
    on another unbound textbox2 I did this : =textbox1(grandtotal)
    It is supposed to show in words
    but it gives #error
    Hemanth

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by krishnanhemanth View Post
    I have written separate vba modules for each currency to be converted to words
    usdtow is a vba module to convert numbers to words using dollars and cents.
    I don't see why you would need different modules. You can write several functions in one module.
    Quote Originally Posted by krishnanhemanth View Post
    for this to happen
    on a unbound textbox1 I use this code
    =DLookUp("CURTOWORDS","GEOGRAPHYCURRENCY","CURRENC Y ='" & [CUSPAYCURRENCY] & "'")
    the result displayed for example will be usdtow
    on another unbound textbox2 I did this : =textbox1(grandtotal)
    It is supposed to show in words
    but it gives #error
    Hemanth
    I'm not quite sure that the expression evaluator of Access can handle that. It would be easier to write a dispatcher function that would receive the currency symbol and the amount, then call the adequate converter:
    Code:
    Public Function ToWords(Byval CurrencySymbol As String, Byval Amount As Currency) As String
    
        Select Case CurrencySymbol
            Case "USD":    ToWords = usdtow(Amount)
            Case "EUR":    ToWords = eurtow(Amount)
            Case "GBP":    ToWords = ...
            Case Else:     ' Handle unknown currency symbol
        End Select
    
    End Function
    Now TextBox1 disappears and the property of TextBox2 becomes:
    Code:
    = ToWords(DLookUp("CURTOWORDS","GEOGRAPHYCURRENCY","CURRENCY ='" & [CUSPAYCURRENCY] & "'"), grandtotal)
    Have a nice day!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Also bear in mind that Europe is not a country
    Member states of the European Union overwhelmingly use the Euro, not all do
    there's plenty who argue that the Euro may be on its deathbed

    is this a multi currency application or a single application that will be used in different countries. oif the latter then the computers localisation setting should handle this transparently, without any further code and without coercing numbers into text. by using the currency format
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2009
    Posts
    72
    Thanks sinndho
    Your solution is in the right direction
    taking this a bit forward, can this code be made modular
    while using the case statement, the user is limited to only those currencies that are coded
    thanks again
    Hemanth

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I don't see how the program could use currencies that are not encoded. However, if they are in a table, you can resort to some kind of DLookup (possibly coupled with an Eval() function: Eval Function - Access - Office.com) mechanism. In any case, the system won't be able to call an AmountToWords function that does not exist. It's your responsability to implement some sort of control mechanism that prevents a user from using an unknown currency symbol as well as writing the AmountToWords functions for the currencies that the program can handle.
    Have a nice day!

  9. #9
    Join Date
    May 2009
    Posts
    72
    Hi Sinndho
    all the currency info are there in a table
    every currency has a amounttowords function coded separately
    usdtoword
    eurotoword
    inrtoword
    gbptoword

    iam attaching the table in text format
    Attached Files Attached Files

  10. #10
    Join Date
    May 2009
    Posts
    72
    Hi Sinndho
    Tried the above function you suggested
    its showing error

    towords...enter parameter value

    Hemanth

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by krishnanhemanth View Post
    Hi Sinndho
    all the currency info are there in a table
    every currency has a amounttowords function coded separately
    usdtoword
    eurotoword
    inrtoword
    gbptoword
    Having all the currencies encoded in a table does not mean that you also have all the functions converting an amount in these currencies into words. By the way, I wonder how you'll manage the case of the Euro, where several langages will be needed.
    Quote Originally Posted by krishnanhemanth View Post
    Hi Sinndho
    Tried the above function you suggested
    its showing error

    towords...enter parameter value
    I'm no mind reader and I'm not in front of your computer. Which function are talking about?
    Have a nice day!

  12. #12
    Join Date
    May 2009
    Posts
    72

    dlookup

    Sorry, I was not clear in my briefing

    this is the function

    Public Function ToWords(ByVal CurrencySymbol As String, ByVal NTS As Currency) As String

    Select Case CurrencySymbol
    Case "USD": ToWords = usdtow(NTS)
    Case "EUR": ToWords = eurtow(NTS)
    Case "GBP": ToWords = gbptow(NTS)
    Case "INR": ToWords = inrtow(NTS)
    Case Else: ToWords = inrtow(NTS)
    End Select
    End Function

Posting Permissions

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