Query to remove spaces between numbers failing

optionone

Registered User.
Local time
Today, 00:34
Joined
Feb 11, 2010
Messages
56
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
 
I deleted my other post as it wasn't correct.

This should work:

IIf(Len([number] & "")=0, Null, Replace([number], Chr(32), ""))
 
Thanks Bob - that works great
 

Users who are viewing this thread

Back
Top Bottom