Remove Spaces

[Graham Clements]

Help.

I need to remove spaces from certain text fields, I
have tried using the 'trim' function but it doesn't seem
to do anything (or do what I want). For example:

Field: Telephone Numbers
Have: 01354 839 394
Need: 01354839394

I am currently achieving this by using a find & replace
method but it is messy and time-consuming if there is a
lot of data. How can I achieve this automatically, like
within a query???

Thanks for any help.

Graham

 

[Graham Clements]

Thanks Arvi but what is the syntax for this function???
I have tried this in a query:

Expr1: Substitute([MyField]," ","")

but it says:

Undefined function 'Substitute' in expression.

 

[Arvi Laanemets]

Hi

Sorry, I dind't read carefully enough. Substitute() is a worksheet function,
i.e. you can use it p.e. in calculated field. The syntax is
SUBSTITUTE(text,old_text,new_text) for your case, but I'm not sure as it
does work for query (but you can try).

A similar VBA function is Replace() with syntax Replace(expression, find,
replace) for you case. Try it too!


Arvi Laanemets

Thanks Arvi but what is the syntax for this function???
I have tried this in a query:

Expr1: Substitute([MyField]," ","")

but it says:

Undefined function 'Substitute' in expression.

-----Original Message-----
Hi

Use a Substitute() instead
(replace " " with "")


Arvi Laanemets





.

 

[Dan Artuso]

Hi,
I've never heard of Substitute, try Replace (only A2K or later) instead.

--
HTH
Dan Artuso, Access MVP

Thanks Arvi but what is the syntax for this function???
I have tried this in a query:

Expr1: Substitute([MyField]," ","")

but it says:

Undefined function 'Substitute' in expression.

-----Original Message-----
Hi

Use a Substitute() instead
(replace " " with "")


Arvi Laanemets





.