TRIM not removing leading spaces in Query expression

[garygoodguy]

Hi, I have imported an excel sheet into an Access table. The excel doc came from a DSS system so there are leading spaces in the first text column. In excel, to remove the spaces the only formula that works is: =TRIM(TRIM(SUBSTITUTE(A5,CHAR(160),""))).

Back in Access now, and in a query I've tried TRIM and LTRIM to remove leading spaces but this does not work. I have a feeling it could be REPLACE, but does anyone know how I can replicate the excel formula in Access to remove the leading spaces in my query expression?



Thanks in advance.

[garygoodguy]

Hi, sorry I figured it out. Didn't realise I had to replace 'Substitute' with 'Replace' and 'CHAR' with 'CHR'.
The rest of the formula is the same and works.

Thanks anyway.