Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    9

    Unanswered: append path into entire column

    Hi all,

    I have a database with 1,900 records. One of the columns is an image name. I need to paste a path at the beginning of these image names, so when I import that database to the client's website, the images will show up on the page.

    Is there a way to set up some sort of script that will take name.jpg and change it to path/path/path/name.jpg, for an entire column?

    Also once that is done, I will have to copy that column to a thumbnails column, and change all the file names from name.jpg to name-th.jpg, is there an easy way to do this?

    Thanks everyone

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    you need a update query for that operation. something like:
    Code:
    UPDATE ImageTable SET [ImageName] = "path/path/path/"+[ImageName];
    but take a backup of your database before doing this. I didn't try it.
    ghozy.

  3. #3
    Join Date
    Apr 2004
    Posts
    9
    cool thanks! I'll give it a shot now

    edit: grrr it doesn't seem to be working... here's what I've got

    Code:
    "UPDATE Products SET [ImageName]"=""graphics/00000001/"+[ImageName]
    I had to take out the semicolon at the end cause Access gave me hell about it.

    When I run it all I get is a blank table cell named ImageName

    I did this in the Query editor... maybe there's another way to do it? I'm very unfamiliar with this stuff in Access..

    Also when I save it and view it again, Access seems to change it around, I attached a screenshot below..

    can you let me know where I'm going wrong?
    Attached Thumbnails Attached Thumbnails access.jpg  
    Last edited by Mike521; 08-05-04 at 13:44.

  4. #4
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    let me tell you step by stepL
    1. open query editor
    2. add your table
    3. add imagename field to query
    4. from toolbar, change query type to update query.
    5. type "graphics/00000001/"+[ImageName] into update to section under imagename field.
    6. Run (!) Query.

    thats should work
    ghozy.

  5. #5
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    or:
    1. open query editor
    2. change query view to SQL View.(top left button on the toolbar)
    3. paste this code into SQL view.
    Code:
    UPDATE Products SET [ImageName] = "graphics/00000001/"+[ImageName];
    5. Switch back to Design View
    6. Run Query.
    ghozy.

  6. #6
    Join Date
    May 2004
    Posts
    159
    OR
    1: Open table
    2: Select collumn
    3: Do ctl H (same as find/replace)
    4 Enter image name in find field and enter image name with path in replace field
    5 Close table

  7. #7
    Join Date
    Apr 2004
    Posts
    9
    Quote Originally Posted by ghozy
    let me tell you step by stepL
    1. open query editor
    2. add your table
    3. add imagename field to query
    4. from toolbar, change query type to update query.
    5. type "graphics/00000001/"+[ImageName] into update to section under imagename field.
    6. Run (!) Query.

    thats should work
    that worked perfectly ghozy, thanks a ton!!!

    can you help me with the next portion of this problem --

    I used a similar tactic to copy everything from imagename over to imagethumbnail.

    However the imagethumbnail must now be modified like so:

    path/image.jpg must be changed to path/image-th.jpg

    so I have to replace the existing last 4 characters with "-th.jpg"

    I'm going to do some research on how to do substring queries, but maybe you'll answer this before I dig up a method elsewhere on the net, thanks again

  8. #8
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    if you are sure all imagenames ending with ".jpg" (4 chars extension forexample not ".jpeg"), you can insert "-th" into imagename like this:
    in design view of the update query paste this to "update to" section for imagethumbnail field:
    Code:
    Left([imagethumbnail],Len([imagethumbnail])-4)+"-th"+Right([imagethumbnail],4)
    or in sql view paste this code as sql text
    Code:
    UPDATE Products SET Products.imagethumbnail = Left([imagethumbnail],Len([imagethumbnail])-4)+"-th"+Right([imagethumbnail],4);
    again I didn't test these either you better have a backup of your dtaabase before running these queries.
    ghozy.

  9. #9
    Join Date
    Apr 2004
    Posts
    9
    didn't see your reply first and I found a page detailing the string functions of access, so I ended up doing it like this --


    Left( [thumbnail] , Len( [thumbnail] ) - 4 ) + "-th.jpg"

    which worked perfectly although access yelled at me telling me that 200 records weren't updated (there are around 200 blanks) so it's not completely robust.

    yours is a bit better though since it doesn't change the extension, just moves it over. I knew all the extensions were jpg though so I wasn't worried about that.

    thanks for the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •