Globally change part of a field

I have a text field that holds the file location of a picture.  The content 
looks like this:

H:\Access Data\Personnel Pictures\picturefile.bmp

The "picturefile.bmp" is different for every record.  Everything preceding 
it is identical in all records.

I've moved the folder with all the pictures to a new location and need to 
update this field to the following:

S:\Everyone\Member Database\Personnel Pictures\picturefile.bmp

So basically, I need to swap out "H:\Access Data" for "S:\Everyone\Member 
Database" in every record.

How so?

Thanks in advance.

Jerry
0
Utf
1/31/2008 12:14:01 AM
access.queries 6343 articles. 1 followers. Follow

4 Replies
659 Views

Similar Articles

[PageSpeed] 16

Use the Replace function in an update query.
     Replace([YourField], "H:\Access Data", "S:\Everyone\Member Database")

-- 
KARL DEWEY
Build a little - Test a little


"JWCrosby" wrote:

> I have a text field that holds the file location of a picture.  The content 
> looks like this:
> 
> H:\Access Data\Personnel Pictures\picturefile.bmp
> 
> The "picturefile.bmp" is different for every record.  Everything preceding 
> it is identical in all records.
> 
> I've moved the folder with all the pictures to a new location and need to 
> update this field to the following:
> 
> S:\Everyone\Member Database\Personnel Pictures\picturefile.bmp
> 
> So basically, I need to swap out "H:\Access Data" for "S:\Everyone\Member 
> Database" in every record.
> 
> How so?
> 
> Thanks in advance.
> 
> Jerry
0
Utf
1/31/2008 12:26:01 AM
Never mind.  figured it out.

Jerry

"JWCrosby" wrote:

> I have a text field that holds the file location of a picture.  The content 
> looks like this:
> 
> H:\Access Data\Personnel Pictures\picturefile.bmp
> 
> The "picturefile.bmp" is different for every record.  Everything preceding 
> it is identical in all records.
> 
> I've moved the folder with all the pictures to a new location and need to 
> update this field to the following:
> 
> S:\Everyone\Member Database\Personnel Pictures\picturefile.bmp
> 
> So basically, I need to swap out "H:\Access Data" for "S:\Everyone\Member 
> Database" in every record.
> 
> How so?
> 
> Thanks in advance.
> 
> Jerry
0
Utf
1/31/2008 12:26:01 AM
P.S.  -- Backup first!
-- 
KARL DEWEY
Build a little - Test a little


"JWCrosby" wrote:

> I have a text field that holds the file location of a picture.  The content 
> looks like this:
> 
> H:\Access Data\Personnel Pictures\picturefile.bmp
> 
> The "picturefile.bmp" is different for every record.  Everything preceding 
> it is identical in all records.
> 
> I've moved the folder with all the pictures to a new location and need to 
> update this field to the following:
> 
> S:\Everyone\Member Database\Personnel Pictures\picturefile.bmp
> 
> So basically, I need to swap out "H:\Access Data" for "S:\Everyone\Member 
> Database" in every record.
> 
> How so?
> 
> Thanks in advance.
> 
> Jerry
0
Utf
1/31/2008 12:27:02 AM
On Wed, 30 Jan 2008 16:14:01 -0800, JWCrosby
<JWCrosby@discussions.microsoft.com> wrote:

>I have a text field that holds the file location of a picture.  The content 
>looks like this:
>
>H:\Access Data\Personnel Pictures\picturefile.bmp
>
>The "picturefile.bmp" is different for every record.  Everything preceding 
>it is identical in all records.
>
>I've moved the folder with all the pictures to a new location and need to 
>update this field to the following:
>
>S:\Everyone\Member Database\Personnel Pictures\picturefile.bmp
>
>So basically, I need to swap out "H:\Access Data" for "S:\Everyone\Member 
>Database" in every record.
>
>How so?
>
>Thanks in advance.
>
>Jerry

Update the field to

Replace([fieldname], "H:\Access Data", "S:\Everyone\Member Database")

This is a good example of why it's usually preferable to store the filename
and the path in different fields - they may indeed change independently of one
another.


             John W. Vinson [MVP]
0
John
1/31/2008 12:42:28 AM
Reply:

Similar Artilces:

How do I change excell doc to all caps?
How do I change my excell doc to all upper case. Try the function UPPER, it changes all text to uppercase letter. princechopper Wrote: > How do I change my excell doc to all upper case. -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7094 View this thread: http://www.excelforum.com/showthread.php?threadid=387354 have a look at the caseconvert add-in available from http://www.cpearson.com/excel/download.htm "Morrigan" wrote: > > Try the fun...

changing program icon
How do I change the icon of an MFC program I am creating? Sorry if this is a dumb question. Click the resource tab and right-click on the icon that you want to change. Click properties. In the file name text box enter the file name of the icon that you wish to change to. anonymous@coolgroups.com wrote in message news:<b83f655567261121cc38cd9cedc48112@news.scbiz.com>... > How do I change the icon of an MFC program I am creating? > > Sorry if this is a dumb question. It's in the resources, under "Icon". My projects all have them listed as "IDR_MAINFRAME&qu...

can i able to change the voting caption "VOTE" to custom caption?
can i able to change the voting caption "VOTE" to custom caption? I don't think so. What happened when you tried? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft.com/KB/555375 After furious head scratching, Balan asked: | can i able to change the voting caption "VOTE" to custom caption? ...

Change item numbers
Hello, our company is in the process of acquiring other companies. We had items numbers that were specific to just our organization. We now want to change our item numbers so they are more generic and will work with any company we acquire in the future. We are on GP10. All our items are service items not actual inventory items but they are all listed in the Item Master and we use SOP transactions. We'd prefer not to discontinue an item and create new items. I'd like to get opinions as to updating the sql tables. I would think we'd have to update Item Master, SOP10200,...

Inconsistency between global catalog in root and global catalog in child domains
Hi, I have a very strange problem in my AD. It seems that there is an inconsistence between global catalog in the root domain and global catalog in the child domains. Global catalog on all root domain controllers are identical. Global catalog on all child domain controllers are identical. However (at least) one orphaned object exist in the global catalog on the child DC's, but not in global catalog on the root DC's. The object is not accessible through adsiedit, ldp, etc. and thus we cannot delete it. Does anybody have a good idea on how to get this object deleted. How the inco...

Powerpoint 2007 Excel import change tab
Hi, The situation is as follows: Powerpoint template needs to import data from specific fields from within a tab in excel. This tab name changes depending on the client. Using the "paste Special" i can get the info imported fine. The part i have a problem with is changing the name of the tab to import different data.Powerpoint doesn't have an option under "Prepare>Edit Links To file" If anyone can suggest a solution even a macro to prompt the user to enter the name of the new sheet they want to pull the data from. The Execl file does not change ...

Change workbook
I have a populated chart in one workbook. I want to make the same chart in another workbook (with different data but with same chart settings eg. line style). Is it possible to somehow copy the chart from one workbook to another and then just change the data series? I tried that but my chart now has ugly grey lines all over it (dont know what they mean but they are diagonal). Please need help! Thanks alot! You may have better results if you copy the entire sheet, including both chart and data, then replace the copied data with the data from the other sheet you want to plot. - Jon ----...

email link in signature font changes, why?
Hi folks, I have an email link in my Outlook 2003 signature created in frontpage. In frontpage the link is arial black, but when I send email it arrives as Times new roman black...any idea why? Thanks, Danny ...

"how to change the administrator password"
Dear expert, how to change the administrator password in MS exchange server 2003, would you please send me the path of an article show me how to do this ?? thankx There is no such thing as an Exchange administrator password. There's the normal administrator account, but Exchange does not use that. There is no longer a service account as there was in Exchange 5.5, since Exchange 200x uses LocalSystem for service startup. -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchangeblog.com "Akkad" <amr.akkad@gbrands.com> wrote in mess...

Copying SumIF formulas without changing the Sum Range
I am having trouble copying a SumIf formula without the Sum Range changing. Does anyone know how to do this? "Deborah" <galyong@sscserv.com> wrote in message news:0e3b01c38e66$27b27100$a101280a@phx.gbl... > I am having trouble copying a SumIf formula without the > Sum Range changing. Does anyone know how to do this? Change the sum range references to absolute, such as A1 to $A$1. ...

Query not recognizing blank fields
I have a table with a date field that have some registries with #Num! When I create a simple query all registries listed in the table as #Num! are listed as "" (blank) on my query. Now I need to to check if this field is "" then bla bla... I'm using the following: Status: IIf([Latest Report]="";"ERROR";"OK") The result is always the ELSE option. So, here is OK.... What I'm doing wrongly? Rodrigo Borges "Rodrigo Borges" <rsborges@hotmail.com> wrote in message news:9E0D5BA4-D2B6-429B-8D5C-E9C52F2C8BF3@microsoft...

Changing WordArt defaults
This problem is apparent to me when I use Publisher (2003 and 2007) but I suspect it is a generic problem with Word Art across the Office range. I use WordArt alot and I always use the same font (monotype Corsiva) but everytime I invoke Wordart it defaults to Times New Roman. Is there anywhere in the system that I can set a default value to my preferred font so that I don't have to search everytime I invoke Wordart? (I currently use both XP and Vista - Office 2003 and 2007) It depends on the style on what font is the default. In Publisher 2007, create a WordArt. Right-click, Add to cont...

Changing default Page Size
I tried to print a report from CRM 3.0 but reliased that all reports are defaulted to 'Letter' page size. When I looked at the printer default size it is set at A4 but in the report the printing preference goes to 'Letter'. I also looked at the rdl file and it is in A4. Does anyone know how to change the page size to A4? Thanks -- dwoo ...

Catching global keystrokes
Hello. I need my program to react to some key combination no matter if the program's window is active or not. How can this be done? Depends on the details of the key combinations as to what you have to do, precisely, but a WH_GETMESSAGE hook looking for WM_KEYDOWN, WM_SYSKEYDOWN or WM_CHAR is a start. Note if the key combination is Ctrl+Alt+Del, then it will never be possible. In Vista, you will not be able to get ALL keystrokes unless you are running as administrator. See my essay on hooks on my MVP Tips site. joe On Fri, 07 Nov 2008 09:42:52 +0300, Vladimir <thamiorsinister@...

How do I change the default paper size
How do you change the default paper size in publisher 2003 to A4 so it stays after the program is closed and then reopened again. In england, everyone uses A4 paper, so it is annoying to have to set the paper size to A4 every time I start publisher. Try this: Start Settings Printer Select your printer, Right click, Properties, Printer Preferences, select your paper size. This is using Windows XP. -- Don Vancouver, USA "Tommylommykins" <Tommylommykins@discussions.microsoft.com> wrote in message news:2501279A-4D4F-4AF5-96FF-F0E68DCC482C@microsoft.com... > How do yo...

No global address list when trying to send email through excel
In excel, you can send the spreadsheet as an attachment through emai (Outlook). When I try to do this, the global address list on th server doesn't show up, therefore the person I'm trying to send it t doesn't show up. The global address list is available because I ca save the spreadsheet and create a new email and insert it as a attachment...I would like to send the spreadsheet in an email throug excel (File, Send to, mail recipient (as attachment) because it' easier...How do I change the settings to look for the GAL on th server. There was some sort of wizard that walked ...

Changing the Exchange Alias
Hello, I'm trying to figure out how to change a user's Exchange Alias. I've changed the username, exchange attributes, run the Recipient Update Service rebuild and update. When I address a message to the user, I right-click on the user's full name (in the To: field) and select properties. The Alias is still the old username. Any thoughts? Thanks! Jeff Did you change the alias name in ADUC? Jeff Brooks-Manas wrote: > Hello, > > I'm trying to figure out how to change a user's Exchange Alias. I've > changed the username, exchange attributes, run the...

If check box changes
Access 2003 On my subform I have 3 checkboxes format Yes\No BBAll - control source BBAll default Yes BB1 - control source BB1 default No BB2 - control source BB2 default No and 2 textboxes TextBB1 - control source =DLookUp("[BB1]","[t000Facts]","[UnitID] =" & [UnitNo]) TextBB2 - control source =DLookUp("[BB2]","[t000Facts]","[UnitID] =" & [UnitNo]) If TextBB1 is null then BB1.enabled=false If TextBB2 is null then BB2.enabled=false this means if TextBB1 is empty then the BB1 checkbox selection is disabled(s...

Open and close an account record creates "changes"?
When I open and then close an Account record (in Web Client and Outlook Client of CRM) without changing anything, it still gives me the error "Are you sure you want to navigate from this page? Your changes have not been saved ... etc.." What can be the reason for this? We might have some workflows and/or coding on the Acount entity, but nothing unsupported or anything complicated..? Hi, Most probably you have some JScript on the form which is recalculating some field. If some field have value "100" and using some JScript, you remove that value and then reenter same v...

modifying field values in an Access 2003 query
Someone please help. Is there a SQL or Visual Basic statement that can be used to set/change the values in a field in an Access 2003 query to a default value? I'm more familiar with the concept of a table's field (or a control on a form) having a "default" value. How is it that you expect a query's field to have a default value? What are the circumstances? If, for example, you want to display something like "n/a" when an underlying field in a table contains a Null, you could use something like: YourQueryField: IIF(IsNull([YourUnderlyingField]),...

global rule
I have a program that tags the subject line of all emails that it thinks is spam. Instead of delivering that message to the recipient I would like to create a rule to deliver it to one large spam mailbox. Is there a way to do this? I am running exchange 2003. ...

Check for duplicates with 2 fields
Hi, I have a form that gets its data from a table with 3 fields: Key (auto number), Item (text), and Detail (text). When I add a new record I want to test for a duplicate records based on both the Item and Detail fields. If there is a duplicate record I want to display my own error message and undo the entry. How is the best way to do this? Also, the Detail field can be a blank. Thanks, -- Phil One way is to add a unique index on those fields & use Form Error to trap & replace the default error msg (probably the best) Or you can use the Forms Before Update even Private Su...

Ownership change
Anyone have experience with being acquired, but stayed on the current systems of MS Dynamics GP? We were just acquired by a new owner. Our companies and operations will stay the same, except we have a new holding company for the new parent. If we are going to stay on MS Dynamics GP, what would be the best way to transition to the new ownership and have a clean cutoff of the old ownership vs new ownership? We were considering two options - 1. start new company db's for each company acquired and copy over our old db to the new db, but remove all the historical tran...

Global Footer Options?
Hi there: Does anyone know if there is a global default option in Excel to ALWAY create footers with the pathname and docname for ALL worksheets? I.E. know there is the solution to create the pathname in eac footer/worksheet, but I want to set this feature as a global defaul for any worksheet I work on. Hope springs eternal : -- Message posted from http://www.ExcelForum.com Hi you can create a new template (*.xlt) file including this option. Place this in your template folder and choose this template for your new workbooks -- Regards Frank Kabel Frankfurt, Germany > Hi there: > D...

area code changed
All around us the area codes are changing. How can I universally find and replace the area codes in my contacts? thanx ...