Pulling out non payments in a date range

[phillsheen]

Hi everyone,
I have a very confusing problem that im not sure where to start with.

I have table that contains members payments. This table is updated once or twice a month with all the new payments that we recieve from the bank. This table contains the date of the payment 'datepaid'.
I have another table which contains the members bank details. On this table I have a field 'paymentday'. This field contains the day of the month which we would expect their payment e.g. 4th or 10th of any month. The Data Type for this is currently number. This may need to be a date type but only to display the day?

What I would like is a query that when run would ask me for the month, for example June, it would then look at the 'paymentday' on the members bank details to find the date in which we should find a payment for that member. The query would then return a table of payments not found with in 5 days (5 days before the date or after the date) of when we would expect the payment.

So for example in June I should have recieved a payment from Joe Bloggs on the 15th of the month. Once the query has run it finds no payment for him between the 10th to the 20th of june. So Joe Bloogs would be in the table.

I would really appreciate any help or guidance on this one as my heads thumping!

Cheers Phill

 

[wazz]

so, ur getting the date from the bank entry?

"This table is updated once or twice a month with all the new payments that we recieve from the bank."

sounds like you're getting paid by the bank. are these payments "from the bank" from the members?

sound like you need this?:

dateofpayment(got from the bank) is not > paymentday + 5

do you need to check on members who have already paid or just not paid. or both?

 

[phillsheen]

Thanks for the reply!

To clarify the points you mentioned, we download a statement from the bank containing all the payments from our members which is then used to update the table 'bankpayments'. This table is set out:
Datepaid/Description/Amount

Datepaid being the actual date we received the payment. This table contains all payments so one member can have hundreds of payments in this one table with different dates.

The second table contains the members payment details called 'paymentdetails'. This table contains the date that we would expect payment 'paymentday'.

I would like both poeple who have not paid in this period and who have paid to be displayed but in seperate querys.

Thanks for your help, I'll try out your suggestion right now!

 

[wazz]

hi. the "formula" above was just a starting point to check on what you were asking. have you made any headway?
- is there a memberid in 'bankpayments'??
- you say you want to select a month but you will need a year won't you?
- do you need everyone who has paid: outside the 5 days, inside or both?
- do i get a raise?

 

[phillsheen]

Good morning!
in answer to your questions yes there is an ID in the bankpayments. Missed that one out
Yes well spotted I will also need a year. There are about 3 years worth of payments currently in the database.
I would like the query to show everyone who has not paid with in the 5 days (so thats people whoes payment is late) of there payment date.

Yes you can have a raise! One extra pat on the back per day for you!

Ive not had chance to play around with the bit of formula yet but Ive all morning to play around with it so I'll have a go!
Thanks for the reply!

Phill

 

[wazz]

hi. i've made some queries in the attached db that i hope will help. double-click the queries to be prompted for a month and year.

the +- 5 days is more complicated than i thought but maybe the queries will be enough. the problem is, if you're expecting a payment from someone on the 3rd then the date calculation will have to go backward: 3,2,1,31,29 (depending on the month). it's very doable but i can't do that right now. (if it was a date and not just a number there would be no problem at all. i suppose you could convert the number you have into a date). i did see a website somewhere that has all the date calculations you could ever possibly want and i think that type of query is on it (traversing months). search for date threads here for the link (possibly a post by ruralguy?) if you have to go that route. (still might have the problem of working with a number and not a date).

hth.

edit: here's the link, i think. looks like the website's been revamped: http://www.pacificdb.com.au/Support/code_datetime.htm
edit: the links to code on the page above aren't active yet...

 

[phillsheen]

Wazz, thank you for the help! The queries you sent me all worked after a little playing around over the weekend to fit my tables how ever there is still a little problem.
The third query even though it does work does not do what I need it to do. At the moment it gives me a list of who HAS paid but has paid late.

Let me explain why I need this query and everything should be a bit clearer. Currently every month we do a list of non payers based on who has not paid between the first and last day of the month (much like query2) This works fine but many mistakes are made because the banks are some times early or late making payments. So for example someone who is expected to be paying on the first of the month may actually pay on the last day of the previous month in which case they would be brought up as a non payer.

This is why we thought it might be a better idea to rather than search for people in a simple monthly search we could look for there payment with in +-5 days of when we would expect there payment to be sent in 'paymentday'.

So for example if Joe Bloggs was to make a payment on the 1st of june he would have under his payment details his unique 'bankdescription' which we get from the bank, lets say 'BloggsJ2008' and the 'paymentdate' which would be 1. For this example lets say the payment comes in a few days early and we recieve it on the 30th of may. On our old query this would have been seen as a non payer. On the new query it would look at the payment date being 1 then look 5 days before and after this date, so 28th may - 5th june. It would find the payment on 30th may and therefore not be displayed in the results.

I hope this makes things clearer, or not as the case maybe??

I think what i need to do is rather than have Qry1 and Qry2 have a query that does not specifically look at the month but looks at the date instead??

I hope you can help me a little more with this one as my heads confused just writing this discription

Cheers
Phill

 

[wazz]

hi, i lost track of this thread. your explanation makes sense, but as i said,

the problem is, if you're expecting a payment from someone on the 3rd then the date calculation will have to go backward: 3,2,1,31,29 (depending on the month).

that's a bit of a job there.

I think what i need to do is rather than have Qry1 and Qry2 have a query that does not specifically look at the month but looks at the date instead??

that would be a lot easier. if you can convert the payment day to a date instead of a number then the date calculation would be easy (er).

on the other hand, if you're looking at +-5 days, that seems arbitrary and could still lead to errors. i thought the 5-day-leeway was important but it doesn't seem so now. how about simply counting the number of months (total months) that a person should have paid from the beginning of time, then count the number of payments they've made and see if they are equal?

 

[wazz]

was just playing around trying to get a date from a number and it wasn't that hard actually. you can feed this function a number and get a date.

Public Function fCreateDate(intPaymentDay As Integer) As Date

    'gives you  'month/day/year'  format. check that the day is between 1 and 31.
    fCreateDate = Month(Date) & "/" & intPaymentDay & "/" & Year(Date)
    
End Function

to see it work you just need a command button and a textbox and the code for the command button is:

Me.txtYourTextbox = fCreateDate(1) - 5