Value List dependent on another field.

[Ted Giles]

Another silly question, but I have tried several options and not managed to do this;

I need a Value List which only contains the Distinct Company where a Date field in the same table is not null

Value List based on a Company
Select Distinct
Company
From
Table One

This works OK

Value List based on a Company AND where data exists in another field is my problem
Select Distinct
Company
From
Table One
Where Date_in field is not null

Can't get it to work.

Any propmpts or ideas?

[john w]

Does the field 'date_in' actually holding a null value or is it simply empty. Since 'null' is a value and if its not actually in the data that perhaps be the issue.

[Ted Giles]

I have tried the options of;
Date> 01/01/1900
Isnotblank
Isnull = false

Got nowhere John.
It's obviously a syntax issue.

[john w]

try date <> ''

[Ted Giles]

Nope

[ljwilson]

try:

not(isnull(date))

...jack

[Ted Giles]

InDe1.jpg (42.34 KiB) Viewed 2551 times

That worked well, Jack.
Where I was going wrong, was assuming that the panel had inheritance for the Selection at launch.
So my panel filter/selection is where Dateout is Null. This displays properly.
In the Value List, adding Not.isnull(datein) picked up everything. I was assuming that the existing filkter was active and it isn't.
The answer seems to be to put both tests into the Value List as my image above shows.