Results 1 to 9 of 9
  1. #1
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167

    changing CDN to CAD

    Can someone show me how to write the expressions that would update my query as follows:



    records with [curr] = "CDN" to [NewField] = "CAD"

    while at the same time...

    records with [curr] = "USD" to [NewField] = "USD" ?



    My plan is to add this {NewField] to the query and use this data as my new currency field. It's a lot easier than dealing with the impact of having the previous records changed from CDN to CAD permanently.

    Can anyone suggest how the expression would look in the query field?

    thanks in advance.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm not sure what you are asking? Are you changing a field/column name OR currency values??
    Perhaps this ISO Currency standard is related??

  3. #3
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    sorry i thought i explained this well enought.

    I have a table with
    [amount] and [currency] fields

    the Canadian currencies have been entered as CDN and not CAD

    I don't want to update these permanently in the table as i need them stated as CDN for another purpose

    I do want to create a query that will that lists all the records - both CDN and USD
    I'm going to add a field to the query that for the sake of this thread will be called [newcurrency].
    I want the expression for this new field to look at the [currency] field of each record and if the [currency]= "CDN" update [newcurrency] to "CAD", and if the [currency] = "USD" then update [newcurrency] to "USD"

    this way the records will remain as they originally were but the query will return CAD and USD records (instead of CDN and USD)

    The new field in the query will be NEWCURRENCY: IF [currency] = CDN update to CAD otherwise [newcurrency] = [currency]

    i know this expression above is totally wrong but i dont know how to properly do what i'm trying to do.

    I'll use this query as the record source for a form that will show "CAD or USD" not "CDN or USD"



    hopefully this explains things better.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In design view try:

    newcurrency: IIf(currency = "CDN", "CAD", currency)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    1: I do want to create a query that will that lists all the records - both CDN and USD
    Code:
    Select fields, currency from YourTable where currency in ("CDN","USD")
    2: I'm going to add a field to the query that for the sake of this thread will be called [newcurrency].
    I want the expression for this new field to look at the [currency] field of each record and if the [currency]= "CDN" update [newcurrency] to "CAD", and if the [currency] = "USD" then update [newcurrency] to "USD


    Select IIF(currency = "CDN","CAN",Currency ) as NewCurrency from yourTABLE

  6. #6
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Create a new table with two fields CurrencyCode(ShortText)/AlternateCurrencyCode(ShortText) and add these records in there:

    CAD|CDN
    CAD|CAD
    USD|USD

    Now in your queries you can link this by the alternate code to your original table [Curr] field and it will convert all possible variation to your desired outcome.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by fishhead View Post

    I have a table with
    [amount] and [currency] fields
    Be aware that "CURRENCY" is a reserved word in Access and shouldn't be used as object names.

  8. #8
    fishhead is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    167
    this worked perfectly thanks Paul!

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by fishhead View Post
    this worked perfectly thanks Paul!
    Happy to help! The table is more dynamic if there may be more combinations.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. 2/3 changing to ?
    By bubbaharleyd in forum Forms
    Replies: 1
    Last Post: 01-15-2019, 10:51 AM
  2. Changing 0 to -1 to Yes/No
    By vector39 in forum Access
    Replies: 5
    Last Post: 06-01-2017, 02:13 PM
  3. changing sql qrt to access qry
    By pnasz in forum Access
    Replies: 1
    Last Post: 08-07-2014, 06:26 AM
  4. Changing VBA to do more
    By data808 in forum Programming
    Replies: 1
    Last Post: 02-03-2014, 02:29 AM
  5. Please Help With Changing Heading
    By angelln in forum Access
    Replies: 3
    Last Post: 08-05-2010, 09:01 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums