Modify DLook Function

Posted on 2009-02-17
Last Modified: 2013-12-12
In the code below the DLookup function is searching a query named RevokedLicense. This column in this query that is to be searched is named CompleteName. It is looking for a name that matches the name that has been entered into a textbox that is also named CompleteName. The problem is it needs to be able to locate either John Doe or John W Doe and currently the name has to be exact. Can the code be modified to accommodate this.
Private Sub Command175_Click()

If IsNull(DLookup("[CompleteName]", "RevokedLicense", "[CompleteName]='" & Me![CompleteName] & "'")) = False Then

Me.Label177.Caption = "REVOKED"


Me.Label177.Caption = ""


End Sub

Open in new window

Question by:mickeyshelley1
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)

    try this:

    If IsNull(DLookup("[CompleteName]", "RevokedLicense", "[CompleteName] like " & Chr(34)  & Me![CompleteName] & Chr(34) & "*")) = False Then
    LVL 119

    Expert Comment

    by:Rey Obrero

    is the table wher you derived the query RevokedLicense also show the CompleteName and not broken to FirstName | LastName | MI  ?

    Author Comment

    Both show complete name
    LVL 119

    Accepted Solution

    the best you can do is to add two columns that split the Completename into FirstName and LastName
    and, on the Form you have to use also two textboxes for First and Last
    this will not be 100% accurate as there will be cases of duplicates.

    If Not Isnull(Dlookup("Completename","RevokedLicense", "FirstName='" & me.FirstName &"' and Lastname=" & chr(34) me.Lastname & chr(34) & ")) then

    Author Closing Comment

    Thanks Again
    LVL 119

    Expert Comment

    by:Rey Obrero
    you may also use a combo box to select the name.
    you can include the completeName and other fields that will make an entry unique from the other records.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
    Let’s list some of the technologies that enable smooth teleworking. 
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

    734 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

    19 Experts available now in Live!

    Get 1:1 Help Now