If isnull test failing for dlookup returning empty value

[rdougherty]

Hello, please help me understand if the following would be seen as a null value, zero length, etc. I am using dlookup in an if, else statement, and my empty cells seem to be passing a not null value, because the code is running that is in the else clause...

I have a table with a single row where values exist for the purpose of calling them as variables on a form, and then set to null when not needed. (I do not wish to delete the record entirely)

I need to do a logic test to use this data if it is not null, else do nothing.

If isnull(DLookup("field1", tablename) then
'Nothing
Else
Var = DLookup("field1", tablename)
End if

Even when the values being looked up by dlookup are set to null prior, this if statement is doimg the thing in the else clause...



Question is whether the dlookup is seeing a null value is still seen as "something" in this scenario because it found a row meeting its criteria (no criteria statement = return the only value in the table since there can only be one row).

See notes from microsoft:

If criteria is omitted, the DLookup function evaluates expr against the entire domain.

And...

The DLookup function returns a single field value based on the information specified in criteria. Although criteria is an optional argument, if you don't supply a value for criteria, the DLookup function returns a random value in the domain.

And finally...

If no record satisfies criteria or if domain contains no records, the DLookup function returns a Null.

That last bit seems to strongly suggest that if dlookup finds anything meeting its criteria, that it will not pass an isnull test...

So does it return a zero length here, or what?

How do I test for that so that I only run my code when there is a data value present in the dlookup field?

Edit: I just read somewhere about vbNullString and Is Nothing

Would either of those do the trick for my use case?

Thanks!

[jojowhite]

you save the result first of DLookup then test if it is null.
also you need to Enclose your table/query name to quote string (as in the example).
Note you need to declare the variable as Variant.

Dim rslt As Variant
rslt = DLookup("field1", "tablename")

If Not IsNull(rslt) Then
' you use the value of rslt here
' example
'Me!txtBox = rslt
End If

[Welshgasman]

Nothing wrong with combining IsNull and DlookUp() ?

? isnull(dlookup("dayno","tblday","dayno=32"))
True

Perhaps you have a ZLS and not Null ?

? dlookup("dayno","tblday","dayno=32") & "" = ""
True

[Gicu]

I always use the Nz() function (the default for text fields is ZLS or ""):
https://support.microsoft.com/en-us/...a-7fd9f4c69b6c

If Nz(DLookup("field1", "tblYourTable"))="" Then  'If IsNull(DLookup("field1", tablename)) then
     'Nothing
Else
     Var = DLookup("field1", "tblYourTable")
End if

Cheers,

[June7]

I don't allow ZLS in text fields. The goal is to not have to deal with possible ZLS. However, doesn't hurt to code for it. Concatenate "" or use Nz(), either will accomplish same.

I would use a variable instead of repeating the DLookup().

[Micron]

If Var is a Variant then I'd omit Else's when they're not really necessary

Var = DLookup("fieldname", "tablename") 'lookup returns Null if not found
'then some sort of examination of Var, such as
If Not IsNull(Var) Then 
    'do something, else the whole If block is bypassed.
End If

[rdougherty]

Thank you all. I will try some of your suggestions as soon as I can get more time with this database. My job is mostly afk, so dev time is severely limited!

[rdougherty]

After reading your responses more carefully, and reading up on what the nz() function is used for, I think perhaps my problem was misunderstood...

The trouble I have is not that I am getting a null value when I don't desire one, but rather that the dlookup may not be able to return a null value in my situation and then be evaluated by isnull() because the recordset it is returning is not a null set (contains 1 record of all null fields), even though the field I am asking to evaluate with isnull() is a null value. So, is the dlookup already passing a not null value in this case to the isnull() to evaluate (because it found a record), and therefore isnull() may not be the optimal way to evaluate the result of the dlookup?

I only want an "apply batch data entry defaults" button to populate a field with those defaults if the user actually entered a default value for the field as part of their entering data in a batch... if they only want to default 1 of 10 fields that all of their records will have in common, and manually enter the other 9, for instance I want to update the new record's empty fields only if the batch default was set for that field, and contains a value.

It almost seems like I need the opposite of a nz() that will force a zls to null so that it can be evaluated by isnull???

[June7]

Huh?

First of all, domain aggregate functions don't return a recordset - it returns a single value. If the function has criteria that does not find any match, it returns Null (except for DCount which will return 0). Unless you are feeding into the criteria an invalid parameter for the expression, which will error. Hard to say since you still haven't provided the DLookup() expression nor any data.

As I stated, issues with empty string are why I don't allow in table.

You need to provide sample data, desired result, and enough code so we can debug this. If you want to provide file, follow instructions at bottom of my post.

[Micron]

dlookup may not be able to return a null value

To back up June7 I already stated that if a value isn't returned by that function it defaults to Null, so that's a non-starter.