Related Topics

Replace() Function And Wildcards

Replace hash (#) with query
I have been having this problem with trying to replace hashes from a field for a little while. I know that they are wildcards for number in queries, but still, the usual brackets approach doesn't seem to help when within a replace function.

The two main failed attempts have been run an update query in which the field is updated to the following:

a) Replace([Age],Chr(35),"")
b) Replace([Age],"[#]","")

Neither one of which has replaced any hashes.

Repeat Replace Function
I have small requirement with Replace function, as per my understanding Replace function is used to replace a text in a filed.

My requirement is if one filed contians more than 10 words to replace at once, how to repeat the sam function for that filed to give output in another filed.

Field1 Exp1: NewName
A, B,C,D,E,F Apple,Bat,Cat,Dog,Eat,Frog.

Use the Find and Replace dialog box to change data
This topic explains how to use the Find and Replace dialog box to find and optionally replace data in an Access database.
* Learn about using the Find and Replace dialog box
* Find and replace data in a table
* Find and replace data in a form
* Find data in a query result set
* Find wildcard characters
* Examples of wildcards in use
* Find quotation marks and null or blank values
* Find and Replace dialog box control reference

Using other records in a Replace function
Having a bit of difficulty tonight and could do with some pointers. My problem is surrounding the use of a table of "illegal characters" data to use within the Replace() function within a query.

I'm currently working with Replace(Replace(Replace(.etc and as I am going to need to use this on multiple queries, I don't want to have to edit each one manuallyevery time I need to add a new 'illegal character'.

I have created a table (tblBannedChars) containing the following fields: BannedChar & ReplaceChar; the general idea being that I could use Replace([string],[BannedChar],[ReplaceChar])and the outcome would be that it had repeated the Replace function for each record in the tblBannedChars table.

Find and Replace in Access 2007
I have address columns:

705 First St #5 |
804 Main St #16 |

I just want to remove the pound (#) signs. How do find/replace for just the #?

I tried the Replace Function but it deletes the entire cell. I also tried the RemoveAlpha but I get an error saying its an undefined function.

Replace Quotation Marks
So I am trying to use a query to replace quotation marks in EVERY column in a table. Now, I have two problems here.

1.) I think I am looking at replacing each column individually. For example, for a table with 4 columns, I am looking at.
Replace([Table]![Column1], etcetc), then
Replace([Table]![Column2], etcetc), then
Replace([Table]![Column3], etcetc), then
Replace([Table]![Column4], etcetc)

This is NOT a big deal to me (Problem 2 is the bigger problem!

2.) As everyone knows the Replace functions works as such so that:
Replace("alphabet", "bet", "hydro") would return "alphahydro". You are supposed to surround the 2nd function (find) and third function (replacement) with quotation marks.

However, I am trying to replace the quotation marks in the table! And in the 2nd function (find), I am surrounding the quotation mark symbol with quotation marks (so it looks like """) and it is not working!

About find/replace
I successfully used find/replace to get rid of extraneous numbers in one of my columns (alphanumeric). The results look like I123450. However all of the resulting numbers still have a 0 as the last digit and I need to get rid of them.

Does anyone know of an easy way to do this? You'd think that wildcards would work but I can't figure it out

Proper syntax for replace()
When using the replace() function, is it possible to refer to a variables in the find and replace parameters?

Replace(«string», «find», «replace»)

I can use a formula like this, where the replacement text is a table field. Replace([textin],"Find this text string",[cname])

What I want to do is get the text to find, and the variable NAME to use for replacement from a table like this:

txtfind | replacefieldname

"" [customername]
"" [shipaddress]
"" [cnumber]

Imagine a query that would replace a series of placeholders, set up as some text between brackets, with the data from a query or table that contains the three fields shown. It's like a mailmerge function.

I can't figure out the proper syntax that would refer to the txtfind and replacefieldname variables in the replace() function.

This is part of a larger project.

replace a character with a wildcard
I just split several strings into arrays, and now I need to find every "x" in the arrays and replace them with wildcards. How do I go about doing this?
Also, there isn't an "x" in every array, so it would be cool if I didn't get an error message when it can't find an "x

Replace Function
The Replace function replaces a sequence of characters in a string with another set of characters (a number of times).

Replace Function
Here's what I want to accomplish, preferably via VB in Access.

Select Query: CheckTitleLength
Fieldname that contains text that I want to replace: Titleshort

I need to replace the following text in the Titleshort field:

Vice President with VP
Executive with Exec

I can place the records of the query into a table if that will

Multiple Replace in the same field to replace different Names
I have a Table and in that table I have a field,named "Title_1", which contains names of different cities.I have duplicated that field and named it "Title_2". What I want to do is to raplace many of the names of the cities because from the source I got the data the names of the cities were written in an "older version" of the greek language and that creates confusion to the user when he wants to find a city.
Now I know that I can create an Update query and use the Replace function in the citeria to replace the name. The thing is that this way I'll have to create a different quey for every city name I want to replace. I know that I'll have make a new Replace for every city name but I'd like to know if it can be done in just 1 query so at least I'll save some time that way.
I know all the city names that I want to replace so I know what will be used for every replace.

Of course I'm open to any different ideas about how to do this task,if someone has any

Find and Replace on a huge amount of records
I am trying to find and replace certain characters that are in every field of table.

The catch is: The table has 600K records in it.
So when I use CTRL H (Find and Replace), it works great.but only for the first 10000 records or so. It won't do any after that.

Then I found a module:

Public Function fReplace(ByVal Expression As String, ByVal Find As String, ByVal Replace As String) As String
Dim strTmp As String, n As Integer

Replace function inserting Apostrophe (')
I am having a difficult time with some VBA code running a replace function. Specifically, I'm trying to update a field that contains gender information. The information is contained in my database as follows: "Womens". HOWEVER, I need to run a VBA function to replace all instances of "Womens" with "Women's".

The problem arises with how you must use quotation marks (") and apostrophes(') in VBA to run queries. Any ideas how I can get this to work?

here is what I currently have:

DoCmd.RunSQL "UPDATE Table1 SET Table1 .Field10 = Replace([Table1 ].[Field10 ],'Womens','Women's')"

This obviously doesn't work because of the mismatch of apostrophes and quotation marks

Replace Function with Table Lookup
I have several records that contain very similar substrings, such as "billable edits" and "edits- billable" that I am trying to standardize (make all "edits-billable" become "billable edits") for simplicity's sake. The challenge is that these substrings occur at the end of lengthy strings. I created a table with two fields, the current substrings and the substrings that I would like to replace them with. How can I connect this table, using a replace function, the edit the original strings? Or is there an alternate way to do this

replace entire case only
I'm using

mystring = replace(mystring, aValue, "")

to replace duplicates in a string used to build an sql string.

I.e. I could have.

1,2,201,55,202,3,40,55, 555, 22, 222, 22

so if I use the replace on the 22 it SHOULD only replace the other 22. whats happening is its replacing the 222 as well. the same will happen for the 555.

can I use this to replace only if its a full match, I.e. the whole string?

Trying to get us onto accdb from mdb and of course have to resolve the user login.

Can I take ownership of the above function?
I understand this is a function Access "owns" or am I

Nearly all of our problems would be resolved if I can get into this function and have it look to the new login Form I will create to get the CurrentUser() rather then the mdw file I assume it currently looks for.

If I can't do this then I must seach for all references to this function and replace same with a new function I create say, =TeamMemberLogged

replace characters in a string
Below is the query I am trying to run and error it is given below.
SELECT '<a href='+A.[Value]+'>'+Replace(A.[Value],"#","?")+'</a>' as total Value from Temp1

Undefined function 'Replace' in expression.

Let me know is there any other way I can replace the Characters in the query

Applying a find/replace function
to all,
I import a monthly financial statement from txt into a table and designate fields based on a specification called in a form. One field is named "Amount".

The "Amount" field contains charges that are 8 characters long and have either a bracket or letter as the last character. An example is "0000376{" or "0000178K".

I have a conversion table to designate what the last character stands for and I would like to replace the amount character with the number assigned. An example is ("{" = 0) to "00003762" or ("K" = -2) to "00001782". Based upon the conversion table, the amount charges represent either debits (+) or credits (-). I also included a sample of the data in my field.

How can I write the replace function to run through the Amount field and replace the last character on each charge? And how can I call it on my form?

WildCards in VB Code
Are there wildcards in VB? For example,

DoCmd.DeleteObject acQuery, [Zip]

But the program name that I am referencing is on the form, and I want to delete any query that is associated with that zip. The query will definitely have the zip in the name, but it may begin with query_, qry_, Q_, zip_Query, etc.

Is there a wildcard that I could use to isolate the zip from my form, regardless of what comes before or after it on the query?