Re: if table is null then incluse all record else only in table ...hel


"Gary Walter" wrote in error:

"Silvio"wrote:
In short, if tbl2 have some value in it, retrieve only the matching
records
in tbl1 otherwise, if tbl2 is empty then retrieve all the records from
tb1.
This is what I have done, but if I live the control empty from the form,
no
records are retrieved at all, ignoring the "is null". Please help. In
short,
if tbl2 have some value in it, retrieve only the matching records in tbl1
otherwise, if tbl2 is empty then retrieve all the records from tb1.


SELECT tblData.City
FROM tblData
WHERE (((tblData.City) In (SELECT city FROM tblRepCity) Or (tblData.City)
Is
Null));

untested:

SELECT
tbl1.City
FROM
tbl1
INNER JOIN
tbl2
ON
tbl1.City = tbl2.City
OR
DCount("*","tbl2") =0;
on further thought:

SELECT tblData.City
FROM tblData
WHERE
tblData.City In (SELECT city FROM tblRepCity)
Or
DCount("*","tblRepCity") = 0;


.

Relevant Pages

  • Re: if table is null then incluse all record else only in table ...hel
    ... This is what I have done, but if I live the control empty from the form, ... if tbl2 have some value in it, retrieve only the matching records in tbl1 ...
    (microsoft.public.access.queries)
  • Re: if table is null then incluse all record else only in table ..
    ... "Gary Walter" wrote in error: ... This is what I have done, but if I live the control empty from the form, ... if tbl2 have some value in it, retrieve only the matching records in tbl1 ...
    (microsoft.public.access.queries)
  • Re: Foreign Key problem
    ... If you want to create a record in tbl2 as soon as a new record is entered in tbl1, use the AfterInsert event procedure of the *form* where the data is added. ... updated with a primary key and matching foreign key on insert. ...
    (microsoft.public.access.formscoding)
  • Re: Monthly Total
    ... Can we assume there is relationship between tbl1 and tbl2 based on the ID, ... Accept Group By in the Total row under this field. ... ID1 Date Error ...
    (microsoft.public.access.queries)
  • Re: linking two forms (2 cont)
    ... Andrey, ... from tbl1 to tbl2 using Tools/Relationship. ... That way, whenever you create a new record in tbl1, a corresponding ... Candia Computer Consulting - Candia NH ...
    (microsoft.public.access.forms)