Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    LA, CA
    Posts
    54

    Question Unanswered: Setting Payment Schedule- Need some help with determining Weekdays...

    Hello all my lovely, helpful Access Fairies!!! My brain is just NOT wrapping around how to do this. I have been tasked to create payment schedules for clients who subscribe to a certain service. It works fine for a "once a month on the same day of the month" type situation, but the users are requesting it to create other options....

    Options:
    1st and last day of the month payments
    bi-weekly payments (starting on a specified work day- aka Mon-Fri)
    1st and 15th of the month payments....

    now here is the snafu... if the 1st or the 15th (or end of the month) falls on a weekend, the payment should default to the last weekday before...

    Also, on the bi-weekly... determining what is the next Thursday (for example) that the payment be taken.... i am a wee bit stumped.

    I see that there is a combo box where the user can select the type of payment plan... but the append query that creates the payment records (one row= one payment: specified by the date of the payment) is not too complicated for my feeble mind.... any suggestions?

    Thanks so much for help in advance

    Rachel
    Compensation Analyst

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    on the bi-weekly... determining what is the next Thursday (for example) that the payment be taken
    If you have recorded the date of the last payment then use the DATEADD function to add 14 days to the date last sent.

    Please attach a sample database with your input form and other relevant queries, tables etc. exclude any personal data etc.

  3. #3
    Join Date
    Nov 2003
    Location
    LA, CA
    Posts
    54
    Thank you so much Papa Smurf... in advance! I am working on getting a De-Confidentialized (I coined that!) version of the database that i can send you.... the client has now made it even MORE complicated.....

    there is a drop down with the following choices:
    BiWeekly
    Monthly
    1st and 15th
    15th and End of Month....

    there is also a drop down designating which payment plan (in months) they are following... the way it is currently set up is the MONTHLY way... only one payment, once a month.... obviously for any of the other selections, there are 2 payments per month. On months that would have 3 biweekly payments (it generally happens twice per calendar year), there will only be two payments.... the fees that are paid are broken into two.... argh.... my head hurts.

    Stand by... still working on the version you can see.... Thanks!!!

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    When you have sent a "De-Confidentialized" version of the database we can start on your requirements.

    On months that would have 3 biweekly payments (it generally happens twice per calendar year), there will only be two payments....
    If the month has three payments will there be two equal payments and the total of these payments equal three bi-weekly payments?

  5. #5
    Join Date
    Nov 2003
    Location
    LA, CA
    Posts
    54
    Yes... there can only be a maximum of 2 payments per month. In fact, the whole concept of more than one payment per month has not been implemented in the db! (it's a new request). So.... the way i went about doing it is ONLY for a monthly payment i am not exactly sure how to get started on the other pieces of it).

    Here is the confidentialized version. Thank you so much for helping me!!!

    Rachel
    Attached Files Attached Files

  6. #6
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Thank you for the download. Please contact me at the email address below with your email address as I do not want to use the forum until the we have the finished product.

    Also, what is the form that you be will using to schedule the payments?

  7. #7
    Join Date
    Nov 2003
    Location
    LA, CA
    Posts
    54
    will do... it's on the Clients form... the button to CREATE PAYMENTS

    i shall shoot you an email in just a mo'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •