split search string problem

Results 1 to 2 of 2

Thread: split search string problem

  1. #1
    Join Date
    Dec 1969

    Default split search string problem

    I have a search form, people can search a teachers database by amongst other things, counties (I&#039;m using Access2000). <BR><BR>if the county is say, cheshire, I get results, but if the county searched on is &#039;Greater London&#039; or &#039;West Yorkshire&#039; I don&#039;t get any records returned, even though the &#039;address4&#039; field of the database has these counties in it. <BR><BR>I am using this code in the SQL: <BR><BR><BR>....first bit then<BR>opSQL = opSQL & " WHERE t_main.lastname LIKE &#039;%" &surname& "%&#039; "<BR>opSQL = opSQL & " AND t_main.postcode LIKE &#039;%" &postcode& "%&#039; "<BR>opSQL = opSQL & " AND t_main.address4 LIKE &#039;%" &county& "%&#039; "<BR>....<BR><BR>I presume I need to format the &#039;address4&#039; search string to deal with spaces in it, but am not sure how. <BR><BR>Thanks

  2. #2
    Join Date
    Dec 1969

    Default RE: split search string problem

    &#060;%<BR>temp = Split(county," ")<BR>clause = ""<BR>For i = 0 To UBound(temp)<BR> If clause &#060;&#062; "" Then clause = clause & " AND " &#039; $$$ see notes<BR> clause = clause & "t_main.address4 LIKE &#039;%" & temp(i) & "%&#039;"<BR>Next<BR>...<BR>opSQL = opSQL & " AND (" & clause & ")"<BR>...<BR>%&#062;<BR><BR>You might want to change AND to OR in the line noted with $$$ above.<BR><BR>That way if somebody entered "Greater Ipswitch" you&#039;d get a match on either "Greater" or "Ipswitch". Your choice.<BR><BR>(Seems to me like you should be using OR in your main query, anyway. As you have it, a person would have to enter the correct postcode for the given county or you give no match. If they know the postcode, why do you or they need to bother looking for the county?)<BR><BR>

Posting Permissions

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