473,231 Members | 1,746 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

Using substitute function from Excel in Access

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
2 16475
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Mike Knight | last post by:
(I've also posted this problem on microsoft.public.excel.programming) I have a MS Access 2003 Database named "AS400 Fields.mdb". This database contains links to tables on an AS400. In MS...
0
by: Jack | last post by:
Windows 2K Pro Access/Excel 2003 Hi there, I have a large number of password protected Excel Workbooks. The files are protected by code that runs when the workbook opens. The code asks the...
1
by: garry.oxnard | last post by:
Can anyone help me to solve a problem which involves switching from Access to Excel (then back to Access) programatically please? I have an Excel template which, on open, also opens an Access...
5
by: billelev | last post by:
Hi there. I need to perform a number of financial calculations within a database I am creating. Rather than writing my own functions, I figured it would be worthwhile to use the functions that...
1
by: baling | last post by:
Hi.... Hi everybody, i have a code that i make in VBA and know I want to use this code in to VB6. But i don't know how to use that code in to VB 6.0 Please correct this code so i can use it in VB...
1
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am...
5
by: maryanncanor | last post by:
Hi, I have an input excel file that needs to be imported to Table1. However, the fieldnames or header in the excel file doesnt match the fieldnames in the database. The fieldnames in the excel file...
1
by: JFKJr | last post by:
Hello everyone, the following Access VBA code opens an excel file and creates textboxes in a given range of cells dynamically. The code attaches "MouseUP" and "Exit" events to the textboxes (using...
3
by: keirnus | last post by:
Hello once again... I made a function in Excel. The function does some error checking within the Excel file. To be easy for me, I want my code in MS Access to simply call the function in Excel....
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.