Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Jan 2004
    Location
    Minnesota
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MS-Access and query string

    I have an Access DB I'm running a query search on (from a search page) works fine (well sort of).
    Example: If I enter "company" in the search field it will find any record with just the word company, what I need it to find is any record with the word company any were in the search field (IE: my company).
    I know in access you can use * as a wildcard (IE "*"& [rsSearch__MMColParam]&"*") would find both "company" and "my company".
    Is there a way to do this


    ** part of current code **
    Code:
    <%
    Dim rsSearch__MMColParam
    rsSearch__MMColParam = "1"
    If (Request.QueryString("search_field") <> "") Then 
      rsSearch__MMColParam = Request.QueryString("search_field")
    End If
    %>
    <%
    Dim rsSearch
    
    Set rsSearch = Server.CreateObject("ADODB.Recordset")
    rsSearch.ActiveConnection = MM_BLOCK_STRING
    rsSearch.Source = "SELECT *  FROM Salesleads  WHERE Company = '" + Replace(rsSearch__MMColParam, "'", "''") + "'  ORDER BY CallDate ASC"
    rsSearch.CursorType = 0
    rsSearch.CursorLocation = 2
    rsSearch.LockType = 1
    rsSearch.Open()
    
    %>

    Thanks for any help in advance

    J.C.
    To learn is a good thing and today I hope to learn something new!

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If i undertand it correctly, then you are just looking for the LIKE operator with a % wildcard. Like

    rsSearch.Source = "SELECT * FROM Salesleads WHERE Company LIKE ('%" & rsSearch__MMColParam & "%') ORDER BY CallDate ASC"


    this will select all records which values for column Company contains the searchterm.
    Like "mycompany", "my company", "companyyyyy", "company"
    % means '0 or more' characters
    Last edited by raf; 08-26-2004 at 10:34 PM.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    Regular Coder
    Join Date
    Oct 2002
    Posts
    380
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Except Microsoft knows better than everyone else using sql: you need to use the '*' character instead of '%' as the wild-card in Access.

    Try
    WHERE Company LIKE ('*' & rsSearch__MMColParam & '*')

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Kiwi
    Except Microsoft knows better than everyone else using sql: you need to use the '*' character instead of '%' as the wild-card in Access.

    Try
    WHERE Company LIKE ('*' & rsSearch__MMColParam & '*')
    Hmm. That's not completely correct since Jet SQL 4.x supports the ANSI wildcards % and _

    In Jet SQL, its
    * for 0 or more characters (and from Jet 4.x you can also use %)
    ? for one character (end from Jet 4.x you can also use _)


    But still, i think your advise is better then mine, because it'll always work for Jet db's.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #5
    Regular Coder
    Join Date
    Oct 2002
    Posts
    380
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oh, OK. I tested it in Access (2003 or XP), rather than connecting to the Jet engine directly. '*' worked in Access, but '%' didn't - but I guess this is an application thing, rather than an engine thing.

    <rant>
    Why, oh why does Microsoft insist on doing things its own way where there are perfectly good standards out there?

    Don't answer that - it's a rhetorical question and I don't want to sidetrack the topic.
    </rant>

  • #6
    New Coder
    Join Date
    Jan 2004
    Location
    Minnesota
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks raf, the '%' did it.

    J.C.

  • #7
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Kiwi
    Oh, OK. I tested it in Access (2003 or XP), rather than connecting to the Jet engine directly. '*' worked in Access, but '%' didn't - but I guess this is an application thing, rather than an engine thing.

    <rant>
    Why, oh why does Microsoft insist on doing things its own way where there are perfectly good standards out there?

    Don't answer that - it's a rhetorical question and I don't want to sidetrack the topic.
    </rant>
    Since the problem got solve, we can sidetrack
    It's actually quite impressive, that given the huuuuuge amount of bugs/shortcommings/'features that are actually diversions from standards' etc, microsoft still managesto at least document each problem
    From http://office.microsoft.com/assistan...CH010410151033
    The ANSI SQL wildcards are only available when using Jet 4.X and the Microsoft OLE DB Provider for Jet. If you try to use the ANSI SQL wildcards through Microsoft Access or DAO, then they will be interpreted as literals. The opposite is true when using the Microsoft OLE DB Provider for Jet and Jet 4.X.
    Why they don't follow standards? Probably a bit because they were/are ruled by geeks. Standards allways have downsides. There will always be situations where (at a given moment in time, for a specific developpersteam, that needs to meet specific customerneeds) the 'best' sollution is a non-standard compliant sollution. It's inherent to standards. Yhey are never in all situations the best sollutions.
    And it's kinda typical for geeks to then choose for the 'best' sollution which in the long run creates these diversions ORn more common, creates IT-departmens where 86 languages are used (50 of them aren't spported anymore so applicatrions written in it are on 'paliative care'), 8 db-formats are used, 4 designertools are used etc etc. Like the company i currently work for. All choosen to be the best sollution for that specific problem etc...

    And even though, they have created standards now, there are always older things (sometimes development started a few years ago, before the standards were created) that don't comform to them.

    What they did here, adding ANSI support in specific situations, probably just made it worse.

    if they would make IE fully standardcompliant, the mess they would be creating for a lott of existing sites that implemented aal sorts of workarounds for there current non-compliance, would become a mess.
    So maybe in some situations, you don't have much of a choice then to stuck by your original gameplan, although it has become standard-deficient by now.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #8
    New to the CF scene
    Join Date
    Oct 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    So in MS Access Query SQL View '*' looks like:

    SELECT SomeTable.SomeRow
    FROM SomeTable
    WHERE (SomeTable.SomeFieldValue) Like "SomeValue*";

    It will show only rows where "SomeValue bla bla" as well as "SomeValue blabla..." and others with "SomeValue" at the begining.


  •  

    Posting Permissions

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