• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

Modify DLook Function

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"
Else
Me.Label177.Caption = ""
 
 
 
End Sub

Open in new window

0
mickeyshelley1
Asked:
mickeyshelley1
  • 3
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:

try this:

If IsNull(DLookup("[CompleteName]", "RevokedLicense", "[CompleteName] like " & Chr(34)  & Me![CompleteName] & Chr(34) & "*")) = False Then
0
 
Rey Obrero (Capricorn1)Commented:

is the table wher you derived the query RevokedLicense also show the CompleteName and not broken to FirstName | LastName | MI  ?
0
 
mickeyshelley1Author Commented:
Both show complete name
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Rey Obrero (Capricorn1)Commented:
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
0
 
mickeyshelley1Author Commented:
Thanks Again
0
 
Rey Obrero (Capricorn1)Commented:
Chief,
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.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now