Update the imported fields with Replace function

[dhen21dx]

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

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

 

[dhen21dx]

Hi All,

Actually the code is valid and workable, the adjustment is in my table and file uploaded. @Micron, the green one is the string replacement of the blue one.

 

[Micron]

Hi All,
Actually the code is valid and workable, the adjustment is in my table and file uploaded. @Micron, the green one is the string replacement of the blue one.

I don't think so. The syntax is

Replace (string1, find, replacement, [start, [count, [compare]]] )

REPLACE(MM_Input.SI_ICXX,"" "" """",""))"
MM_Input.SI_ICXX is string1; the red is what to find, the blue is the replacement; the green??

Maybe I added them when playing around because I don't see them in your posts now.