Need query to return all values including Nulls

[Randy]

Access 2000. I need a query to return results even if some of the fields are
null (Blank) Some fields are Currency, some are dates, some are numbers
etc. I have tried in the criteria: Is Null Or Not Is Null, but have no
results if lets say the fee is 0.00. Any ideas? Oh here is my sql view if
it help...Thanks..Randy

SELECT DISTINCTROW Account.BatchDate, Sum(Account.Debit) AS SumOfDebit,
District.Dname, Count(Account.Cert_ID) AS CountOfCert_ID,
Account.District_ID, Accountsubform.FeeTypeCode, Accountsubform.Fee
FROM (Account INNER JOIN District ON Account.District_ID = District.Dist_ID)
INNER JOIN Accountsubform ON (Account.District_ID =
Accountsubform.District_ID) AND (Account.AccountID =
Accountsubform.AccountID)
GROUP BY Account.BatchDate, District.Dname, Account.District_ID,
Accountsubform.FeeTypeCode, Accountsubform.Fee;

 

[Allen Browne]

You need to use outer joins between the tables.

1. In the upper pane of the query design window, double-click the line
joining the Account table to the District table. Access offers a dialog with
3 choices. Choose #2 or #3 depending what you want (possibly all districts,
whether they have accounts or not?).

2. Repeat for the join between the other 2 tables.

This article describes the issues of outer joins and using criteria on null
fields:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

 

[Randy]

Thank you Allen..Randy

You need to use outer joins between the tables.

1. In the upper pane of the query design window, double-click the line
joining the Account table to the District table. Access offers a dialog
with 3 choices. Choose #2 or #3 depending what you want (possibly all
districts, whether they have accounts or not?).

2. Repeat for the join between the other 2 tables.

This article describes the issues of outer joins and using criteria on
null fields:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html