none
Using =substitute() in a query SOLVED RRS feed

  • Question

  • Is it possible to use that in a query?

    The reason I ask is because my data is coming from china and for whatever reason half of their data is using "non-break space". Maybe they are using non-standard keyboads?

    I want to do something like substitute([column],chr(194) & char(160),char(32))


    • Edited by JoseKreif Tuesday, September 8, 2015 4:10 PM
    Tuesday, September 8, 2015 2:40 PM

Answers

  • You can use REPLACE().

    The reason I ask is because my data is coming from china and for whatever reason half of their data is using "non-break space". Maybe they are using non-standard keyboads?

    It's a completely different language then the western ones. Instead of manipulating the data on this level, you should examine the cause. I'm not speaking any Chinese so I cannot tell you whether these characters are important or not.

    • Marked as answer by JoseKreif Tuesday, September 8, 2015 4:16 PM
    Tuesday, September 8, 2015 2:49 PM

All replies

  • You can use REPLACE().

    The reason I ask is because my data is coming from china and for whatever reason half of their data is using "non-break space". Maybe they are using non-standard keyboads?

    It's a completely different language then the western ones. Instead of manipulating the data on this level, you should examine the cause. I'm not speaking any Chinese so I cannot tell you whether these characters are important or not.

    • Marked as answer by JoseKreif Tuesday, September 8, 2015 4:16 PM
    Tuesday, September 8, 2015 2:49 PM
  • You can use REPLACE().

    The reason I ask is because my data is coming from china and for whatever reason half of their data is using "non-break space". Maybe they are using non-standard keyboads?

    It's a completely different language then the western ones. Instead of manipulating the data on this level, you should examine the cause. I'm not speaking any Chinese so I cannot tell you whether these characters are important or not.

    It's not important to China.

    This "space" is causing trouble with grouping. Look here

    At one point, they where using a standard spacebar (chr(32)), eventually, they started using something else.

    By itsself, Replace([Customer].[Sales Person],Chr(160)," ") does the trick. However, in my query, i'm getting the Criteria datatype mismatch error.

    • Edited by JoseKreif Tuesday, September 8, 2015 3:10 PM
    Tuesday, September 8, 2015 2:59 PM
  • Okay, it's about Unicode. Try using ChrW(&HC2A0), but it's hard to test without concrete data.

    Tuesday, September 8, 2015 3:19 PM
  • Yes, Access has a built-in function called Replace().

    Replace([column],chr(194) & chr(160),chr(32))

    Please note it is chr(), not char()


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, September 8, 2015 3:19 PM
  • Please note it is chr(), not char()

    I know, I pronounce it "Char", so when typing it out like I did above, I misspelled it.

    I have my issue solved. There was customers names in the delivery report but not in the customer list. This was causing null values to showup.

    I added a where customer is not null to the criteria/

    I use this:  Replace([Customer].[Sales Person],Chr(160)," ")

    More specifically

    Sales Person: IIf(IsNull([Customer].[Customer Name]),[Customer].[Sales Person],Replace([Customer].[Sales Person],Chr(160)," "))

    Everything is good now.

    • Edited by JoseKreif Tuesday, September 8, 2015 4:17 PM
    Tuesday, September 8, 2015 4:10 PM