Using substitute function from Excel in Access

[Jeff Marcum]

I need to write a query in Access that returns the results of a text field
with the spaces replaced with underscores. So, if the data in the field was
'hello world', the query results would show 'hello_world'. The SUBSTITUTE
function in excel does this. I can't find an equivalent function in Access.

In case this helps the syntax in Excel is
SUBSTITUTE(text,old_text,new_text,instance_num)

Text is the text or the reference to a cell that contains the text you
want to replace.

Old_text is the existing text you want to replace. If the case of Old_text
does not match the case of text, SUBSTITUTE will not replace the text.

New_text is the text you want to replace Old_text with.

Instance_num specifies which occurrence of Old_text you want to replace.
If omitted, every instance of Old_text is replaced.

[Douglas J. Steele]

Access 2000 and newer has a Replace function that should do what you're
looking for.

REPLACE(ExistingString, " ", "_")

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Jeff Marcum" <je********@consultant.com> wrote in message
news:c8**********@mailgate2.lexis-nexis.com...

I need to write a query in Access that returns the results of a text field
with the spaces replaced with underscores. So, if the data in the field was 'hello world', the query results would show 'hello_world'. The SUBSTITUTE
function in excel does this. I can't find an equivalent function in Access.
In case this helps the syntax in Excel is
SUBSTITUTE(text,old_text,new_text,instance_num)

Text is the text or the reference to a cell that contains the text you
want to replace.

Old_text is the existing text you want to replace. If the case of Old_text does not match the case of text, SUBSTITUTE will not replace the text.

New_text is the text you want to replace Old_text with.

Instance_num specifies which occurrence of Old_text you want to replace.
If omitted, every instance of Old_text is replaced.

[Jeff Marcum]

I knew there had to be something. I don't know how I overlooked that.
Thanks for pointing it out to me.
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:bI******************@news04.bloor.is.net.cabl e.rogers.com...

Access 2000 and newer has a Replace function that should do what you're
looking for.

REPLACE(ExistingString, " ", "_")

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Jeff Marcum" <je********@consultant.com> wrote in message
news:c8**********@mailgate2.lexis-nexis.com...

I need to write a query in Access that returns the results of a text field with the spaces replaced with underscores. So, if the data in the field

was

'hello world', the query results would show 'hello_world'. The SUBSTITUTE function in excel does this. I can't find an equivalent function in

Access.

In case this helps the syntax in Excel is
SUBSTITUTE(text,old_text,new_text,instance_num)

Text is the text or the reference to a cell that contains the text you
want to replace.

Old_text is the existing text you want to replace. If the case of

Old_text

does not match the case of text, SUBSTITUTE will not replace the text.

New_text is the text you want to replace Old_text with.

Instance_num specifies which occurrence of Old_text you want to replace. If omitted, every instance of Old_text is replaced.