Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL string ASP - Access LIKE usage

    **Edited by KT to include link**
    I am trying to build a SELECT statement in VBScript that pulls data from an Access db on my webserver. things are fine with I string together many AND statements but, of course, that focuses my seraches too quickly. One of the fields I am querying on needs to support the LIKE statement and pull data from any column that *contains* a particular county (many records have several counties listed). When I implement LIKE it just pulls records that MATCH the county, leaving out those records with multiple counties.

    In Access, the SQL view of the query I need looks like this:

    SELECT CRCG_Contacts.County
    FROM CRCG_Contacts
    WHERE (((CRCG_Contacts.County) Like "*falls*"));

    [In this case, 'Falls' is a particular county]

    As you may notice, the query picks up lines with multiple counties listed with the wildcard character for Access, the asterisk[*] placed on both sides. Now, on the .asp page... I placed the same type of query as this string:

    SQLstring = "SELECT * FROM CRCG_Contacts WHERE County LIKE '"&*County*&"';"

    And that nets me this error:

    Microsoft VBScript compilation (0x800A03EA)
    Syntax error
    /crcg2/Locals_Search_Results.asp, line 169, column 62
    SQLstring = "SELECT * FROM CRCG_Contacts WHERE County LIKE ' " & * County * & " ' ; "
    Microsoft VBScript compilation (0x800A03EA)
    Syntax error
    /crcg2/Locals_Search_Results.asp, line 169, column 62
    SQLstring = "SELECT * FROM CRCG_Contacts WHERE County LIKE '"&*County*&"';"

    I've tried many varients and even used other wildcard characters such as %, etc. but it seems to me that if I am querying Access the Access wildcard is what is needed and, since the coding is blocked out by double quotes the silly VB Compiler shouldn't be griping, darnit! <img src=/S/bash.gif border=0 alt=bash width=35 height=39> i tried 'On Error Resume Next' but that didn't help much.

    So, is there some special wildcard character to syntax that I am missing? sure seems so.

    For the record, this is an ASP form that is, for test purposes, posting to itself. If you need more of the code, let me know.

    You can get a feel for what I am up to in a previous posting called <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=htm&Number=84564&page=0&vie w=collapsed&sb=5&o=0&fpart=>'ASP/SQL - this is probably easy! '</A> in this forum.

    Thanks!

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: SQL string ASP - Access LIKE usage

    You put a literal character outside the "" marks. Try putting it inside:
    LIKE '%" & County & "%';"
    (I have had success with % in ASP; I think if * worked I wouldn't have tried %)

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL string ASP - Access LIKE usage

    thanks! that worked.

Posting Permissions

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