#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Sertaozinho-SP
    Posts
    45
    Rep Power
    16

    SQL query in an Access DataBase


    Hi,

    I have a database that lists names of files from pictures of products. For example,

    404 is a code. The respective file name is 404_1.jpg. We need the
    suffix because we will have another files like
    404_02.jpg

    and 404_xx.jpg

    This database only has file names in one field.

    My SQL query string is:

    sql = "SELECT foto FROM fotos WHERE foto LIKE '%" & prod & "_%.jpg' "
    sql = sql & "ORDER BY foto"

    fotos is the table
    foto is the field
    prod is a string that represents the current product's code
    like 404.

    However, this SQL string returns
    all files for 404 and for 4043

    If you could please give me some help about it I would enjoy a lot!

    Thanks in advance!

    Daniel
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    in Orbit mostly
    Posts
    148
    Rep Power
    16
    I'm not positive for Access, but MS SQL Server uses _ as a one character wildcard character, so I suspect the Jet Database Engine does the same on the Web Server.
    Something like:
    Code:
    sql = "SELECT foto FROM fotos WHERE foto LIKE '%" & prod & "[_]%.jpg' "
    sql = sql & "ORDER BY foto"
    might do the trick in Access too.
    I think the first % is also superfluous, and would be better trimming blanks from the source of the product number, if that were the scope of the starting%, because otherwise I would think you would also match 1404_01.jpg
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Sertaozinho-SP
    Posts
    45
    Rep Power
    16
    Hi,

    Thanks man, but I got errors when the database had more than one picture.
    So, I just added a new field in my table
    called code, so, I would have pairs:
    code-picture name files

    It is just the case of matching a variable
    prod to the code and then retrieving the files under code.

    Thanks anyway!

    Daniel

IMN logo majestic logo threadwatch logo seochat tools logo