Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2006
    Posts
    1

    Unanswered: Data Conversion Help Needed

    Hello I am a relative newbie for access and have searched many different sites hoping to find the solutions to my two (seemingly simple) problems. I thought I had found the answers several times but it is not the case. If you could possibly help I would appreciate it greatly.

    First: I have a table with a field with the currency in Euros and no decimal place, i need to add the decimal place two characters before the end and then convert it into dollars and add 1 to the result. I have searched how to run calculations in access and would take the data out and put it into excel and run the calculation but I have too many rows of data and will be doing this calculation often so was hoping to find one simple solution.

    Second: I have one column with text separated by a "-". I was looking to see how I could create two new fields with the first field having the data before the "-" and the second field with the data following. I found a query that allows me to separate first and last name but not a string of data separated by a -. Any help would be greatly appreciated, Brian

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    First:

    cCur(([yourField] / 100) + 1) As yourConvertedField

    Second:

    Left([yourField], InStr([yourField], "-") - 1)
    Right([yourField, Len([yourfield]) - InStr([yourField], "-"))

    You should probably be storing that information seperately to begin with, just fyi.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    To begin with....Welcome to dbforums.

    For the First Item:

    Are you trying to accomplish this change within the table itself or is it in a Form or Report?

    Is this a new or additional field or do you want to modify the Euro Currency field?

    Are you looking to do this with code?

    If you are trying to make this change within a Table, Form, or Report then take a look at the Format property for the Field(s).

    If you want to accomplish this through code then try this:
    Code:
    ' Because the Exchange Rate continually changes, you
    ' will need to take this into consideration within
    ' your convertion calculation'
    '
    ' The Me.myFormTextBoxField below would have the
    ' Format propert set to Currency.
    Me.myFormTextBoxField = (EurosInCurrencyFormat * (ExchangeRateinCurrencyFormatAnd4DecimalPlaces/1)) + 1
    ' To get a live update Exchange Rate from the Internet,
    ' you will obviously need more code.
    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and add 1 to the result
    bizarre!
    ignore, for a second, how you would code it: worry why you would ever want to do something that strange?

    i work for a megaCorp so i am used to obscure ...but + 1 ???????

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    Whoa...nothing Cavalier about that.

    Perhaps...thinking for another second, it's just an obscure number so as not display the actual intended amount...perhaps an echange fee or other rip-off reason.

    Are we bored today Izy? LOL

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hey CL.

    bored - not
    other antisocial adjectives - oh yes, many, maybe even most

    seriously tho: it is a strange calc!

    the reason for asking was not simple sniping - perhaps this is really a "round-up" question in disguise.

    izy
    currently using SS 2008R2

Posting Permissions

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