=substitute (in Excel) equivalent for Access

[respree]

Hello all:

I was wondering if there is the equivalent of Excel's =substitute formula for Access. I am trying to write a query that will look at a field within a table and when it finds a space in any of the records, it replaces it with a %20.

Am new to Access and it appears many of Excel's formulas carry over to Access, but the =substitute doesn't. Is it called something else.

Any help would be appreciated.

Thanks in advance.

 

[Cortici]

Try the replace function:

Replace(expression, find, replace[, start[, count[, compare]]])

Check VBA help - 'replace' for more.

HTH,

 

[respree]

Thank you for your response.

I must be doing something wrong. Could you tell me what is wrong with the formula below?

Stringname: Replace([Artist Names]!Fullname,"","%20",1,1,0)

 

[Cortici]

Try:

Stringname: Replace([Fullname]," ","%20")

I assume this is an expression in a query.

If this doesn't work, please tell me what kind of error you are getting.

HTH,

 

[respree]

Sorry, I made a slight adjustment to it and it does work.

Before:
Stringname: Replace([Artist Names]!Fullname,"","%20",1,1,0)

After:
Stringname: Replace([Artist Names]!Fullname,"","%20",1,10,0)

What was happening was that it found only the first space, but ignored the other spaces. The 'after' formula seems to fix this.

Many thanks again for your help. =)