If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
replace part of field with contents of another field
I need to replace a wildcard character ($) in one field
with the contents of the ID field in the same record. My database is 3000+ records, so doing this by hand is not an option - or at least not a good one. For example: My field currently reads "$_TN.jpg" and I want the result to be "12_TN.jpg" where "12" is the value in the ID field of the same record. |
#2
|
|||
|
|||
Assuming you're using Access 2002 or Access 2003, you should be able to use
the Replace function in an Update query. You'd want someting like Replace([MyField], "$", [ID]). This should also work in Access 2000, but if all the service packs haven't been applied, sometimes the Replace function doesn't work in queries. There's an easy work-around, though: write your own wrapper function that uses the Replace function: Function MyReplace(InputString As Variant, _ ChangeFrom As String, _ ChangeTo As String) As Variant MyReplace = Replace(InputString, _ ChangeFrom, ChangeTo) End Function If you're using Access 97 or earlier, you'll have to write your own Replace function. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Chip" wrote in message ... I need to replace a wildcard character ($) in one field with the contents of the ID field in the same record. My database is 3000+ records, so doing this by hand is not an option - or at least not a good one. For example: My field currently reads "$_TN.jpg" and I want the result to be "12_TN.jpg" where "12" is the value in the ID field of the same record. |
#3
|
|||
|
|||
Doug, You are WONDERFUL!! It worked perfectly. You have
no idea how much time you just saved me. THANK YOU!!! -----Original Message----- Assuming you're using Access 2002 or Access 2003, you should be able to use the Replace function in an Update query. You'd want someting like Replace([MyField], "$", [ID]). This should also work in Access 2000, but if all the service packs haven't been applied, sometimes the Replace function doesn't work in queries. There's an easy work-around, though: write your own wrapper function that uses the Replace function: Function MyReplace(InputString As Variant, _ ChangeFrom As String, _ ChangeTo As String) As Variant MyReplace = Replace(InputString, _ ChangeFrom, ChangeTo) End Function If you're using Access 97 or earlier, you'll have to write your own Replace function. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Chip" wrote in message ... I need to replace a wildcard character ($) in one field with the contents of the ID field in the same record. My database is 3000+ records, so doing this by hand is not an option - or at least not a good one. For example: My field currently reads "$_TN.jpg" and I want the result to be "12_TN.jpg" where "12" is the value in the ID field of the same record. . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Creating a parameter query using part of the field | LORIB | Running & Setting Up Queries | 1 | September 1st, 2004 09:06 PM |
Update Part# field where is blank | JUAN | Running & Setting Up Queries | 4 | August 19th, 2004 08:54 PM |
Querying Part of a Field | Brian | Running & Setting Up Queries | 2 | August 2nd, 2004 03:27 PM |