Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Question Unanswered: Query returns empty recordset?!?!?

    Here's what I have:
    A VB6 application that connects to an Access 2000 database. The database has 15 fields -> 1 "autonumber" field and 14 text fields. The application has 14 textboxes. I need the user to be able to enter information into any or none of the textboxes and, through a wildcard query, return any matching records. There are quite a few blank fields in the database (ie. Not all of the records have information in all of the fields).

    The SQL string looks something like...
    "SELECT * FROM MyTable WHERE firstfield LIKE '" & "%" & "txtbox1.text" & "%" &"'"
    followed by several AND statements for each of the 13 other fields.

    Here's the problem.....the query only works if all 14 textboxes are filled in with something & there is a match in the database. If I leave all of the textboxes empty, no records are returned. If I leave some of the textboxes blank, it will return records if that particular field is blank in the database. If the user left all of the textboxes blank, I would like it to return every record in the database.

    The app does not return any errors. In summary, the query searches for "blanks" and is ignoring the % (wildcard).

    Any thoughts, comments or suggestions would be greatly appreciated. Thank you in advance.

    Gary

  2. #2
    Join Date
    Aug 2003
    Location
    Bologna - Italy
    Posts
    209
    First and foremost, i think you've got a pair of double quotes in excess:

    "SELECT * FROM MyTable WHERE firstfield LIKE '" & "%" & txtbox1.text & "%" &"'"

    But second one, the LIKE operator, if called with an empty value, would match only EMPTY fields, so if you run a query with all LIKE operators with empty parameters, you're asking for the records which have ALL EMPTY FIELDS, so i think the DB gives you the correct answer, NO RECORDS...

    You must have a check with an IF statement which inserts the LIKE clause only if the corresponding textbox has been filled, or omits it if the texbox is empy.

    Doing so will result in a query which has NO like causes if you leave all textboxes empty, which will, in turn, return ALL the records to the listing.

    Bye!
    The only failure is not trying to do it.

  3. #3
    Join Date
    Jul 2003
    Posts
    81

    Cool Re: Query returns empty recordset?!?!?

    All the 13 fields are from the same table?

  4. #4
    Join Date
    Aug 2003
    Posts
    2

    Smile

    To Shores:
    You were correct about the extra double quotes - thank you.
    Secondly, what you said about the LIKE statement makes perfect sense & I will try that. Here's the weird thing (at least to me)....the original DB was done in DBII or DBIII (remember those?). I imported the database to Access 2000, saved it as a new file, and the SQL statement (with all the LIKE's) worked fine. Then I copied 14 of the fields to a new table in the same Access DB and tried the same SQL statement....and it gave the results I posted here. Oh well....live and learn. Then, learn some more!

    To Mastermind:
    Yes, all of the fields are in the same table. See above for how they got there.

    Thanks for your time!!!!!!

  5. #5
    Join Date
    Aug 2003
    Location
    Bologna - Italy
    Posts
    209
    About the LIKE clause: it may be that the LIKE clause functions differently in DBIII, as it may not be fully comliant with standard SQL, and it may be that the presence of other fields that did not appear in the query clauses in the first Access table, changed the meaning of the LIKE clauses...

    In any way, removing the entire LIKE clause when nothing has been specified in the textbox is a good idea also because it makes simpler the work of the DB engine, resulting in a leaner and faster query.

    Bye!
    The only failure is not trying to do it.

Posting Permissions

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