Solved Can I use Dlookup/DMIN lookups within VBA where part of the criteria is looking for a 'null' value in a field?

[Snuggle Walrus]

Hey everyone,

I'm trying to set up a lookup on a table that logs some serial numbers for a little side project of mine. I log the serial numbers of items into a table called tbl_serialnumbers. However, I've been implementing the option of allowing Part Numbers to be used when a serial doesn't exist. I realise this may seem odd... but obviously some scenarios have unique values in my Serial number field and others would have potential duplicate data as multiple MPN fields can exist.

In scenarios when I'm working with unique values my Dlookup looks like...

SerialIDScanSearch = DLookup("[SerialID]", "[tbl_SerialNumbers]", "[Serial Number]=  '" & SerialScan & "'")

Here SerialIDScanSearch is my variable for the primary Key (unique value in the table) and my lookup is searching for Serial ID (unique value) from within the serial numbers table where the Serial Number field matches the scanned serial number in my text box. This has been working great! However, for the next part of my project, I'm trying to find a way of having it pull a SerialID (unique value) for an item that may have been booked in with an MPN. Appreciate this is tricky so the easiest way is doing a first in first out so the 'earliest' scan of this product that's currently not in use. For this, I was planning on adding to the criteria for OrderID = Null but I admit I'm unsure of the correct syntax for this or if this even works, I imagine I'd be changing the DLookup to a DMin? where I'm looking for the 'smallest' SerialID that matches the Scan but also doesn't currently have an orderID against it (this would still be a Null value within the table it holds no data until this point when I then run an update query via currentDb.execute.

I know there are a few ways within access to work with Null values but I'm unsure which applies when using this type of formula, all my coding knowledge is from self-taught trial and error and the pleasure of browsing this forum (thanks to everyone you've been an inspiration for nerdy little projects for the last 3 years). I believe something like the following could be used.

SerialIDScanSearch = DMin("[SerialID]", "[tbl_SerialNumbers]", "[Serial Number]=" & SerialID & " AND [OrderID]= Null)

SerialIDScanSearch = DMin("[SerialID]", "[tbl_SerialNumbers]", "[Serial Number]=" & SerialID & " AND IsNull([OrderID]))

So these are my current two ideas for the lookup to try and return the smallest unique serial ID that still matches the part number scan BUT doesn't currently have an order ID assigned to it. I hope this all makes sense with what I'm trying to do. Thanks have a great day! Happy to answer any questions if I've not explained it well enough.

 

[June7]

Did you try them?

Cannot compare anything to null, not even null, because there is nothing to compare. Use IsNull() function.

SerialIDScanSearch = DMin("[SerialID]", "[tbl_SerialNumbers]", "[Serial Number]=" & SerialID & " AND IsNull([OrderID])")

If Serial Number is a text data type, need apostrophe delimiters.

SerialIDScanSearch = DMin("[SerialID]", "[tbl_SerialNumbers]", "[Serial Number]='" & SerialID & "' AND IsNull([OrderID])")

If you already have SerialID, why would you do lookup? Why would Serial Number = SerialID? Should that be SerialScan instead?

Strongly advise not to use spaces in naming convention.

 

[Josef P.]

Tip: create an Access query and enter Null in the filter line of OrderID. Then switch to the SQL view and look at the Where statement. You can also use this expression for DLookup & Co.

... " AND OrderID Is Null"

 

[Snuggle Walrus]

Did you try them?

Cannot compare anything to null, not even null, because there is nothing to compare. Use IsNull() function.

SerialIDScanSearch = DMin("[SerialID]", "[tbl_SerialNumbers]", "[Serial Number]=" & SerialID & " AND IsNull([OrderID])")

If Serial Number is a text data type, need apostrophe delimiters.

SerialIDScanSearch = DMin("[SerialID]", "[tbl_SerialNumbers]", "[Serial Number]='" & SerialID & "' AND IsNull([OrderID])")

If you already have SerialID, why would you do lookup? Why would Serial Number = SerialID? Should that be SerialScan instead?

Strongly advise not to use spaces in naming convention.

Thankyou for such a quick reply, Ah yes sorry I derped on my initial bottom code send it was indeed meant to be serialscan my apologies. Worked a treat! Very happy with that run some msgbox(SerialIDScanSearch) tests just under it to see if it pulled in the correct ID each time and passed 10/10 tries! Dead chuffed!

 

[Zidar]

Expression
<SerialIDScanSearch = DMin("[SerialID]", "[tbl_SerialNumbers]", "[Serial Number]=" & SerialID & " AND IsNull([OrderID])")/>
may not work correctly, when OrderID is number.

IsNull(argument) returns "TRUE" when argument is not null. IsNull returns NULL.

When used in DLOOKUP we get this:
1) not null parameter passed, existing OrderNumber
? dlookup("Status","Table1", " OredrNumber = " & (5))
"Data in DB_Staging" --- correct answer, for OrderNumber=5 Status is indeed "Data in DB_Staging"

2) not null parameter passed , OrderNumber does not exist:
? dlookup("Status","Table1", " OredrNumber = " & (-5))
Null

 

[June7]

IsNull() always returns True or False.

Domain aggregate functions return a value if match found or Null if not, except for DCount() which returns 0 if no match found.

If Null return is possibility, handle it with Nz() function.

If SerialIDScanSearch is a string variable: = Nz(DMin(...), "")

 

[Zidar]

IsNull is not going to help. here are some examples of how Dlookup behaves for various parameters:

SerialIDScanSearch = DLookup("[SerialID]", "[tbl_SerialNumbers]", "[Serial Number]= '" & SerialScan & "'")

This may be long answer to prove that IsNull or NZ are not good solutions. Perhaps better way be to ensure NULL cannot be passed to DLOOKUP, or write little code (see the end of the message)

With my table, this happens:

? Dlookup ("Status","Table1","Sequence = " & 2)​

Data in DW on Prod = correct answer, parameter is OK​

​

? Dlookup ("Status","Table1","Sequence = " & NULL) 'parameter = NULL​

Run-time error 3075: Syntax Error (Missing operator in query expression "Sequence = ..")​

​

? Dlookup ("Status","Table1","Sequence = " & IsNull(NULL)) 'apply IsNull to NULL parameter​

"SSIS Pkg run" -- coincidently this i the first record when order by PK,, not correct​

​

? "Sequence = " & IsNull(NULL)​

Sequence = True​

​

? Dlookup ("Status","Table1","Sequence = TRUE")​

SSIS Pkg run​

​

? Dlookup ("Status","Table1","Sequence = ""London"" ") 'wrong parameter data type​

Run-time error 3464: Data type missmatch in criteria expression.​

​

? Dlookup ("Status","Table1","Sequence = London ") 'stil wrong data type, with no quotes​

Run-time error 2471 "The expression you entered as a query parameter produced this error 'London'​

If isNull does not work, we can try NZ, like this:
? nz(NULL,"Instead of NULL")
Instead of NULL
? nz ("Not null anyways","Instead of NULL")
Not null anyways

That also would not work because criterion string "sequence = " & NZ(parameter, "Instead of NULL") is "Sequence = Instead of NULL"

a=NULL
? a
Null
? Dlookup ("Status","Table1","Sequence = " & nz(a,"Instead of NULL"))
Error 3075: Syntax error in query expression 'Sequence = Instead of NULL')

Therefore. modifying lookup condition 'in situ' does not work. What you really wanted could be this:

Sub Use_Dlookup (varParameter as variant)
DIM strResult as string
DIM varParameterLookup  as variant 'allows NULLs
varParameterLookup  = (Dlookup ("Status","Table1","Sequence = " & varParameter )
IF   NOT Isnull (varParameterLookup  )  THEN
       strResult  =  varParameterLookup 
ELSE
        strResult  = "Status not found for "Sequence = " & varParameter"
        MsgBox  strResult 
END IF
Use_Dlookup = varParameterLookup 
END SUb

I hope this helped.

Zidar

 

[Josef P.]

What do you want as a result?

Are you looking for this?
? Dlookup ("Status","Table1","Sequence Is Null")

dim a as Variant
...
x  = Dlookup("Status","Table1",BuildCriteria("Sequence", dbLong, Nz(a, "NULL")))

 

[June7]

@Zidar:

OP wants to consider only records where field is null. Can use IsNull() function or IS NULL to enforce that.

Of course using = sign for Null criteria will not work. I already advised that.

IsNull([OrderID]) returns True or False. OrderID IS NULL does the same. No = sign. If True then consider record, if False then ignore record. Why would you say that won't work? OP already said they are now getting correct output.