We help IT Professionals succeed at work.

Modify DLook Function

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

Open in new window

Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:

try this:

If IsNull(DLookup("[CompleteName]", "RevokedLicense", "[CompleteName] like " & Chr(34)  & Me![CompleteName] & Chr(34) & "*")) = False Then
CERTIFIED EXPERT
Top Expert 2016

Commented:

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

Author

Commented:
Both show complete name
CERTIFIED EXPERT
Top Expert 2016
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks Again
CERTIFIED EXPERT
Top Expert 2016

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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.