我試圖運行 Dlookup 以回傳查詢結果中列的所有值。由于 Dlookup 只能回傳 1 個結果，因此我在查詢中添加了一個行計數列，因此我可以使用行計數作為 Dlookup 的條件。
我的查詢結果在結果 查詢設計視圖 查詢結果 2 中如下所示
在 SQL 視圖中，查詢如下：
SELECT tblAssets.SerialNumber, tblAssets.ID, tblAssets.AssetType, tblPlacing.Location, tblPlacing.PlacingStartDate, tblPlacing.PlacingEndDate, RowNumber([ID]) AS [NO]
FROM tblAssets INNER JOIN (tblLocations RIGHT JOIN tblPlacing ON tblLocations.LocationID = tblPlacing.Location) ON tblAssets.ID = tblPlacing.AssetID
GROUP BY tblAssets.SerialNumber, tblAssets.ID, tblAssets.AssetType, tblPlacing.Location, tblPlacing.PlacingStartDate, tblPlacing.PlacingEndDate, RowNumber([ID])
HAVING (((tblAssets.AssetType)=55) AND ((tblPlacing.Location)=[Forms]![FrmPPM]![TXTLocationID]) AND ((tblPlacing.PlacingEndDate) Is Null) AND ((ResetRowNumber())<>False));
我在以下視頻 ( https://www.youtube.com/watch?v=HWbpzETe-M0 ) 中添加了行數，如果需要更多資訊，請告訴我。
查詢結果看起來是正確的，但是，當使用 Dlookup（下面）時，它回傳 null。
Dim VAR1 as Variant
VAR1 = DLookup("[SerialNumber]", "QRYPPM", "[NO] = 1")
當我嘗試回傳 [NO] 列值時，它可以作業。
VAR1 = DLookup("[NO]", "QRYPPM", "[SerialNumber] = '6501038'")
uj5u.com熱心網友回復：
我沒有你的資料，所以不能運行一個完整的測驗，但我使用我的RowNumber函式（下面）運行了類似的東西，并且DLookup能夠過濾回傳的行號（你的 [NO]）并從另一個回傳一個值場地。
因此，請嘗試使用此功能（您的 SQL 將需要稍作調整，請參閱內嵌注釋）：
' Builds consecutive row numbers in a select, append, or create query
' with the option of a initial automatic reset.
' Optionally, a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query having an ID with an index):
' SELECT RowNumber(CStr([ID])) AS RowID, *
' FROM SomeTable
' WHERE (RowNumber(CStr([ID])) <> RowNumber("","",True));
'
' Usage (typical select query having an ID without an index):
' SELECT RowNumber(CStr([ID])) AS RowID, *
' FROM SomeTable
' WHERE (RowNumber("","",True)=0);
'
' Usage (with group key):
' SELECT RowNumber(CStr([ID]), CStr[GroupID])) AS RowID, *
' FROM SomeTable
' WHERE (RowNumber(CStr([ID])) <> RowNumber("","",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
' Call RowNumber(vbNullString, True)
' 2. Run query:
' INSERT INTO TempTable ( [RowID] )
' SELECT RowNumber(CStr([ID])) AS RowID, *
' FROM SomeTable;
'
' Usage (typical append query, automatic reset):
' INSERT INTO TempTable ( [RowID] )
' SELECT RowNumber(CStr([ID])) AS RowID, *
' FROM SomeTable
' WHERE (RowNumber("","",True)=0);
'
' 2020-05-29. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function RowNumber( _
ByVal Key As String, _
Optional ByVal GroupKey As String, _
Optional ByVal Reset As Boolean) _
As Long
' Uncommon character string to assemble GroupKey and Key as a compound key.
Const KeySeparator As String = "¤§¤"
' Expected error codes to accept.
Const CannotAddKey As Long = 457
Const CannotRemoveKey As Long = 5
Static Keys As New Collection
Static GroupKeys As New Collection
Dim Count As Long
Dim CompoundKey As String
On Error GoTo Err_RowNumber
If Reset = True Then
' Erase the collection of keys and group key counts.
Set Keys = Nothing
Set GroupKeys = Nothing
Else
' Create a compound key to uniquely identify GroupKey and its Key.
' Note: If GroupKey is not used, only one element will be added.
CompoundKey = GroupKey & KeySeparator & Key
Count = Keys(CompoundKey)
If Count = 0 Then
' This record has not been enumerated.
'
' Will either fail if the group key is new, leaving Count as zero,
' or retrieve the count of already enumerated records with this group key.
Count = GroupKeys(GroupKey) 1
If Count > 0 Then
' The group key has been recorded.
' Remove it to allow it to be recreated holding the new count.
GroupKeys.Remove (GroupKey)
Else
' This record is the first having this group key.
' Thus, the count is 1.
Count = 1
End If
' (Re)create the group key item with the value of the count of keys.
GroupKeys.Add Count, GroupKey
End If
' Add the key and its enumeration.
' This will be:
' Using no group key: Relative to the full recordset.
' Using a group key: Relative to the group key.
' Will fail if the key already has been created.
Keys.Add Count, CompoundKey
End If
' Return the key value as this is the row counter.
RowNumber = Count
Exit_RowNumber:
Exit Function
Err_RowNumber:
Select Case Err
Case CannotAddKey
' Key is present, thus cannot be added again.
Resume Next
Case CannotRemoveKey
' GroupKey is not present, thus cannot be removed.
Resume Next
Case Else
' Some other error. Ignore.
Resume Exit_RowNumber
End Select
End Function
完整的故事可以在我的專案VBA.RowNumbers 中找到。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/329189.html