MS access payment due list

i've looked for posts on this subject but cant find anything at all so i come to you for expert help!!!

I have a micrsosft access database i have made, it lists customer accounts, they each pay on a monthly basis but all at different times depending on the date they signed up for the service,

i would like a feature that lists the clients that need to pay on todays date,

so for example if clients 'bloggs' and 'peters' signed up on the 15/01/03 and today was 15/02/03 then both 'bloggs' and 'peters' would appear in a list box or text field,

once i have confirmed that 'bloggs' has paid i can then clear the 'bloggs' entry from the list be selecting it and clicking clear (or something along those lines)

can anyone help?

thanks,

ALEX

1) Create a table "TimeDue" with fields:-
- eventid - the id for particular bill that customer has to pay.
- paidid - if the payment was paid -> value = 1
. . . . . . . if the payment was not paid -> value = 0 (default)
- datepaid - today's date. when transaction happened, insert today's date into this field.
- timepaid - Current time will be insert into this field when the payment received.
2) Write a query to count the due date from your customer account table and insert the result into table "TimeDue".
3) Update display on your "Payment Due List" form once per day.

Everytime u bring up the "Payment Due List", it will show all those payment with 'paidid=0'. You will know who is due on that day. Certainly, this will be dangerous if "TimeDue" table accidentally modify or erased.

Another way to do it :-
Add additional field "paidid", "datepaid", "timepaid" into your customer account table. Update the above 3 field whenever customer paid their balance. Write a query to pull up all the due payment to display in "Payment Due List" form.

I'm sure there're smarter solution to this problem.

i have no idea how to script something like that:

i need it to work for each client each month on the same date of that month,

i.e. started on 01/01/03, then it is listed on 01/02/03, then 01/03/03 etc...

forget the clear of paid clients i just want it to list the clients, clearing paid ones from the list is too complicated and not really needed,

i'd just like it so that it 'bloggs' appears on 01/02/03 and then dissapears from the list on 02/02/03

does that make sense???

i just cant figure out how to populate a list based on the day being the same and the month being different?

does this make sense?

SQL Site reference
1. http://www.mvps.org/access/datetime/index.html
2. http://www.exceltip.com/st/%20Calcu...ween_two_dates_
<br%20/>using_VBA_in_Microsoft_Excel<br%20/>/523.html

second URL contain 2 lines.

- - ------------------------------------------------------------ - -
The following are based on Access 2002.
On your customer account table, you might want to add a field:-
ReceiptNo. how?
1) Open your Account.mdb file
2) select particular table, and click Design button on top, or press Alt + D.
3) append a field - ReceiptNo
Date type - Number
4) Close the design form by clicking "X".

Create a query to pull out your result:-
1) Click Query , on the left panel.
2) Select "Create Query in Design View" (Double left click)
3) will see "Show Table" window pop-up. Select the CustomerTable, and click ADD, Click Close.
4) Choose all the field you want to display. (have to include the Date, and ReceiptNo field)
5) At the "Criteria" row,
Add " <Date()-30 " under Date field,
Add " =0 " under ReceiptNo field.(default value = 0, receipt number will be updated to this field once customer pay their bill)
6) Save the query.

Next, Add a forms to pull out your query.
1) Menu on your left panel - Tables, Queries, Forms, Reports.....etc
- Click on Forms.
2) select "Create form in Design view"
3) Check if you have "toolbox" panel to design your form.
- Click on "View", "Toolbar", and make sure "Toolbox" is checked.
4) Select Listbox icon and choose
"I want the list box to look up the values in a table or query", and click NEXT
5) Choose Queries (or press Alt-Q), and select Query1(the query that you've just created).
- click NEXT
6) Select all the fields from "Available Fields" on the left side and click "> > " button.
- Click NEXT
7)"How wide would you like the columns in your list box?"
- click NEXT
8)on next screen, click NEXT
9)Label your list box and click Finish.

You should have created a "Paid Customer" form. Once customer pay their bill, update the table with receipt number. and this should make your "LIST" up to date.

(Please avoid using extra exclamation mark. If you need extra service, hire a consultant to help you. Don't irritate someone who has a heart to post their help on this forum.)

i havn't had a chance too look through this yet but it looks great thanks a bunch, i really appreciate your effort and help

will check it out in the morning and see what it can do, cheers!!!

Alex

i've got it working i thnk...

but it just loads all entries in the database until the receipt number is set to something other than 0

but i just want to load the entries that have the same 'day' as the current date i.e.

one 01/02/03
two 03/02/03
three 01/04/03

if todays date is 01/06/03 'one' and 'three' would be loaded, then the next day nothing would appear in the form then on the next day the date would be the 03/06/03 and that would display 'three'

does that makes sense, you see i dont need a receipt num field just a way of only displaying table entries that share the same DAY as todays date, this way the same entries appear in the payment form on the same day each month,

i hope that makes sense, i just can't figure out how to do that.

cheers,
alex

on the second section "Create a query....", number 5...
" <Date()-30 " is the condition that make the changes to your display.
You could change the condition to " = Date()-30 " than it will appear just on the 30th day earlier.

If you want to display the results within all the last 30 days, than
" < Date () and > Date()-30 " will produce the result

In Access, if you go to the Design function of your Queries , you could choose either to Display or not (check or uncheck) to display the result.

Using the ReceiptNo field might not be necessary for you, only if your system starts to grow.


Good Luck

i think your getting the wrong end of the stick, but thankyou anyway.

it works like this... user bloggs signs up on 01/03/03 for example they have made a payment of 5 pounds, 1 month later they have to make another payment of 5 pounds, i want something to bring up the bloggs record on the first of each month as a reminder to check they have paid.

so on the first of every month their name pops up in the form list box, on the second they will have dissapered from the list and will only appear again on the first day of the next month.

if they had originally signed up on 15/03/03 then they would appear in the list on the 15th of each month only,

if i have 10 customers that all pay on the 15th they will appear on the 15th of each month and then dissapera on the 16th, the people that pay on the 16th will take their place,


does that make more sense, sorry,

Alex