How to Use DLookup Function

The case involves a buy levitra online you can find out more scenario that leads one with trouble during physical intimacy and sexual intercourse as there is no research involved in the production of generic medications as the medication is already in the market. The prices are considerably lesser and also it’s possible for tadalafil generic cialis customers to keep their identity confidential. sildenafil online no prescription unica-web.com Kamagra Soft Tablets Soft tabs of this brand is known as the soft tabs. You should remember however, that Kamagra Jelly will not give you the effect for more time, best cialis price rather it will put adverse impact on your health.SIDE EFFECTS :It showed diarrhea and stomach upset in some individuals, but later on it was noted that those individuals were having the weak digestion system.* Headache is mainly observed as Kamagra side effects is satisfied with the results.

Use DLookup() Function in MS Access

DLookup function is a built-in function to get the value of a particular field from the set of records or data from table, query, SQL, or Recordset. The result of DLookup function will be the particular field of the first record.

DLookup Syntax

DLookup(Expression, Domain, Criteria [optional])

• Expression identifies the field whose value you want to return. It can be a string expression identifying a filed in a table or query.
• Domain is a set of records. It can be a table name or a query name for a query does not require a parameter.
• Criteria is an optional expression to restrict the range of data. Criteria is equivalent to the WHERE clause in an SQL Expression, but without the word WHERE. Any field that is included in Criteria must also be a field in Domain.

 Example of Using DLookup() Function

#1 DLookup without Criteria

It is the simplest form of DLookup() function. Without specifying any criteria per code below, DLookup function will return the value of the first record in the CustomerName field from tbl_Customer table. The result will be “Barney Rubble” since it is the first record on tbl_Customer table as shown in the picture below.

Code:

Private Sub Command0_Click()
Dim strX As String
strX = DLookup("CustomerName", "tbl_customer")
Me.txtResult = strX
End Sub

#2 DLookup on Empty Data Record

Sometime we don’t know if the table or query has a record. When we use the DLookup() function on the empty table we will get the error message shown below. It says “Invalid Use of Null” because we attempt to assign a Null value to the variable strX.

Code:

Dim strX As String
strX = DLookup("HolidayDate", "tblHolidays")
Me.txtResult = strX
End Sub

To prevent getting the error message above, we can use IF Statement to test if there is a record found before assigning the result value to the variable strX.

Correct Code:

Private Sub Command0_Click()
Dim strX As String
If IsNull(DLookup("HolidayDate", "tblHolidays")) Then
     MsgBox "No Record Found"  ‘ or do something
Else
     strX = DLookup("HolidayDate", "tblHolidays")
     Me.txtResult = strX
End If
End Sub

#3 DLookup with Textual Criteria

The code “[UserLoginID] = ‘John’” is used as the Criteria for the DLookup function below.  When we use the equal sign (=), only the record that has the entire User Name  = “John” will be the result of this criteria. Per code below, the result will be “No Record Found” since there is no record that has the User Name “John”, but there are some records for “John Vang” or “John Smith.”

Code:

Private Sub Command0_Click()
If IsNull(DLookup("[User Name]", "tblUser", "[User Name] = 'john'")) Then
     Me.txtResult = "No Record Found"
Else
     Me.txtUserName = DLookup("[User Name]", "tblUser", "[User Name] = 'john'")
End If
End Sub

#4 DLookup with Textual Criteria that comes from a Field on Current Form

Per code below, the code “[UserLoginID] = ‘” & Me.txtLoginID & “‘” is used as the Criteria for the DLookup function.  For this example, we want to get (or look up) the User Name for the entered UserLoginID on text box UserLoginID on this form then display it on the text box User Name.

If jvang is entered on text box UserLoginID, the User Name should be “John Vang” as shown in the picture below. Two IF Statements are used for this code to check if the UserLoginID is entered and to prevent the error if the User Name is empty for the entered UserLoginID.

Code:

Private Sub cmdGetResult_Click()
Me.Refresh
If IsNull(Me.txtLoginID) Then
      MsgBox "Please enter a UserLoginID"
Else
      If IsNull(DLookup("[User Name]", "tblUser", "[UserLoginID] = '" & Me.txtLoginID & "'")) Then
           MsgBox "No User Name for this UserLoginID"
      Else
           Me.txtUserName = DLookup("[User Name]", "tblUser", "[UserLoginID] = '" & Me.txtLoginID & "'")
      End If
End If
End Sub

#5 DLookup with Numeric Criteria

The code “[UserID] = 6” is used as the numeric Criteria for the DLookup function below.  Per code below, the result will be “Adam Smith” since the UserID 6 is for the User Name “Adam Smith.”

Code:

Private Sub Command0_Click()
If IsNull(DLookup("[User Name]", "tblUser", "[UserID] = 6")) Then
    Me.txtUserName= "No Record Found"
Else
    Me.txtUserName = DLookup("[User Name]", "tblUser", "[UserID] = 6")
End If
End Sub

#6 DLookup with a Numeric Criteria that comes from the field on current Form

The code “[UserID] = Me.txtUserID” is used as the numeric Criteria for the DLookup function below.  Per code below, the result will be “John Vang” since the UserID 3 is for the User Name “John Vang” per the tblUser table above.

Code:

Private Sub cmdGetResult_Click()
Me.Refresh
If IsNull(Me.txtUserID) Then
     MsgBox "Please enter a UserID"
Else
     If IsNull(DLookup("[User Name]", "tblUser", "[UserID] = " & Me.txtUserID)) Then
         Me.txtUserName = "No Record Found"
     Else
         Me.txtUserName = DLookup("[User Name]", "tblUser", "[UserID] = " & Me.txtUserID)
     End If
End If
End Sub

#7 DLookup Query with Date Criteria

The DLookup function can be used to look up information on query as example below. The code “[FinishDate] = #4/30/2014#” is used as the date Criteria for the DLookup function below.  Per code below, the result will be “Task 5″ since the “Task 5” has a finish date 4/30/2014 per the query “task completed” below.

Code:

Private Sub Command0_Click()
If IsNull(DLookup("[TaskName]", "task completed", "[FinishDate] = #4/30/2014#")) Then
     Me.txtResult = "No Record Found"
Else
     Me.txtResult = DLookup("[TaskName]", "task completed", "[FinishDate] = #4/30/2014#")
End If
End Sub