DLookup where Criteria is not null.

[VegaLA]

Hi all,
trying to write a DLookup wher it pulls up the first record that has a value that is not Null. The code is as below:

strValue = DLookup("[FieldName]", "MyQuery", "[FieldValue]=Is Not Null")

but I am getting a syntax error message. I suspect I am coding this wrong, can anyone point me in the right direction please?

Thanks in advance,
Mitch.

 

[WayneRyan]

Mitch,

strValue = DLookup("[FieldName]", "MyQuery", "IsNull([FieldValue])")

But, if there are multiple Nulls, you will get ONE random [FieldName]

Also, DLookUp can RETURN a Null value and it will error when that happens.
Use a variant to receive the value, or use the Nz function.

Wayne

 

[VegaLA]

Thanks Wayne.

I'm actually testing to see if the value is not null. Is that possible with DLookup ?

Doh!! got it, its <> Null.

Thanks again.

 

[WayneRyan]

Mitch,

In the following:

strValue = DLookup("[FieldName]", "MyQuery", "IsNull([FieldValue])")

If there are no records where [FieldValue] is Null ... then the DLookUp will
return a Null value ... and it will throw an error when it tries to assign that
to your "strValue" string variable.

You either need to define:

Dim strValue As Variant

or use something like:

strValue = Nz(DLookup("[FieldName]", "MyQuery", "IsNull([FieldValue])")), "No Value")

Wayne