ADO RecordSet by Writing the WHERE cluase for a Part of a field it dos not work for me

Posted on 2005-04-10
Last Modified: 2010-05-02
I Am Using MS Access 2003 SP1
and ADO 2.7 library

Dim CN As New ADODB.Connection
   Set CN = New ADODB.Connection
   CN.CursorLocation = adUseClient
   CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & App.Path & "\data.mdb; Persist Security Info=False"
   CN.Mode = adModeReadWrite

Dim RS As New ADODB.Recordset, SqlSql As String
    SqlSql = "SELECT [LastName] & ' ' & [FirstName]  AS Name1 FROM ListNames WHERE ListNames.LastName LIKE '*p*'"
    If RS.State = adStateOpen Then Set RS = Nothing
  RS.CursorLocation = adUseClient
  RS.Open SqlSql, CN, adOpenKeyset, adLockPessimistic, adCmdText
        MsgBox "There are no records in the List or Selection", vbCritical, TovTtl
        MsgBox RS!Name1
End If

i have many fields with the letter P in it, and my recordset dos not find it
I experimented, and it appears that the wildcard does not work in my recordset.

Does anyone know a way around this?

Question by:YomTovSuccos
    1 Comment
    LVL 8

    Accepted Solution

    instead of this::
    ListNames.LastName LIKE '*p*'

    Try this::
    ListNames.LastName LIKE '%p%'

    Good luck

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now