By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,797 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

Using substitute function from Excel in Access

P: n/a
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.


Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.

Nov 12 '05 #2

P: n/a
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.


Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.