Welcome to SPN

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

Sign Up Now!

SQL Recordset criteria with a *

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

  1. Hindsey

    Hindsey
    Expand Collapse
    Guest

    Below is my code. I have a form that has a field named [Criteria] and a
    button that when clicked creates a new recordset and sets the form to use the
    recordset:

    Dim rsCodes As ADODB.Recordset
    Set rsCodes = New ADODB.Recordset
    sSQL = "SELECT * FROM tabCodeIncidentType Where [Description] Like
    'Fire*'"
    rsCodes.Open sSQL, CurrentProject.Connection, adOpenKeyset,
    adLockOptimistic

    Set Forms("frmLookupCodes").Recordset = rsCodes

    When I click the button to run this code, all of the records on the form go
    away (I originally opened the form without any criteria and displayed all the
    records. I am wondering what is wrong with my code. If I remove the * after
    'Fire, then it does bring up a record that contains only the word Fire in the
    description field. However, that goes away when the * is put in.

    Any help?
     
  2. Loading...

    Similar Threads Forum Date
    Microsoft Brings 64-Bit Perks in SQL Server 2000 Service Pack (Ziff Davis) Interfaith Dialogues May 7, 2005

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    * (and ?) only work as wildcard characters with DAO. ADO uses % and _ as
    wildcard characters.

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


    "Hindsey" <Hindsey@discussions.microsoft.com> wrote in message
    news:DCEB5E71-180C-4531-9C87-694CB0DE293E@microsoft.com...
    > Below is my code. I have a form that has a field named [Criteria] and a
    > button that when clicked creates a new recordset and sets the form to use

    the
    > recordset:
    >
    > Dim rsCodes As ADODB.Recordset
    > Set rsCodes = New ADODB.Recordset
    > sSQL = "SELECT * FROM tabCodeIncidentType Where [Description] Like
    > 'Fire*'"
    > rsCodes.Open sSQL, CurrentProject.Connection, adOpenKeyset,
    > adLockOptimistic
    >
    > Set Forms("frmLookupCodes").Recordset = rsCodes
    >
    > When I click the button to run this code, all of the records on the form

    go
    > away (I originally opened the form without any criteria and displayed all

    the
    > records. I am wondering what is wrong with my code. If I remove the *

    after
    > 'Fire, then it does bring up a record that contains only the word Fire in

    the
    > description field. However, that goes away when the * is put in.
    >
    > Any help?
     
  4. Hindsey

    Hindsey
    Expand Collapse
    Guest

    Great - Thanks a lot.

    "Douglas J. Steele" wrote:

    > * (and ?) only work as wildcard characters with DAO. ADO uses % and _ as
    > wildcard characters.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Hindsey" <Hindsey@discussions.microsoft.com> wrote in message
    > news:DCEB5E71-180C-4531-9C87-694CB0DE293E@microsoft.com...
    > > Below is my code. I have a form that has a field named [Criteria] and a
    > > button that when clicked creates a new recordset and sets the form to use

    > the
    > > recordset:
    > >
    > > Dim rsCodes As ADODB.Recordset
    > > Set rsCodes = New ADODB.Recordset
    > > sSQL = "SELECT * FROM tabCodeIncidentType Where [Description] Like
    > > 'Fire*'"
    > > rsCodes.Open sSQL, CurrentProject.Connection, adOpenKeyset,
    > > adLockOptimistic
    > >
    > > Set Forms("frmLookupCodes").Recordset = rsCodes
    > >
    > > When I click the button to run this code, all of the records on the form

    > go
    > > away (I originally opened the form without any criteria and displayed all

    > the
    > > records. I am wondering what is wrong with my code. If I remove the *

    > after
    > > 'Fire, then it does bring up a record that contains only the word Fire in

    > the
    > > description field. However, that goes away when the * is put in.
    > >
    > > Any help?

    >
    >
    >
     

Share This Page