Update the imported fields with Replace function

dhen21dx

Board Regular
Joined
May 10, 2013
Messages
166
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Code:
"UPDATE MM_Input SET MM_Input.SI_ICXX =(REPLACE(CSTR(MM_Input.SI_ICXX),"" "",""""))"

See if that works.
 
Upvote 0
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:
Upvote 0
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.

ItemSI_ICXXSI_SLXXCategory
Non-Val0001 000 1111MATGS55Paper
Non-Val9990 888 0000GTSCC90Pen
Non-Val0000 199 5443NMSMM32Plate

<tbody>
</tbody>
 
Last edited:
Upvote 0
Got the solution now.. Thank you so much guys..I've tested different approach.
 
Last edited:
Upvote 0
Could you at least post it, in case someone else finds they have the same problem.?
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top