Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2007
    Posts
    3

    Unanswered: searching in access

    hello,

    i have this asp script that is suppose to query my database which works only if i enter the exact value in NewName field however i will like to change it so that it searches the field NewName with a partial search such as if the value in NewName is Firstname i want to be able to pull it up by searching for name. script below


    set objrecord=objconn.execute("SELECT * FROM results WHERE NewName LIKE'" & record & "'")


    thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Try:

    ....LIKE'*" & record & "*'"

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Nov 2007
    Posts
    3
    Cyberlynz,
    when i tired that it doesn't pull up any records from the database. any other ideas?

    thanks.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if its a text type data then try adding a wild card character (forget which int he Access world its either ? or *, or if you are using ANSI SQL compatability mode %

    eg
    "SELECT * FROM results WHERE NewName LIKE'" & record & "*'"

  5. #5
    Join Date
    Nov 2007
    Posts
    3
    healdem,
    sorry that didn't work either. when the changes is applied it doesn't pull up any records, i must tell you that the data is been input from a flash form to the asp script and then to ms access.

    thanks

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok so have you checked what is in the variable record

    can you do a debug of the sql statement
    immediately before you assign the value Id suggest you insert a breakpoint or do something like msgbox "SELECT * FROM results WHERE NewName LIKE'*" & record & "*'"

    on reading the help file it looks as if Access uses wildcards slightly differently that I though, but then again had you done that you would have known that also....

    then Id want to check that there actually is data that it should find.

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    does it work with
    ...NewName LIKE<INSERT A SPACE HERE>'" & record & "'")

    SQL ignores excess <space> chars but dies if a necessary space is missing.

    izy
    currently using SS 2008R2

Posting Permissions

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