[AccessD] listbox with rowsource type (ala MS)

Hi Henry

"Where False" does per definition retrieve zero records.

In many cases it is very useful to ensure a form is opening with zero
records and for a one-table or simple query lookup it has proved fast
and reliable to me.

However, with multiplying tables (no joins) resulting in large
recordsets this method - as you showed - may be too slow. Thus my
suggestion for using "Where PK Is Null" on each table as the SQL
parser in this case seems to be able to figure that out to result in
zero records.

I would recommend a more relaxed attitude; as always (1) no tool is
perfect for every job and (2) you must know how to handle it.

/gustav


> Gustav:

> One of the things I was trying to say was that using a field list row source 
> does not submit the query SQL to the BE for retreival so there is no 
> difference whether or not a where clause is present.  Another point is that 
> it is only necessary to add an impossible restraint to a single table if the 
> SQL is submitted because Jet should process that index first and finding 
> nothing to join on, disregard the other table indexes.  I'm not satisfied 
> that using three 'Where Null' restraints wouldn't cause all three indexes to 
> be pulled before the FE started processing the joing.

> My main point was that I have seen the 'Where False' construct advocated 
> several times in the past on this list and that had led me to believe that 
> someone here had at some time read or determined that the FE SQL parser had 
> enough intelligence to determine that it was unnecessary to submit the 
> request for data to a BE file when this condition was encountered.  
> Otherwise, how does it make sense?  If there are records with 0 in some 
> field, will  it return those?  If there are yes/no fields with false 
> records, would it return those?  Apparently not, so what does it actually do 
> with this magical condition?  Let's just put an end to promoting 'Where 
> False' as a means to retreiving a limited recordset.

> Hen

>>From: Gustav Brock <gustav at cactus.dk>
>>Reply-To: Access Developers discussion and problem 
>>solving<accessd at databaseadvisors.com>
>>To: Access Developers discussion and problem 
>>solving<accessd at databaseadvisors.com>
>>CC: Sad Der <accessd666 at yahoo.com>
>>Subject: Re: [AccessD] listbox with rowsource type (ala MS)
>>Date: Mon, 15 Sep 2003 09:17:46 +0200
>>
>>Hi Sander
>>
>>Henry is right (Subject: Where False, was listbox with rowsource
>>type).
>>I did a test but with small tables only.
>>
>>However, assuming each table has a normal autonumber primary key,
>>modify the query like this:
>>
>>   SELECT
>>     tblOne.*,
>>     tblTwo.*,
>>     tblThree.*
>>   FROM
>>     tblOne,
>>     tblTwo,
>>     tblThree
>>   WHERE
>>     tblOne.ID Is Null And
>>     tblTwo.ID Is Null And
>>     tblThree.ID Is Null;
>>
>>That should reveal the field names in a split second.