changing field w/ action query

[Russ]

I've got a table with a field that holds path information for a file.
I've relocated these files and the current path no longer exists. Can
I use an update query or another method to change the contents of
these records in that field?

For example:
Currently the location field contains I:\My Documents\Access\photos
I want it to read D:\My Documents\Access\Photos

Russ

 

[Eric D via AccessMonster.com]

Yes, you can update the records with an update query.
Create a new query, select the table you want to update.
You can select the one field, if it's a mass update or others if you want to
change the field based on meeting certain criteria. I usually create a Select
query first, once my query selects the records I'm concerned with and I know
my statement is correct, I change the query to an update query.
Click on Query/Update Query to change to an update query. You will see a new
option below each field you selected in your query - Update To:. In the field
you want to change, simply enter "D:\My Documents\Access\Photos" in this
Update To: field and then run. This will update all records to point to your
new location.

 

[Russ]

Eric,

Sorry for the misleading message I sent earlier, but the path also
includes a filename at the end. Of course these filenames are all
different, so I need to maintain that part and change the path to it.

Russ

 

[Eric D via AccessMonster.com]

You need to review IIF, MID and Length functions.
If the first character of [yourlocationfield] = "I" then [yourlocationfield]
= "D" plus [yourlocationfield] starting at position 2.

I'm not trying to be a jerk here as I could give you the exact syntax but you
should give it a try first. This will help you in the future.

If you can't figure it out, let me know and I'll send the exact syntax.

Eric,

Sorry for the misleading message I sent earlier, but the path also
includes a filename at the end. Of course these filenames are all
different, so I need to maintain that part and change the path to it.

Russ

Yes, you can update the records with an update query.
Create a new query, select the table you want to update.

[quoted text clipped - 18 lines]

 

[Russ]

I'll give it a look. I did change the data using datasheet view and
find/replace, but I thought this would be a better way for any changes
in the future when these folks are on their own.

Russ

You need to review IIF, MID and Length functions.
If the first character of [yourlocationfield] = "I" then [yourlocationfield]
= "D" plus [yourlocationfield] starting at position 2.

I'm not trying to be a jerk here as I could give you the exact syntax but you
should give it a try first. This will help you in the future.

If you can't figure it out, let me know and I'll send the exact syntax.

Eric,

Sorry for the misleading message I sent earlier, but the path also
includes a filename at the end. Of course these filenames are all
different, so I need to maintain that part and change the path to it.

Russ

Yes, you can update the records with an update query.
Create a new query, select the table you want to update.

[quoted text clipped - 18 lines]

Russ