Query not returning the right results (using dates)

[BBK]

I have with fantastic help from John Big Booty created a query that lists all tenants that are due to pay rent for today.

Supposing my query returns 3 results that are due payment.

I check my bank records and see only 2 of these results are true.

I want to be able to run another query that will list the tenant(s) who have failed to make a payment.

I have managed to conjour up the following code ( no laughing ):

SELECT DISTINCT Query1.FirstName, Query1.LastName, tblPayment.DatePaid, Query1.DayOfPayment
FROM Query1, (tblTenant INNER JOIN tblLease ON tblTenant.TenantID = tblLease.TenantID) INNER JOIN tblPayment ON tblTenant.TenantID = tblPayment.TenantID
WHERE (((tblPayment.DatePaid)<>[Query1].[DayOfPayment]))
GROUP BY Query1.FirstName, Query1.LastName, tblPayment.DatePaid, Query1.DayOfPayment
ORDER BY Query1.DayOfPayment DESC;

But it is listing all the 3 tenants payments made so far, and not finding the one who has missed their payment JUST for todays date.

I hope i have explained my problem ok and hope someone could shed some light on yet another problem i have.

Thanks for reading.

 

[BBK]

Ok i got it working a little better with this code

SELECT DISTINCT Query1.FirstName, Query1.LastName, tblPayment.DatePaid, Query1.DayOfPayment
FROM Query1, (tblTenant INNER JOIN tblLease ON tblTenant.TenantID = tblLease.TenantID) INNER JOIN tblPayment ON tblTenant.TenantID = tblPayment.TenantID
WHERE (((tblPayment.DatePaid)=Date()))
GROUP BY Query1.FirstName, Query1.LastName, tblPayment.DatePaid, Query1.DayOfPayment
ORDER BY Query1.DayOfPayment DESC;

But, it listing all Tenants who are due payment (3 of them for todays date), even though i have entered for two of them payments for today.

So my result should only list 1 tenant who has not paid but was due to pay for todays date.

Any help greatly appreciated

 

[BBK]

After much hair loss and a long night i finally got it working

With unmatched query wizard an a addition of an extra field in my payments form