Null query problem (1 Viewer)

[phillsheen]

Hi,
I have a problem with my query. I have a table which has a record of payments, the fields are
id/datepaid/reference/amount

My first query works fine. its simply to find the payments between two dates. The criteria is Between [Enter Start Date] And [Enter End Date] which is under datepaid. This query is saved as qrydatepaid.
The problem Im having is when I try to use this query in another query.
My other query is to show the dates not paid between the dates. I want to do this simply by adding qrydatepaid into a new query (qrydatenotpaid) and putting Is Null in the criteria under datepaid.
When I do this it just comes up blank.

Does any one know what Im doing wrong with this?

Cheers
Phill

 

[neileg]

?

qrydatepaid returns records with payments between two dates. It follows, therefore, that there are no records where the date paid is null. So it's not a surprise that this returns no data.

Try using Not Between as the comparator in a new query based on the table, not your query.

 

[phillsheen]

Sorry Im getting a little messed up.

The purpose of this query is to pick out poeple who have not sent a payment to us. So if I put in a date range 01/01/2007 to 31/01/2007 then the query will return to me any refernces with no payments between this date. Will the Not Between criteria do this?

Cheers
Phill

 

[CraigDolphin]

I imagine that your data structure stores records of payments made by people and stores no record if no payment is made. Assuming that this is true...

To get a list of people who have not made a payment between two dates therefore, you need a query that displays people who have made a payment between those two dates, and another table/query of all people in your database. The list you want shows all people in the second query that are not present in your first query. You can use the query wizard to find unmatched 'people' between the two lists.

If you do store a record when no payment is made, then you need to limit your query results to those records where [somefield] = 'No Payment Made' (or whatever is appropriate given your data) and use as criteria between dataA and DateB in your date field.

 

[phillsheen]

Hello again,

The list you want shows all people in the second query that are not present in your first query. You can use the query wizard to find unmatched 'people' between the two lists.

I understand what you mean here but I cant find this unmatched thing on my wizard? And Im not sure of what I should be putting on the criteria.

I'll take a step back and describe the database a little more.
Its a members database which for this part has 3 tables: Members, Paymentdetails and payments. The Members table simply holds the members details. This table is related to the paymentdetails table which hold the members bank details so every member has one set of bank details. These tables are linked through there primary key.
On the paymentdetails table there is a 'bankreference' field. This field is related to the 'reference' field on the table Payments which is updated from the bank every month. On the Paymentdetails form I have made the payments is shown as a subform so every member has one set of paymentdetails which shows many payments.
So what I want to do is when the payments is updated from the bank, for example lets say from July, I can run a the query between 01/07/07 and 31/07/07. This will then bring up any members that have not made a payment between these dates.

Phew! Thats was a bit heavy for friday morning!
Hope it makes a little more sence. Sorry if Im repeating myself in all that!

Cheers
Phill

 

[Rich]

Change your query joins between Members and payments, then use the IsNull function on payments to filter out those who have not paid

 

[phillsheen]

Change your query joins between Members and payments, then use the IsNull function on payments to filter out those who have not paid

How??

Im not sure what tables or queries I should have in this query?? If you could tell what needs to go into the query, if I need the other query I made to show the people who have paid in the month first etc it would be great.

Sorry for being crap at this!

Thanks for all the replies!

Phill

 

[Rich]

I assume you have a table with members and a separate table for payments and that both tables are linked, and the members table to the payments query

 

[neileg]

Following on from Rich's advice:

Create a select query that has your members table and your paymants table. Make sure that these tables are joined in the query with a left join (returns all the records from the members table and those from payments that match). Apply your date criteria to the payment data. On a different field from the payments table (I'd choose the PK but any would do) apply Is Null as a criterion.

 

[CraigDolphin]

To find the unmatched query wizard: In your database window click on queries. Then click on the 'New' button at the top of the db window. A dialogue should open with 5 varieties of new queries. The bottom one on the list is the unmatched query wizard. Make sure you have already created the two lists as mentioned in my previous post so you will be able to select them when prompted by the wizard.

 

[phillsheen]

hi everyone!
Ive been away the last few days so I havent had chance to reply to you. I have had a go at the suggestiopns you have posted but with no luck. I still cant get anything out of it so Ive attached the database so if you have a few minutes you can have a look and if your feeling really really nice you could maybe stick in a query for me??
The security file is in there and the username is owner and password is swissball. The VB password is water.
The file i get from the bank is under tables called download Download20060602 (previously called payments in the post for clarity). The only other tables I think are required are members and payments (called paymentdetails in the post) which have there own forms.
There are some fields I have had to delete in the members form because of privacy but this should not effect the database.

Any help would be fantastic!!
Cheers
Phill

 

[CraigDolphin]

No idea how to get the security file to work and can't open your main db, sorry. If you can post a version without security I'd be happy to look at it.

 

[neileg]

No idea how to get the security file to work and can't open your main db, sorry. If you can post a version without security I'd be happy to look at it.

Same here.

 

[phillsheen]

Sorry about that! Nothing but trouble that security!
Ive done what Mircosoft advise to remove all security and it seems to work so Ive attched the database minus the fortknox level security!

Phill

 

[ajetrumpet]

Phil,

I did not look through your entire file, but I did notice that your "payment not made between date" query runs just fine. It doesn't give you any records because you are querying a recordset (paydates) that, in turn, has a source table (download .....) with 0 NULL values in the "datepaid" field. I know you get those from the bank. Just thought I would answer your original question....

I want to do this simply by adding qrydatepaid into a new query (qrydatenotpaid) and putting Is Null in the criteria under datepaid.
When I do this it just comes up blank.

Maybe this will help you in your quest for the final result.

 

[phillsheen]

did not look through your entire file, but I did notice that your "payment not made between date" query runs just fine. It doesn't give you any records because you are querying a recordset (paydates) that, in turn, has a source table (download .....) with 0 NULL values in the "datepaid" field. I know you get those from the bank. Just thought I would answer your original question....

ooooooh I get it now! Must have been that full nights sleep that i had last night! I see now why it doesnt work since there is nothing there for the Is Null to give back. Cheers ajetrumpet.
Still no idea how to make it work though

 

[neileg]

The query [payment not between date] works fine for me. Have I lost track of your original problem?

 

[phillsheen]

I think Ive done it!! Its a miracle!!

Ok so the query I have made to show all the none payers between a date goes as follows, Ive made a query with the Payments table and the Paydates query with a join between the bank description on table and query. From there i put the Is Null criteria under Datepaid on paydates and now it seems to work perfectly. BUT I have another question
I want to filter the information down a little further. Under the description field I have some values I dont want to show on the list. For example non members are '0' and frozen members are '3'. This is writen in the bank description. I dont want these on my list so what criteria do I need to put to filter this?
Im sure this is very simple, the criteria Im sure would go under the description field, Im just not sure of the text.

Thanks for all the help and your time!

Phill

 

[neileg]

I assume you mean the membership status field. Use Not In (0,3) or alternatively = 1 if all the active members are status 1.

 

[phillsheen]

It works perfectly!
I cant believe that took so long and was so simple!
Thanks everyone, I felt a learn alot from that little problem!

Cheers
Phill