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

 

[Legacy 98055]

"UPDATE MM_Input SET MM_Input.SI_ICXX =(REPLACE(CSTR(MM_Input.SI_ICXX),"" "",""""))"

See if that works.

 

[dhen21dx]

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

 

[welshgasman]

Try Cstr() function?

 

[dhen21dx]

Yes, tried it, bute failed, still conversion error

"UPDATE MM_Input SET MM_Input.SI_ICXX =(REPLACE(CSTR(MM_Input.SI_ICXX),"" "",""""))"[CODE]

 

[xenou]

Provide some sample data.

 

[dhen21dx]

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.

[TABLE="width: 292"]
<tbody>[TR]
[TD]Item[/TD]
[TD]SI_ICXX[/TD]
[TD]SI_SLXX[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]Non-Val[/TD]
[TD]0001 000 1111[/TD]
[TD]MATGS55[/TD]
[TD]Paper[/TD]
[/TR]
[TR]
[TD]Non-Val[/TD]
[TD]9990 888 0000[/TD]
[TD]GTSCC90[/TD]
[TD]Pen[/TD]
[/TR]
[TR]
[TD]Non-Val[/TD]
[TD]0000 199 5443[/TD]
[TD]NMSMM32[/TD]
[TD]Plate[/TD]
[/TR]
</tbody>[/TABLE]

 

[dhen21dx]

Got the solution now.. Thank you so much guys..I've tested different approach.

 

[welshgasman]

Could you at least post it, in case someone else finds they have the same problem.?

 

[Micron]

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.