Query to remove spaces between numbers failing

[optionone]

Hi,

I am importing some 20 odd files each month in to an access database to process - one of the fields in these files has recently changed (I dont have control over these files unfortunately). The field is a number in the format 000 000 0000 - this used to be supplied without the spaces.

The macro that transfers the spreadsheets to the database defaults this field as text. Not all the rows have the number present so are null.

I tried using replace([number]," ","") in my to-monthly-archive append query but I get an error stating type conversion failure. I'm lost as to why this is failing - any help appreciated

Thanks,

Adam

 

[boblarson]

I deleted my other post as it wasn't correct.

This should work:

IIf(Len([number] & "")=0, Null, Replace([number], Chr(32), ""))

 

[optionone]

Thanks Bob - that works great