'Looks up the value of [CountOfStatus] in the Query [Qry_Status] Where the value of status is 1-NEW

StrValueNEW = DLookup("[CountOfStatus]", "Qry_Status", "[Status] = '1-NEW'")



Syntax for NUMERICAL values:

  • DLookup("FieldName" , "TableName" , "Criteria = n")
  • DLookup("[UnitPrice]", "Order Details", "OrderID = 10248")


Syntax for STRING: (note the single apostrophe before and after the string value)

  • DLookup("FieldName" , "TableName" , "Criteria= 'string'")

Syntax for VARIABLES: (note the single apostrophe before and after the string value)

  • StrCoName = DLookup("[Header-Footer]", "TblCoDetails", "CoRef = " & "'" & StrVisibleView & "'")
  • It look s up the field [Header-Footer] in the table [TblCoDetails] where the field [CoRef] = StrVisibleView


Syntax for FORM FIELDS: 

  • DLookup("CustomerID", "Orders", "OrderID = " & Forms![Orders]!OrderID)
  • This example would return the CustomerID field from the Orders table for the record that is currently being displayed in the Orders form (based on OrderID).


Syntax for DATES: (note the # before and after the date value)

  • DLookup("FieldName" , "TableName" , "Criteria= #date#")
  • Example
    • Dim LDate As Date

      LDate = DLookup("OrderDate", "Orders", "OrderID = 10248")

  • In this example, the variable called LDate would now contain the OrderDate value from the Orders table where the OrderID is 10248.


Spaces 

    You cant DLOOKUP where the value you you are using to search has spaces in it .. !!!


Null Values

quantityRegion = DLookup("[Quantity]", "tblSalesData", "[regionCode] = " & i & " AND [Year] = " & yr)


If (IsNull(quantityRegion)) Then

'nothing

Else

'bunch of code

...

End If


Dealing with a NULL value returned by the DLookup result

    Use the "NZ Function" to deal with "NULLS"


TBSQty = Nz(DLookup("[Qty]", "[QryTBSQty]"))   --- This means that if the QTY field is Null it will accept it and run


Links