Replace wildcards with a space

I have a text field with all values containing an asterisk.  The asterisk can 
be located anywhere within the contents.  I am trying to replace the asterisk 
with a space.  I will then use TRIM to remove any spaces at the beginning of 
the text.

I have been unable to get an update query to work.  When my criteria is like 
"~[*}", Access says that 0 rows will be updated.   When the criteria is [*], 
I am asked to enter a parameter value.

Any assistance will be greatly appreciated.

Thanks - bkh
0
Utf
5/6/2010 3:51:02 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1686 Views

Similar Articles

[PageSpeed] 23

Barbara wrote:

>I have a text field with all values containing an asterisk.  The asterisk can 
>be located anywhere within the contents.  I am trying to replace the asterisk 
>with a space.  I will then use TRIM to remove any spaces at the beginning of 
>the text.
>
>I have been unable to get an update query to work.  When my criteria is like 
>"~[*}", Access says that 0 rows will be updated.   When the criteria is [*], 
>I am asked to enter a parameter value.
>

UPDATE table
SET field = Trim(Replace(field, "*", " "))
WHERE field Like "*[*]*"

-- 
Marsh
MVP [MS Access]
0
Marshall
5/6/2010 5:00:48 PM
Reply:

Similar Artilces:

Excel File Paths Contain Spaces And Wont Open
Hi I'm using MS Office 97 and opening Excel files just by double-clicking has always worked fine. A few weeks back the excel file type became associated with a very old version of Excel 4 which was located on a network drive - don't ask me how or why!!! I've re-associated back to Excel 97 but now, whenever I try to open a file contained in a path which contains spaces (e.g. C:\Program Files\My Directory\myfile.xls), Excel first tries to open each separate bit of the path (C:\Program.xls, Files\My.xls, etc) - all of which obviously fail - before successfully opeing the correct fil...

decimal spaces toolong when mail merging from excel
I recive data in an excel spreadsheet that contains in one column a number expressed as " 5.55" which is supposed to be a percentage, but when I try to mailmerge the data, it mailmerges as "5.55061561650660". In other words it carries out the decimal place more than 2 digits, which is not what I want. I have tried formatting the cell, rounding, hand typing the information in another column and deleting the column that contained the original information (which I am sure was edited from 5.55061561650660.) How can I fix this problem so that when I mail merge it comes ou...

GE SmartAire Carbon Air Cleaner Replacement Pre-Filter
Price:$8.99 Image: http://thediscounfinder.info/image.php?id=B0009GZJT2 Best deal: http://thediscounfinder.info/index.php?id=B0009GZJT2 Universal Cut-to-Fit activated carbon pre-filter helps remove large particles and reduce common household odors such as cooking odors, tobacco smoke odors, pet odors, musty odors, etc. For optimum performance replace pre-filter every 2 to 3 months. SIMILAR PRODUCTS: GE SmartAire HEPA Filter:http://thediscounfinder.info/index.php?id=B0009H791C GE Foam Pre-filter Air Cleaner Replacement Filter:http://thediscounfinder.info/index.php?id=B0009GZLAY ...

Strange Disk space problem on Exchange 2003 Server
Strange Disk space problem on Exchange 2003 Server I have Windows 2003/Exchange 2003 server with 56G D: volume where all Exchange stuff is installed. I noticed today that there are only 14GB free space and 42G in use. The Priv is only 4G, back ups have worked fine, no log files, no large Page file. In fact I can only account for 10G of data on the partition including page file, all of Exchange, and hidden and system files. Also, I looked backed over my records and the disk is filling up quickly. (3 G in last two weeks?) Anyone have any ideas what is going on here? mick2767@hotmai...

"=20" instead of spaces
Dear All, I would appreciate it if anyone could help me out here. We have over 1000 different domains that we send emails to. But there are just 3-4 domains that usually do not accept our mail & we get a Failure Notice (Undeliverable) as below ****************************** <abc@xyz.com>: 101.101.101.101 failed after I sent the message. Remote host said: 550 Error: Your email program uses "=20" instead of spaces. Please correct this (try setting your mail program to use plain text) and resend your message. ****************************** Could someone suggest how I ...

Query wildcard symbols
Just a heads-up in case this bites you, too. I migrated an application from Access 2002 to Access 2007. I have a search form for the users to enter some criteria ... the form dynamically builds a SQL WHERE clause before opening a "results" form based on an underlying query that includes all the fields available to search on. Once in Access 2007 format, the search form opens empty every time?! When I add selection criteria to the underlying query directly, the result set is empty every time?! Oh wait, I'm using the "*" (anything/everything) wildcard character. W...

"Replace With" option
Hi all I would like to amend a column with phone numbers and change only th first two characters to a "0" Most of the numbers look like this; 44131 555 8000 It should all read like this; 0131 555 8000 I could use the Ctl H feature but it would affect the number if "44 appeared in the middle or end of the number. Is this possible?? Thanks for any advice Joe -- Message posted from http://www.ExcelForum.com Hi! Try this in another column and copy down: =IF(LEFT(C2,2)="44","0"&MID(C2,3,LEN(C2)-2),C2) where your number is in C2. If you have larg...

MX/Smart Host/SMTP Address Space scenarios
By http://support.microsoft.com/default.aspx?scid=kb;en-us;294736, we understand that unlike Exchange 5.5 you don't have to create SMTP connectors unless you're interested in servicing certain conditions, such as SMTP address spaces. For the most part, we don't need anything special, we forward everything to a smart host except for about 15 domains. You can configure every SMTP virtual server to forward all its SMTP mail to a smart host. --is it correct to assume that this means for all domains except your internal, or will it force the smart host to accept everything you&...

Find and Replace #17
Hi everyone Just a quick question I cant find the answer to anywhere! How do you d a find and replace for functional symbols such as ? or * Thanks in advance, Matt Traffic Broke -- trafficbroke ----------------------------------------------------------------------- trafficbroker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2871 View this thread: http://www.excelforum.com/showthread.php?threadid=49146 Put a tilde "~" in front of the symbol. HTH Otto "trafficbroker" <trafficbroker.1zocxc_1133967012.9132@excelforum-nospam.com>...

Word Automation
I've got an Excel macro that opens up a Word document, then uses Find/Replace on the Word Document. Upon testing, it seems the code is able to find the text, but is not replacing it. I don't get any errors when the code compiles or runs. Following is an Excel macro I wrote as a test. When I copy this macro into my word document, remark out the lines that don't apply to Word (references to WordApp and WordDoc), and replace WordDoc with ThisDocument in the first line of my With statement, it runs fine. Any thoughts? Sub Test() Const X As String = "%LetterDate%&quo...

Replaced laptop hard drive, now XP keeps asking for driver
After having recently replaced a laptop hard drive (IDE) with a larger one, Windows XP, sp3 keeps asking for a driver upon reboot for the new and larger Western Digital hard drive. The machine works fine with the new drive, recognizes the entire 120 GB drive (the old one having been about 40 GB), and even Device Manager says the device is working properly. There does not seem to be any hard drive drivers on Western Digital's web site for this 2.5", 120GB, IDE drive. TIA for any help on this one ... e. I found this on another thread from a previous responder: &...

Spacing between paragraphs at bottom of page
I consider myself an advanced user with Word, but this annoyance has fooled me. How do I stop the additional spacing between paragraphs at the end of pages? This happens when I have a longer paragraph, and the document decides to keep the text altogether rather than on two separate pages. Any idea on how this can be fixed? Thanks! It's not clear to me what you're describing, but perhaps there is something in http://sbarnhill.mvps.org/WordFAQs/BottomLine.htm that will apply to your problem. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, A...

not enough memory or disk space to display or print the picture after 1st time
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC I know this topic has been covered before, but not in the way I get the problem. I can open a document with a math formula or a graphic the FIRST time and it will correctly display. Any changes to the document or any subsequent openings I then get the error message, &quot;not enough memory or disk space to display or print the picture&quot;. <br><br>eMac 1.25 Gz 10.4.6, Word 2004 <br><br>Thanks. One possible cause: You're *way* behind on Tiger -- it's been at 10.4.11 for ...

Partial replace
Hello, I'm trying to search and replace but I do not want to replace the whole cell, only a few characters in them. Namely, I have list of names, some of which have two stars ** after it and I want to get rid of those 2 stars after the name. How do I do that? Thanks a lot Sandra One way .. Assuming data in A1 down, Put in B1: =TRIM(SUBSTITUTE(A1,"**"," ")) Copy down -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Sept" <sep@sept-europe.com> wrote in message news:BEB9F138.137%sep@sept-europe.c...

removing space
Hi, I have data in a column, which precedes by one or more spaces, how do I remove these. thanks Hi one way: =TRIM(A1) Also have a look at: http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Regards Frank Kabel Frankfurt, Germany mike wrote: > Hi, I have data in a column, which precedes by one or > more spaces, how do I remove these. > > thanks Try David McRitchie's TrimALL macro, especially if the data is pasted in from an external source, like a web page: http://www.mvps.org/dmcritchie/excel/join.htm#trimall In article <b86d01c4798f$31678a90$a3012...

Server Replacement
hello I have to replace old E2kSP3 with new E2k3 I read http://support.microsoft.com/kb/307917 and http://support.microsoft.com/kb/822931 My question is regarding system public folder migration. In the articles I've mentioned only few of them are subject to migrate. But what abouth the others: EventsRoot OflineAddressBook\Ex:/o:OrgName/OU=First Administrative Group OWAScratchPad{GUID1} OWAScratchPad{GUID2} Schema Schema-root StoreEvents{GUID1} StoreEvents{Guid2} SystemConfiguration Should I be worried about them or not WBR, Alexander Please follow http://www.swinc.com/resources/ex...

SBS2008 + C:\ partition very low on disk space
Hi All, Been to see someone who had a very early SBS2008 to do some service packing etc etc as they had been having some issues. When I got there the C:\ partition which is 65GB only has 5GB free disk space left on it.. Weird thing is when you check out how much disk space the files and folders are using this only amounts to 27GB ! So where pray tell is the other 33GB consumed ??? I've turned off all the hidden files and folders etc etc to view this so I should be looking at everything as far as I can tell. Anyone any ideas ? Obviously need to sort this before I e...

EXCEL : Replace a value in a cell by a picture
How can I replace a value in a cell , by a picture . Example : Replace the value 1 with a picture of a boat and replace value2 with a picture of a train take a look here: http://www.mcgimpsey.com/excel/lookuppics.html In article <272ABD6C-EFD7-4185-BA2C-62750A61EB85@microsoft.com>, Vanderzeypen <Vanderzeypen@discussions.microsoft.com> wrote: > How can I replace a value in a cell , by a picture . > Example : > Replace the value 1 with a picture of a boat and replace value2 with a > picture of a train ...

Search / Replace Expressions Question
I am trying to replace spaces with tabs where the format is something like 12.2121 19.2141 12.2121 19.2141 and I can't just search on 5 spaces and replace with tab because there are other occurences of multiple spaces in the document so I need to pattern match. I got this far [0-9] [0-9][0-9].[0-9] but am not sure what to put in replace. I was hoping [0-9]^t[0-9][0-9].[0-9] meaning keep what was there but use tab instead which doesn't work. Pretty sure there was a way to say ok now that you found the pattern, just replace (this) Any help apprec...

code for a simple search and replace
Hi all what type of code can execute a simple "search and replace all" for a string in a document? I need some code that I can associate to a keyboard shurtcut and tha performs something like: SEARCH AND REPLACE ALL "the yellow lemon" WITH "the green lime" thanks! Ctrl+H -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com <ent@ent.ent> wrote in message news:e1j4w5vwKHA.5036@TK2MSFTN...

Replace company name on large qty of contacts
One of my customers was recently sold or had a name change. Is there a way to go change all the contacts from that company to the new company name? http://www.slipstick.com/outlook/searchreplacecompany.asp -- Russ Valentine "MarcoPolo" <MarcoPolo@discussions.microsoft.com> wrote in message news:4E6CA3DE-4546-49C3-865E-5BF822CDF362@microsoft.com... > One of my customers was recently sold or had a name change. Is there a way > to > go change all the contacts from that company to the new company name? ...

fso replacement
I use the following section of code to determine if there is a specific folder in a location and the second section to see if there is anything in the folder and how many items are there. It works great in 2003 but creates an error in 2007. I have heard that 2007 does not support fso. Is there an alternative to this code that will work? I understand enough to read the code but not enough to write it myself. fol = "\\mmpdsnas01a\apps$\Quality\Back Ends\VCAR Supporting Documents\" & Me.[VCARNum] Set fso = CreateObject("Scripting.FileSystemObject") Dim R...

Blank spaces
Hi all, I have loads of cells in a column with a blank space at the beginnin of the text in each cell. I would like to get rid of the blank spaces at the beginning of eac cell's text, but without getting rid of the blanks included in the bod of the cells' text. Anybody knows how I could do this? Thanks for helping m -- Scurlo ----------------------------------------------------------------------- Scurloc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2985 View this thread: http://www.excelforum.com/showthread.php?threadid=49553 Use the TRIM fun...

Getting cells to automatically delete spaces
Hi all I have got a very large spreadsheet where the information is presente as: 111111/ 1 Notice the two spaces after the forward slash. There's about 10'00 cells in the column with varying numbers but all in the above format. I need to get the two spaces deled so that it will appear as: 111111/1 Is there a way i can get excel to proccess this for me. Any help will be much appreciated. Thanks in advance. Darre -- Message posted from http://www.ExcelForum.com Darren, Select the required area or a whole sheet, and do a simple Replace (Edit menu), asking Excel to replace s...

Signature line double spaced
I created a signature for my e-mail and it appears in the actual e-mail as double spaced. I have made sure all settings I can find are set on single spacing with no before or after and it still double spaces. Any ideas =?Utf-8?B?SmVycnkgRHVubGFw?= <anonymous@discussions.microsoft.com> wrote in news:D945B2A5-3A03-4C2E-A8A0-FB5CF177E8E3@microsoft.com: > I created a signature for my e-mail and it appears in the actual > e-mail as double spaced. I have made sure all settings I can find are > set on single spacing with no before or after and it still double > spaces. Any id...