Update the imported fields with Replace function

dhen21dx

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

Tom Schreiner

Well-known Member
Joined
Mar 18, 2002
Messages
6,867
Code:
"UPDATE MM_Input SET MM_Input.SI_ICXX =(REPLACE(CSTR(MM_Input.SI_ICXX),"" "",""""))"
See if that works.
 

dhen21dx

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

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,587
Office Version
2013
Platform
Windows
Provide some sample data.
 

dhen21dx

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

dhen21dx

Board Regular
Joined
May 10, 2013
Messages
121
Got the solution now.. Thank you so much guys..I've tested different approach.
 
Last edited:

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
707
Could you at least post it, in case someone else finds they have the same problem.?
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,782
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:

Forum statistics

Threads
1,086,097
Messages
5,387,812
Members
402,081
Latest member
PiotrX

Some videos you may like

This Week's Hot Topics

Top