changing query to show null values

[phillsheen]

Hi,
This query will allow me to view payments that are made between 2 dates. I would like to know how to flip the query around so that it gives me the payments that have not been made. I think this would be described as returning the null values?

The SQL code i have at the momnet is:

SELECT download20060602.Date, download20060602.Description, download20060602.Amount, Members.FirstName, Members.[Mid Name], Members.Surname, Members.[Memb No]
FROM Members LEFT JOIN download20060602 ON Members.description = download20060602.Description
WHERE (((download20060602.Date) Between [Enter Start Date] And [Enter End Date]));

Any help or ideas would be fantastic.

Cheers
Phill

 

[workmad3]

well, assuming that download20060602.Date is the date the payment is made, and this is null when a payment hasn't been made, then

SELECT download20060602.Date, download20060602.Description, download20060602.Amount, Members.FirstName, Members.[Mid Name], Members.Surname, Members.[Memb No]
FROM Members LEFT JOIN download20060602 ON Members.description = download20060602.Description
WHERE download20060602.Date IS NULL;

should do the job. If you have a different way of distignuishing payments that haven't yet been made, then please inform us

 

[phillsheen]

Thanks for the reply.
The query you wrote is kinda what I want but I still need to be able to put the dates in myself rather than just bringing up everything. I'll try to explane, I want the query to show me the payments that have not been made (so null date) between a specific date. So the client may have made a payment last month but not this month. Or another client may have missed three payments but I only want to show the payments for the last month.

I hiope this makes more sense?

Cheers for the reply.
Phill

 

[neileg]

Create a query based on download20060602 and specify your date range. Then create a second query and add in the Members table and the query you just made. Join these with a left join on description. This will return all members and any payments made during the date range you specify. Use the criteria Is Null on the date to get all members who have not made a payment during that date range.

By the way, Date is a reserved word in Access and will cause problems if used as an object name. Change the field to DatePaid or something else. Embedded spaces or punctuation in object names can cause problems, too.