left join and where clause trouble

[James]

how can i list all my customers joined to their orders (left join) and have
a date filter. but i still want it to show the customers that havn't placed
any orders in that date range, i want their order fields to show null. right
now it doesn't even show the customer name if they are blocked by that
filter.

my objects:

tblCustomers
tblOrders

my query:

SELECT [Customers].Name, [Orders].ShowDate
FROM [Customers] LEFT JOIN [Orders] ON [Customers].CustomerID =
[Orders].CustomerID
WHERE ((([Orders].OrderDate)>#1/1/2002#));

 

[Van T. Dinh]

Not sure but try:

SELECT [Customers].Name, [Orders].ShowDate
FROM [Customers] LEFT JOIN
[Orders] ON [Customers].CustomerID = [Orders].CustomerID
WHERE ((([Orders].OrderDate)>#1/1/2002#))
OR ([Orders].CustomerID Is Null);

 

[James]

nope, didn't work.

Not sure but try:

SELECT [Customers].Name, [Orders].ShowDate
FROM [Customers] LEFT JOIN
[Orders] ON [Customers].CustomerID = [Orders].CustomerID
WHERE ((([Orders].OrderDate)>#1/1/2002#))
OR ([Orders].CustomerID Is Null);


--
HTH
Van T. Dinh
MVP (Access)

how can i list all my customers joined to their orders (left join) and have
a date filter. but i still want it to show the customers that havn't placed
any orders in that date range, i want their order fields to show null. right
now it doesn't even show the customer name if they are blocked by that
filter.

my objects:

tblCustomers
tblOrders

my query:

SELECT [Customers].Name, [Orders].ShowDate
FROM [Customers] LEFT JOIN [Orders] ON [Customers].CustomerID =
[Orders].CustomerID
WHERE ((([Orders].OrderDate)>#1/1/2002#));

 

[Van T. Dinh]

"Didn't work" does give any info / details for follow up!

You need to describe what happened.

* Errors? Post error message
* Nothing returned?
* Some rows returned but not what you expected? Describe the relevant
values / characteristics of incorrect row.
* Correct rows not returned? Describe similarly.

Post a set representative sample data as well as the sample returned form
the sample data.

HTH
Van T. Dinh
MVP (Access)

 

[James]

when i added the "or is null" it returned the same results as if i didn't
add it. Meaning it only returns customers that have placed an order after
the date criteria.

 

[Van T. Dinh]

You may need to use a UNION Query like:

SELECT [Customers].Name, [Orders].ShowDate
FROM [Customers] INNER JOIN
[Orders] ON [Customers].CustomerID = [Orders].CustomerID
WHERE ((([Orders].OrderDate)>#1/1/2002#))

UNION

SELECT [Customers].Name, Null
FROM [Customers]
WHERE [Customer].CustomerID Not In
(
SELECT [Customers].Name, [Orders].ShowDate
FROM [Customers] INNER JOIN
[Orders] ON [Customers].CustomerID = [Orders].CustomerID
WHERE ((([Orders].OrderDate)>#1/1/2002#))
)

BTW, "Name" is a bad Field name. Virtually every Access object has the
Property "Name" and the Field name "Name" can create problems for you later.
Suggest you change the Field name to something else like "CustName".

 

[James]

Works Great. Thank You!

You may need to use a UNION Query like:

SELECT [Customers].Name, [Orders].ShowDate
FROM [Customers] INNER JOIN
[Orders] ON [Customers].CustomerID = [Orders].CustomerID
WHERE ((([Orders].OrderDate)>#1/1/2002#))

UNION

SELECT [Customers].Name, Null
FROM [Customers]
WHERE [Customer].CustomerID Not In
(
SELECT [Customers].Name, [Orders].ShowDate
FROM [Customers] INNER JOIN
[Orders] ON [Customers].CustomerID = [Orders].CustomerID
WHERE ((([Orders].OrderDate)>#1/1/2002#))
)

BTW, "Name" is a bad Field name. Virtually every Access object has the
Property "Name" and the Field name "Name" can create problems for you
later.
Suggest you change the Field name to something else like "CustName".



--
HTH
Van T. Dinh
MVP (Access)

when i added the "or is null" it returned the same results as if i didn't
add it. Meaning it only returns customers that have placed an order
after
the date criteria.