Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102

    Unanswered: use a whole recordset for a 'LIKE' query (was "quickie")

    trying to search a DB for all records within a range.
    I have a recordset containing about 10 postcodes and I need to find all the records containing any one of those postcodes
    currently I have:
    Code:
    sqlString= "SELECT aucTitle FROM tblAPAuctions WHERE aucPostalCode LIKE '%" & left(rsPostcodeResult, 4) & "%'"
    but I am getting 'type mismatch' error.

    Thanks for looking
    Mark

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    what data type is field aucPostalCode ?

    also, what is the data type of variable rsPostcodeResult? If it's a recordset, you'll need to add the field name.

    (rsPostcodeResult.Fields!PostalCode)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    aucPostalCode is Varchar[20] and rsPostcodeResult is a recordset (of 4 character postcodes)
    I'll just check your example

  4. #4
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    I did this:
    "SELECT aucTitle FROM tblAPAuctions WHERE aucPostalCode LIKE '%" & left(rsPostcodeResult.Fields!postcode, 4) & "%'"
    but it didn't like the !

  5. #5
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    thanks for your hints so far guys, I'm afraid I'm not getting anywhere with this.
    I don't know what the syntax is to use a whole recordset for a 'LIKE' query.
    and I have tried creating a loop to iterate thru the (small) recordset and send each field to the 'LIKE' query but that's even more difficult - I can't figure it out.
    Please help!
    Thanks
    Last edited by darkmunk; 01-16-07 at 12:46.

  6. #6
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Well, you need all pc's in the recordset and you want one query find it for you. It looks like you're trying it from VB so you have to build the statement while looping throug the resultset with the wanted pc's.

    After the loop your statement should be looking something like this:
    Code:
    SELECT aucTitle
    FROM tblAPAuctions 
    WHERE aucPostalCode LIKE '%1234%'
    OR aucPostalCode LIKE '%1235%'
    OR aucPostalCode LIKE '%1236%'
    OR aucPostalCode LIKE '%1237%'
    etc.
    As I'm not an expert on VB so I don't know exactly how to loop through the resultset but I'm sure it's something like:
    Code:
    While Not rs.EOF
      <concat the string>
      rs.FetchNext
    Loop

  7. #7
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    thanks, that's basically what I was trying but I was getting head-spin trying to concatenate it and increment my variable 'code(x)'
    Code:
    rsPostcodeResult.moveFirst
          sqlString= "SELECT aucTitle FROM tblAPAuctions WHERE aucPostalCode LIKE '%"
    	  Do while not rsPostcodeResult.eof
    	  x=x+1
    		 code&x = rsPostcodeResult("postcode")
    			sqlString=sqlString & " or code&x & "%'"
    
    	Loop
    any ideas how I clean this up? (don't laugh!)
    Last edited by darkmunk; 01-16-07 at 13:25.

  8. #8
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Mmmm, I'm not entirely familiar with this syntax but shouln't you at the end of the loop fetch the next row in the recordset?

    Code:
    rsPostcodeResult.moveFirst
    
    ' Fetch the first pc to avoid an OR to many!
    sqlString= "SELECT aucTitle FROM tblAPAuctions WHERE aucPostalCode LIKE '%" & rsPostcodeResult("postcode") & "%'"
    
    ' Move the set-pointer one row down and go into the loop
    rsPostcodeResult.moveNext
    Do while not rsPostcodeResult.eof
      sqlString=sqlString & " OR aucPostalCode LIKE '%" & rsPostcodeResult("postcode") & "%'"
      rsPostcodeResult.moveNext
    Loop
    Ofcourse there should at least be one row in the set! Maybe that should be checked first.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Do you really have to use LIKE? Are you passing in exact matches for your postcodes or is it important to be able to match only part of the postcode?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    That would've been my next question LIKE isn't so bad but a LIKE with a % as the first character eliminates the (efficient) use of any index. But I know in some countries the pc starts with a character, that could be a reason.

  11. #11
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    Thanks Lexiflex I'll give that a try, and get back to you.
    I may be able to use an exact query later, but that's an interesting point.

  12. #12
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    OK, that code works perfectly and I do need the 'LIKE' and the '%' wildcard, because the postcodes are not all formatted the same.
    Nice one guys. Thanks

Posting Permissions

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