'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")
- Dim LDate As Date
- 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"
- https://support.microsoft.com/en-us/office/nz-function-8ef85549-cc9c-438b-860a-7fd9f4c69b6c
- https://www.access-programmers.co.uk/forums/threads/handling-dlookups-null-return-value.172415/
TBSQty = Nz(DLookup("[Qty]", "[QryTBSQty]")) --- This means that if the QTY field is Null it will accept it and run
Links
- https://support.office.com/en-ie/article/DLookup-Function-8896cb03-e31f-45d1-86db-bed10dca5937
- http://www.access-programmers.co.uk/forums/showthread.php?
- http://www.techonthenet.com/access/functions/domain/dlookup.php
- https://www.techonthenet.com/access/functions/domain/dlookup.php