Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Update the imported fields with Replace function

  1. #1
    Board Regular
    Join Date
    May 2013
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Update the imported fields with Replace function

    Hi,

    Just want to have your opinion on how to resolve the issue, i have Text field to upload in my Table "Short Text", and i need to update the field SI_ICXX to remove spaces, however, i think it automatically converted to Numbers.

    Sample SI field(Short Text):12333 02 01 i need to update as 123330201.

    Error in the upload is Type Conversion Failure

    Code:
    Upd_SI_field = "UPDATE MM_Input SET MM_Input.SI_ICXX =(REPLACE(MM_Input.SI_ICXX,"" "",""""))"
    
        DoCmd.SetWarnings False
        DoCmd.RunSQL Upd_SI_field
        DoCmd.SetWarnings True

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,867
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update the imported fields with Replace function

    Code:
    "UPDATE MM_Input SET MM_Input.SI_ICXX =(REPLACE(CSTR(MM_Input.SI_ICXX),"" "",""""))"
    See if that works.

  3. #3
    Board Regular
    Join Date
    May 2013
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update the imported fields with Replace function

    Still have an error "Type Conversion Failure", do you have another idea? Thanks

  4. #4
    Board Regular
    Join Date
    May 2013
    Posts
    667
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update the imported fields with Replace function

    Try Cstr() function?
    Office 2007
    Access novice. Sometimes trying to give something back

  5. #5
    Board Regular
    Join Date
    May 2013
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update the imported fields with Replace function

    Yes, tried it, bute failed, still conversion error

    [CODE]
    "UPDATE MM_Input SET MM_Input.SI_ICXX =(REPLACE(CSTR(MM_Input.SI_ICXX),"" "",""""))"[CODE]
    Last edited by dhen21dx; Sep 26th, 2019 at 06:56 AM.

  6. #6
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,492
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Update the imported fields with Replace function

    Provide some sample data.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #7
    Board Regular
    Join Date
    May 2013
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update the imported fields with Replace function

    Hello here is a sample data, i have MM_Input Table with Short Text fields, that data will be imported from excel, then update to remove the space between numbers.

    Item SI_ICXX SI_SLXX Category
    Non-Val 0001 000 1111 MATGS55 Paper
    Non-Val 9990 888 0000 GTSCC90 Pen
    Non-Val 0000 199 5443 NMSMM32 Plate
    Last edited by dhen21dx; Sep 26th, 2019 at 10:32 AM. Reason: Edit grammar

  8. #8
    Board Regular
    Join Date
    May 2013
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update the imported fields with Replace function

    Got the solution now.. Thank you so much guys..I've tested different approach.
    Last edited by dhen21dx; Sep 26th, 2019 at 11:08 AM.

  9. #9
    Board Regular
    Join Date
    May 2013
    Posts
    667
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update the imported fields with Replace function

    Could you at least post it, in case someone else finds they have the same problem.?
    Office 2007
    Access novice. Sometimes trying to give something back

  10. #10
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,727
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Update the imported fields with Replace function

    You probably ran into records with Null. You can't use Replace on Nulls, plus you turned off warnings thus wouldn't get the warning that would tell you how many records it couldn't update for the 4 reasons - type conversion failure and lock, validation and key violations. I prefer to use the .Execute method of the CurrentDb object along with dbFailOnError parameter for action queries. IMHO, it's better than RunSql and turning off warnings.

    What also doesn't look right in the first posted sql is the lack of a space between the Find parameter of the Replace function. It maybe should be
    "UPDATE MM_Input SET MM_Input.SI_ICXX =(REPLACE(MM_Input.SI_ICXX,"" "" """",""))"

    EDIT - Actually, more like
    sql = "UPDATE MM_Input SET MM_Input.SI_ICXX =(REPLACE(MM_Input.SI_ICXX,"" "" """"))"
    I have no idea what the green quote pairs are for in the original post.
    Last edited by Micron; Sep 26th, 2019 at 11:05 PM.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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