Access DLookup多条件判断及AllowPurge函数逻辑修正技术问询

Got it, let's break this down. First, let's restate your requirement clearly to make sure we're on the same page:

Allow purge only if all non-E type orders are in 'O' (unprocessed). If even one non-E order is in 'S' (shipped), we need to block the purge.

What's Off with the Original Code? #

The original function is checking the wrong thing entirely—it's counting shipped E-type orders, but your rule is about shipped non-E orders. Plus, there's a syntax error in the DLookup call (the AND OrderType='E' was incorrectly placed outside the criteria string).

Corrected Function #

Here's the revised VBA code that does exactly what you need:

Private Function AllowPurge(FileID As String) As Boolean
    Dim shippedNonECount As Integer
    
    ' Count how many non-E orders for this FileID are already shipped (Status='S')
    shippedNonECount = DLookup("COUNT(*)", "OrderMaster", _
        "FileID=" & FileID & " AND OrderType<>'E' AND OrderStatus='S'")
    
    ' Allow purge ONLY if there are NO shipped non-E orders
    AllowPurge = (shippedNonECount = 0)
End Function

Let's Walk Through the Changes #

1. Fixed the Criteria: We now target OrderType<>'E' (all orders that aren't type E) where OrderStatus='S' (shipped). This count tells us exactly how many orders would block the purge.
2. Simplified the Logic: Instead of a clunky If/Else block, we directly set AllowPurge to whether the count is zero. This makes the code shorter and easier to understand at a glance.
3. Fixed Syntax Error: The original DLookup had the OrderType='E' part outside the criteria parameter—we moved it into the proper string and flipped it to <>'E' to match our needs.

Quick Edge Case Note #

If there are no non-E orders for the given FileID, the count will be zero, so the function returns True (allow purge). That's correct because all non-E orders (which are none) are in the unprocessed state.

One small thing to check: If FileID is a string value (not numeric), you'll need to wrap it in single quotes in the criteria: "FileID='" & FileID & "' AND OrderType<>'E' AND OrderStatus='S'"

内容的提问来源于stack exchange，提问作者JJ D