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

[Guest]

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));

 

[John Vinson]

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));

Would a UNION query help? It will return all records in either table,
eliminating duplicates:

SELECT tblData.City
UNION
SELECT tblRep.City;

John W. Vinson[MVP]

 

[Guest]

Nope! The query is much more complex than what i have listed and the union
will create problem with other parts of the query. Thank you anyway.

 

[tina]

if you're doing this within a form - to populate the form with records? or
perhaps a subform or pop-up form? - then i think you'll need to set the
appropriate form's RecordSource after checking for records in tbl2, and then
requery that form. something along the lines of

If DCount(1, "tbl2") < 1 Then
Me.RecordSource = "SELECT * FROM tbl1"
Else
Me.RecordSource = "SELECT tblData.dataID, " _
& "tblData.City FROM tblData INNER JOIN " _
& "tblRepCity ON tblData.City = tblRepCity.city"
End If

Me.Requery

the above code assumes that you're setting the RecordSource property of the
form that the code is running from. if you're setting the property of
another open form, replace "Me." with "Forms!FormName.RecordSource" (without
the double quotes, of course).

hth

 

[Guest]

Nope Tina! I need to work with query. :-(

if you're doing this within a form - to populate the form with records? or
perhaps a subform or pop-up form? - then i think you'll need to set the
appropriate form's RecordSource after checking for records in tbl2, and then
requery that form. something along the lines of

If DCount(1, "tbl2") < 1 Then
Me.RecordSource = "SELECT * FROM tbl1"
Else
Me.RecordSource = "SELECT tblData.dataID, " _
& "tblData.City FROM tblData INNER JOIN " _
& "tblRepCity ON tblData.City = tblRepCity.city"
End If

Me.Requery

the above code assumes that you're setting the RecordSource property of the
form that the code is running from. if you're setting the property of
another open form, replace "Me." with "Forms!FormName.RecordSource" (without
the double quotes, of course).

hth

 

[Gary Walter]

"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;

 

[Gary Walter]

"Silvio"wrote:

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;

 

[Guest]

Gary you are my life saver. Thank you so much, it works like a charm.

Silvio
,