DLookup Problem

[Laser]

I am using.. DLookUp("[CID]","[InvoicePayments]","[CID]=forms!Invoicing![CID]") and which works fine. However I also need to add a number field which contains a NULL value from [InvoiceNo] from the same table but I cannot find the correct syntax for the lookup.. Does anyone know how to rectify?

Any help is appreciated

 

[DHookom]

I think this is what you want:
DLookUp("[CID]","[InvoicePayments]","[CID]=forms!Invoicing![CID] and [your field] is null")

It isn’t clear where you are using this expression but there might be a better expression.

 

[Laser]

I think this is what you want:
DLookUp("[CID]","[InvoicePayments]","[CID]=forms!Invoicing![CID] and [your field] is null")

It isn’t clear where you are using this expression but there might be a better expression.

Hi DHookom, Many thanks your response, unfortunately that did not work. The first part of the expression does work as it pulls in the correct details from [CID] but will not find the[InvoiceNo] that is blank - I intend to use the forms oncurrent event to respond to fire an event based on what is found. You used "is Null" at the end of the statement which didn't work so I tried using IsNull([InvoiceNo) instead but that also failed

 

[Pat Hartman]

but will not find the[InvoiceNo] that is blank

"blank" has no meaning and ZLS is NOT = null. In fact, NOTHING is ever = Null. Is the field you are checking for null a text field? If so, it probably has AllowZLS set to yes and so the field may not be null at all. It may just look like null

ALWAYS show us YOUR code. @DHookom did not give you working code. He gave you a suggestion which you had to translate into code. What did you translate Duane's suggestion to?

 

[Laser]

Hi Pat, Maybe I didn't explain myself properly so I will try again. Basically I want to check the table [InvoicePayments] for the [CID] which is a text field and also the [InvoiceNo] field which is a Number field - the ultimate goal is to find a matching [CID] to the form that I am on (This already works fine) but having problems with the correct syntax to identify a value in the [CID] field and no invoice number within the [InvoiceNo] field. So in essence the Dlookup should return a value for the [CID] Field such as "ABC" and for the [InvoiceNo] field that has nothing in it (Ie An Empty Field with no value) - This Dlookup I intend to place in an OnCurrent event of the form that will trigger if it finds a entry with a value for the [CID] field but nothing in the [Invoice] field within the table. Hope I've explained better

 

[Gasman]

Is it Null or a ZLS?
Perhaps try .....

DLookUp("[CID]","[InvoicePayments]","[CID]=forms!Invoicing![CID] & " and LEN([your field] & "") = 0)"

 

[plog]

Huh? What? Why?

DLookUp("[CID]","[InvoicePayments]","[CID]=forms!Invoicing![CID]")

Why do you need to lookup [CID] when you already have it? Are you just checking for its existence? If so a DCount would be a better function to use.

 

[Laser]

Is it Null or a ZLS?
Perhaps try .....

DLookUp("[CID]","[InvoicePayments]","[CID]=forms!Invoicing![CID] & " and LEN([your field] & "") = 0)"

Thanks Gasman but that throws up a compile error

 

[Laser]

Huh? What? Why?

DLookUp("[CID]","[InvoicePayments]","[CID]=forms!Invoicing![CID]")

Why do you need to lookup [CID] when you already have it? Are you just checking for its existence? If so a DCount would be a better function to use.

Because I want to find the matching [CID] in the [InvoicePayments] table [thats equal to the form that I am on but has no vaule in the [InvoiceNo] field

 

[Pat Hartman]

ALWAYS show us YOUR code. @DHookom did not give you working code. He gave you a suggestion which you had to translate into code. What did you translate Duane's suggestion to?

I guess my question was too subtle.

 

[plog]

The matching CID will be the only CID you are looking for. It doesn't make sense. It really sounds like you are checking for existence not trying to obtain a value. DCount would be a better function to use for this.

Suppose you input CID=2 into the form, call me a psychic witch, but you will never get back CID=3 from the Dlookup. I'll go one crazy prediction further--when you get back a result from that Dlookup it will be CID=2. But you knew that before you did the Dlookup. So why do you need that value?

Dcount is what you want.

 

[Gasman]

Thanks Gasman but that throws up a compile error

And the error is?
I always forget the double quotes protected, so try

and LEN([your field] & """")
or
and LEN([your field] & '')

with the rest of the code.

 

[Laser]

Ok people.. many thanks for your contributions. I think I was trying to do something that is impossible so I ended up playing with DCount which I managed to get working so thanks to all and especially to those suggestions of using DCount.. Cheers All

 

[Gasman]

Not impossible. Just have to get your data types correct.

 

[June7]

Most domain aggregate functions return a value if there is a match or NULL if there is not. DCount is the only one that also returns a value if there is no match. If you used DLookup, would have to test if return is NULL, like: If IsNull(DLookup(...)) Then

I NEVER allow empty string in table fields. If field were truly NULL, IsNull() or IS NULL would work. So something else was wrong with your code - which you never provided for review.

Gasman's suggestion testing for ZLS appears to have incorrect quote marks. Also, since InvoiceNo is a number type, testing for length won't work. A number field cannot hold string so testing for NULL should work.

 

[DickyP]

While we are on domain functions don't forget the niggle of DCount where zero is returned both for a result of zero, for Null (which kinda makes sense although strictly mathematically wrong), AND for any other error!

 

[plog]

which kinda makes sense although strictly mathematically wrong

How is that?

Mathematically the count of the NULL set is 0. Mathematically the count of the set with only member 0 is 1.

All sets have a non-null count

 

[Pat Hartman]

Ok people.. many thanks for your contributions. I think I was trying to do something that is impossible so I ended up playing with DCount which I managed to get working so thanks to all and especially to those suggestions of using DCount.. Cheers All

It is far easier for us to actually help you when you answer questions AND show YOUR code, rather than just saying it doesn't work.