Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Table with wildcards

Discussion in 'Information Technology' started by MikeP, Jul 28, 2006.

  1. MikeP

    MikeP
    Expand Collapse
    Guest

    I'm making a search engine for a table that contains entries like these

    10300 (??? SF)

    6 mm² (4 + G)

    0.138"- 0.414"

    # 12 AWG (19)


    in various text fields. These symbols are causing problems with the search
    queries. in fact Access cant even filter for them properly. Any tips?
     
  2. Loading...

    Similar Threads Forum Date
    As A Child, Public Marches Of Sikhism Made Me Uncomfortable. They Still Do . Why ? Whats The Logic Hard Talk Oct 16, 2016
    Arts/Society Backyard Vegetable Garden Language, Arts & Culture Dec 27, 2013
    Heritage Now a Vegetable Market, Ranjit Singh's Royal Haveli a Picture of Neglect History of Sikhism Nov 11, 2013
    Sikhism Helium: 1984 and the "Periodic Table of Hate" (Jaspreet Singh) Book Reviews & Editorials Oct 28, 2013
    Heritage How our entire history was dumped in a horse stable History of Sikhism Oct 28, 2013

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Surround special characters with square brackets. Assuming you want anything
    with three question marks in it, you'd use LIKE "*[?][?][?]*". For anything
    with an octothorpe (#) in it, use LIKE "*[#]*"

    Double up the quotes. To find something with double quotes inside the
    string, use LIKE "*""*" (exagerated for clarity, that's LIKE " * " " * ")

    I had no problems using LIKE "*mm²*". What problem are you having?


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "MikeP" <MikeP@discussions.microsoft.com> wrote in message
    news:2B7B8687-7EEB-4B91-9400-373DF06DC979@microsoft.com...
    > I'm making a search engine for a table that contains entries like these
    >
    > 10300 (??? SF)
    >
    > 6 mm² (4 + G)
    >
    > 0.138"- 0.414"
    >
    > # 12 AWG (19)
    >
    >
    > in various text fields. These symbols are causing problems with the search
    > queries. in fact Access cant even filter for them properly. Any tips?
     
  4. MikeP

    MikeP
    Expand Collapse
    Guest

    The table is a link to a read only table in an Infor database. My search tool
    is made so that there are multiple pulldown boxes with potential criteria
    listed to assist in te search. Each selection filters the next one so the
    user doesnt have to type anything to narrow down the search. The symbol thats
    giving me the most problems is #, because # doesnot find itself, while ?
    does. Is there anyway to temporaily deactivate wildcards?

    "Douglas J. Steele" wrote:

    > Surround special characters with square brackets. Assuming you want anything
    > with three question marks in it, you'd use LIKE "*[?][?][?]*". For anything
    > with an octothorpe (#) in it, use LIKE "*[#]*"
    >
    > Double up the quotes. To find something with double quotes inside the
    > string, use LIKE "*""*" (exagerated for clarity, that's LIKE " * " " * ")
    >
    > I had no problems using LIKE "*mm²*". What problem are you having?
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "MikeP" <MikeP@discussions.microsoft.com> wrote in message
    > news:2B7B8687-7EEB-4B91-9400-373DF06DC979@microsoft.com...
    > > I'm making a search engine for a table that contains entries like these
    > >
    > > 10300 (??? SF)
    > >
    > > 6 mm² (4 + G)
    > >
    > > 0.138"- 0.414"
    > >
    > > # 12 AWG (19)
    > >
    > >
    > > in various text fields. These symbols are causing problems with the search
    > > queries. in fact Access cant even filter for them properly. Any tips?

    >
    >
    >
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    No, there's no way to disable wildcards.

    What exactly are you doing with the data? I don't understand from your
    description.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "MikeP" <MikeP@discussions.microsoft.com> wrote in message
    news:B4B4E53D-8FD9-4412-B65F-52C7C7F7636F@microsoft.com...
    > The table is a link to a read only table in an Infor database. My search

    tool
    > is made so that there are multiple pulldown boxes with potential criteria
    > listed to assist in te search. Each selection filters the next one so the
    > user doesnt have to type anything to narrow down the search. The symbol

    thats
    > giving me the most problems is #, because # doesnot find itself, while ?
    > does. Is there anyway to temporaily deactivate wildcards?
    >
    > "Douglas J. Steele" wrote:
    >
    > > Surround special characters with square brackets. Assuming you want

    anything
    > > with three question marks in it, you'd use LIKE "*[?][?][?]*". For

    anything
    > > with an octothorpe (#) in it, use LIKE "*[#]*"
    > >
    > > Double up the quotes. To find something with double quotes inside the
    > > string, use LIKE "*""*" (exagerated for clarity, that's LIKE " * " " *

    ")
    > >
    > > I had no problems using LIKE "*mm²*". What problem are you having?
    > >
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no private e-mails, please)
    > >
    > >
    > > "MikeP" <MikeP@discussions.microsoft.com> wrote in message
    > > news:2B7B8687-7EEB-4B91-9400-373DF06DC979@microsoft.com...
    > > > I'm making a search engine for a table that contains entries like

    these
    > > >
    > > > 10300 (??? SF)
    > > >
    > > > 6 mm² (4 + G)
    > > >
    > > > 0.138"- 0.414"
    > > >
    > > > # 12 AWG (19)
    > > >
    > > >
    > > > in various text fields. These symbols are causing problems with the

    search
    > > > queries. in fact Access cant even filter for them properly. Any tips?

    > >
    > >
    > >
     
  6. MikeP

    MikeP
    Expand Collapse
    Guest

    This is the sql code embedded in one of my combo boxes

    SELECT DISTINCT dbo_relQIS.Attrib01
    FROM dbo_relQIS
    WHERE (((dbo_relQIS.Grp) Like "*" & [Forms]![Search]![Group] & "*")) OR
    ((([Forms]![Search]![Group]) Is Null))
    ORDER BY dbo_relQIS.Attrib01;

    The user has specified the group of parts he wants to search in. The box
    displays all the status attributes of items that belong to that group, the
    user can pick a status description from this pull down list and hit find to
    bring up all the items in these 2 subsets. But if the record in Attrib01 or
    Group contains the character # then the search wont bring up anything. It is
    a read only parts search.


    "Douglas J Steele" wrote:

    > No, there's no way to disable wildcards.
    >
    > What exactly are you doing with the data? I don't understand from your
    > description.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "MikeP" <MikeP@discussions.microsoft.com> wrote in message
    > news:B4B4E53D-8FD9-4412-B65F-52C7C7F7636F@microsoft.com...
    > > The table is a link to a read only table in an Infor database. My search

    > tool
    > > is made so that there are multiple pulldown boxes with potential criteria
    > > listed to assist in te search. Each selection filters the next one so the
    > > user doesnt have to type anything to narrow down the search. The symbol

    > thats
    > > giving me the most problems is #, because # doesnot find itself, while ?
    > > does. Is there anyway to temporaily deactivate wildcards?
    > >
    > > "Douglas J. Steele" wrote:
    > >
    > > > Surround special characters with square brackets. Assuming you want

    > anything
    > > > with three question marks in it, you'd use LIKE "*[?][?][?]*". For

    > anything
    > > > with an octothorpe (#) in it, use LIKE "*[#]*"
    > > >
    > > > Double up the quotes. To find something with double quotes inside the
    > > > string, use LIKE "*""*" (exagerated for clarity, that's LIKE " * " " *

    > ")
    > > >
    > > > I had no problems using LIKE "*mm²*". What problem are you having?
    > > >
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no private e-mails, please)
    > > >
    > > >
    > > > "MikeP" <MikeP@discussions.microsoft.com> wrote in message
    > > > news:2B7B8687-7EEB-4B91-9400-373DF06DC979@microsoft.com...
    > > > > I'm making a search engine for a table that contains entries like

    > these
    > > > >
    > > > > 10300 (??? SF)
    > > > >
    > > > > 6 mm² (4 + G)
    > > > >
    > > > > 0.138"- 0.414"
    > > > >
    > > > > # 12 AWG (19)
    > > > >
    > > > >
    > > > > in various text fields. These symbols are causing problems with the

    > search
    > > > > queries. in fact Access cant even filter for them properly. Any tips?
    > > >
    > > >
    > > >

    >
    >
    >
     
  7. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Tue, 13 Jun 2006 10:16:02 -0700, MikeP
    <MikeP@discussions.microsoft.com> wrote:

    >Is there anyway to temporaily deactivate wildcards?


    Don't use LIKE in your SQL.

    John W. Vinson[MVP]
     
  8. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Create a function along the lines of FixSp (for Fix Special Characters)
    that's as simple as:

    Function FixSp(InputText As Variant) As String
    Dim strOutputText As String

    strOutputText = InputText & vbNullString
    strOutputText = Replace(strOutputText, "#", "[#]")
    strOutputText = Replace(strOutpuText, "?", "[?]")
    ' Add any other characters which which you're having problems

    FixSp = strOutputText

    End Function

    then change your

    WHERE (((dbo_relQIS.Grp) Like "*" & FixSp([Forms]![Search]![Group]) & "*"))

    (since FixSp always returns a string, you'll end up with WHERE
    (((dbo_relQIS.Grp) Like "**")) when
    Forms]![Search]![Group] is null, so you don't really need that condition
    anymore.)

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "MikeP" <MikeP@discussions.microsoft.com> wrote in message
    news:F3561374-DFE7-470D-B3C2-49A879708AAC@microsoft.com...
    > This is the sql code embedded in one of my combo boxes
    >
    > SELECT DISTINCT dbo_relQIS.Attrib01
    > FROM dbo_relQIS
    > WHERE (((dbo_relQIS.Grp) Like "*" & [Forms]![Search]![Group] & "*")) OR
    > ((([Forms]![Search]![Group]) Is Null))
    > ORDER BY dbo_relQIS.Attrib01;
    >
    > The user has specified the group of parts he wants to search in. The box
    > displays all the status attributes of items that belong to that group, the
    > user can pick a status description from this pull down list and hit find

    to
    > bring up all the items in these 2 subsets. But if the record in Attrib01

    or
    > Group contains the character # then the search wont bring up anything. It

    is
    > a read only parts search.
    >
    >
    > "Douglas J Steele" wrote:
    >
    > > No, there's no way to disable wildcards.
    > >
    > > What exactly are you doing with the data? I don't understand from your
    > > description.
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "MikeP" <MikeP@discussions.microsoft.com> wrote in message
    > > news:B4B4E53D-8FD9-4412-B65F-52C7C7F7636F@microsoft.com...
    > > > The table is a link to a read only table in an Infor database. My

    search
    > > tool
    > > > is made so that there are multiple pulldown boxes with potential

    criteria
    > > > listed to assist in te search. Each selection filters the next one so

    the
    > > > user doesnt have to type anything to narrow down the search. The

    symbol
    > > thats
    > > > giving me the most problems is #, because # doesnot find itself, while

    ?
    > > > does. Is there anyway to temporaily deactivate wildcards?
    > > >
    > > > "Douglas J. Steele" wrote:
    > > >
    > > > > Surround special characters with square brackets. Assuming you want

    > > anything
    > > > > with three question marks in it, you'd use LIKE "*[?][?][?]*". For

    > > anything
    > > > > with an octothorpe (#) in it, use LIKE "*[#]*"
    > > > >
    > > > > Double up the quotes. To find something with double quotes inside

    the
    > > > > string, use LIKE "*""*" (exagerated for clarity, that's LIKE " * " "

    *
    > > ")
    > > > >
    > > > > I had no problems using LIKE "*mm²*". What problem are you having?
    > > > >
    > > > >
    > > > > --
    > > > > Doug Steele, Microsoft Access MVP
    > > > > http://I.Am/DougSteele
    > > > > (no private e-mails, please)
    > > > >
    > > > >
    > > > > "MikeP" <MikeP@discussions.microsoft.com> wrote in message
    > > > > news:2B7B8687-7EEB-4B91-9400-373DF06DC979@microsoft.com...
    > > > > > I'm making a search engine for a table that contains entries like

    > > these
    > > > > >
    > > > > > 10300 (??? SF)
    > > > > >
    > > > > > 6 mm² (4 + G)
    > > > > >
    > > > > > 0.138"- 0.414"
    > > > > >
    > > > > > # 12 AWG (19)
    > > > > >
    > > > > >
    > > > > > in various text fields. These symbols are causing problems with

    the
    > > search
    > > > > > queries. in fact Access cant even filter for them properly. Any

    tips?
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >
     

Share This Page