Date filter

[Guest]

I am having trouble with a query showing all results. I have a field in my
query "Paid Date" and under criteria I have put "is Null" so that I can find
all records that have not been paid. When I run the query it still shows all
records with and without data in the "Paid Date" column. It is almost as if
it ignores my criteria. Am I doing something wrong?

 

[Guest]

Did you actually put "is Null" in the criteria field? Try Null (without the
double quotes) and see if that helps.

hth

 

[Guest]

Tried it, still not working.

Did you actually put "is Null" in the criteria field? Try Null (without the
double quotes) and see if that helps.

hth

 

[John Spencer]

Could you post the entire SQL statement? (VIEW: SQL on the menu)?

Also, what is the field type of your Paid Date Field?
Is it a date field (in which case Is Null should always work)?
Is it a text field (in which case there is the possibility that the field is
not null but is a zero-length string)?
Is it a boolean (yes/no) field (in which case the field cannot be null, but
must be True(-1) or False(0))?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

SELECT Leases.[Lease Id], Leases.[Status Code], Leases.[Prospect Id],
Leases.[Received Date], Leases.[Payment Due Date], Leases.Bonus, Leases.[Net
Acres], Leases.Subtotal, Owners.[Lessors Name], Owners.Address, Owners.City,
Owners.[Lessors State], Owners.ZIP, Tracts.Section, Tracts.Township,
Tracts.Range, [Prospect ID's].Prospect, [Payment History].[Paid Date],
[Payment History].[Client Pd Date], [Payment History].[Client Check #],
[Payment History].[Client Paid], [Payment History].[Collection #], [Payment
History].[Check #] INTO asdf
FROM ([Prospect ID's] INNER JOIN (Tracts INNER JOIN (Owners INNER JOIN
Leases ON Owners.[Owner Id] = Leases.[Lessor Id]) ON Tracts.[Tract Id] =
Leases.[Tract Id]) ON [Prospect ID's].[Prospect ID] = Leases.[Prospect Id])
INNER JOIN [Payment History] ON Leases.[Lease Id] = [Payment History].[Lease
ID]
WHERE (((Leases.[Status Code])="rece") AND (([Payment History].[Paid Date])
Is Null)) OR (((Leases.[Status Code])="assi"));


It is set as a date field, but if there are spaces would that create a non
null value?

 

[John Spencer]

The problem is your where clause. You are only limiting status code =
rece to the paid date being null

If you remove the extra (bless you Access) parentheses this gets down to.
WHERE (Leases.[Status Code]="rece"
AND [Payment History].[Paid Date] is Null)
OR Leases.[Status Code]="assi"

What you want is
WHERE (Leases.[Status Code]="rece"
OR Leases.[Status Code]="assi")
AND [Payment History].[Paid Date] is Null

If you are using the query grid to build this, put the IS NULL under
Paid Date on both lines of the criteria.

Access will probably make that read
WHERE (Leases.[Status Code]="rece" AND
[Payment History].[Paid Date] is Null)
OR (Leases.[Status Code]="assi" AND
[Payment History].[Paid Date] is Null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

SELECT Leases.[Lease Id], Leases.[Status Code], Leases.[Prospect Id],
Leases.[Received Date], Leases.[Payment Due Date], Leases.Bonus, Leases.[Net
Acres], Leases.Subtotal, Owners.[Lessors Name], Owners.Address, Owners.City,
Owners.[Lessors State], Owners.ZIP, Tracts.Section, Tracts.Township,
Tracts.Range, [Prospect ID's].Prospect, [Payment History].[Paid Date],
[Payment History].[Client Pd Date], [Payment History].[Client Check #],
[Payment History].[Client Paid], [Payment History].[Collection #], [Payment
History].[Check #] INTO asdf
FROM ([Prospect ID's] INNER JOIN (Tracts INNER JOIN (Owners INNER JOIN
Leases ON Owners.[Owner Id] = Leases.[Lessor Id]) ON Tracts.[Tract Id] =
Leases.[Tract Id]) ON [Prospect ID's].[Prospect ID] = Leases.[Prospect Id])
INNER JOIN [Payment History] ON Leases.[Lease Id] = [Payment History].[Lease
ID]
WHERE (((Leases.[Status Code])="rece") AND (([Payment History].[Paid Date])
Is Null)) OR (((Leases.[Status Code])="assi"));


It is set as a date field, but if there are spaces would that create a non
null value?

Could you post the entire SQL statement? (VIEW: SQL on the menu)?

Also, what is the field type of your Paid Date Field?
Is it a date field (in which case Is Null should always work)?
Is it a text field (in which case there is the possibility that the field is
not null but is a zero-length string)?
Is it a boolean (yes/no) field (in which case the field cannot be null, but
must be True(-1) or False(0))?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

You are truly amazing... I have been racking my brain over this for like two
days. Thanks!

The problem is your where clause. You are only limiting status code =
rece to the paid date being null

If you remove the extra (bless you Access) parentheses this gets down to.
WHERE (Leases.[Status Code]="rece"
AND [Payment History].[Paid Date] is Null)
OR Leases.[Status Code]="assi"

What you want is
WHERE (Leases.[Status Code]="rece"
OR Leases.[Status Code]="assi")
AND [Payment History].[Paid Date] is Null

If you are using the query grid to build this, put the IS NULL under
Paid Date on both lines of the criteria.

Access will probably make that read
WHERE (Leases.[Status Code]="rece" AND
[Payment History].[Paid Date] is Null)
OR (Leases.[Status Code]="assi" AND
[Payment History].[Paid Date] is Null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

SELECT Leases.[Lease Id], Leases.[Status Code], Leases.[Prospect Id],
Leases.[Received Date], Leases.[Payment Due Date], Leases.Bonus, Leases.[Net
Acres], Leases.Subtotal, Owners.[Lessors Name], Owners.Address, Owners.City,
Owners.[Lessors State], Owners.ZIP, Tracts.Section, Tracts.Township,
Tracts.Range, [Prospect ID's].Prospect, [Payment History].[Paid Date],
[Payment History].[Client Pd Date], [Payment History].[Client Check #],
[Payment History].[Client Paid], [Payment History].[Collection #], [Payment
History].[Check #] INTO asdf
FROM ([Prospect ID's] INNER JOIN (Tracts INNER JOIN (Owners INNER JOIN
Leases ON Owners.[Owner Id] = Leases.[Lessor Id]) ON Tracts.[Tract Id] =
Leases.[Tract Id]) ON [Prospect ID's].[Prospect ID] = Leases.[Prospect Id])
INNER JOIN [Payment History] ON Leases.[Lease Id] = [Payment History].[Lease
ID]
WHERE (((Leases.[Status Code])="rece") AND (([Payment History].[Paid Date])
Is Null)) OR (((Leases.[Status Code])="assi"));


It is set as a date field, but if there are spaces would that create a non
null value?

Could you post the entire SQL statement? (VIEW: SQL on the menu)?

Also, what is the field type of your Paid Date Field?
Is it a date field (in which case Is Null should always work)?
Is it a text field (in which case there is the possibility that the field is
not null but is a zero-length string)?
Is it a boolean (yes/no) field (in which case the field cannot be null, but
must be True(-1) or False(0))?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I am having trouble with a query showing all results. I have a field in my
query "Paid Date" and under criteria I have put "is Null" so that I can
find
all records that have not been paid. When I run the query it still shows
all
records with and without data in the "Paid Date" column. It is almost as
if
it ignores my criteria. Am I doing something wrong?