Solved Credit card "payment due" date (1 Viewer)

Vulpeccula

New member
Local time
Today, 10:58
Joined
Sep 5, 2020
Messages
23
Hello to all you Access-Programmers,
I am appealing for help on a simple, two table credit card app: tblCreditCard and tblTransactions.
Associated forms are: frmCcList (unbound with list box lstProvider), and frmTransactions (unbound with listbox lstTransactions).
So, we double-click on a card provider and via qryTransactions, we populate lstTransactions – straightforward.
To the crux of the matter: - reports; I need to forecast the minimum payment required at statement date then predict the “payment due” date.

Problem: some providers require payment within 21 days of statement, others require payment within 28 days, for some it is PCM (per calendar month).

The challenge: I can work out a calculation for “payment due” based on the number of days interval as stated by each provider, but how do I calculate PCM ?

Any assistance would be gratefully received

Thanks in advance guys...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:58
Joined
Oct 29, 2018
Messages
21,357
Hi. Not sure what PCM exactly means, but if it means you need to know how many days in a given month, you can either create a table of months and days or calculate it using an expression.
 

isladogs

MVP / VIP
Local time
Today, 10:58
Joined
Jan 14, 2017
Messages
18,186
Surely you aren't storing credit card details in an Access app? It really isn't secure enough
 

plog

Banishment Pending
Local time
Today, 05:58
Joined
May 11, 2011
Messages
11,611
The best way to communicate data issues is with data. Please provide 2 sets of data to demonstrate your desire:

A. Starting data from your table(s). Show table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect the report to show when you feed it in the data from A.

Again, 2 sets of data--starting and expected results based on the starting data.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:58
Joined
Oct 29, 2018
Messages
21,357
Its Here:-
Hi. What I meant was is there a "special" formula used for PCM calculations, or is it just a matter of using the number of days for each month instead of simply using every 30 days?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:58
Joined
Jul 9, 2003
Messages
16,244
Surely you aren't storing credit card details in an Access app?

I Agree with Colin, not a good idea!

I'm pretty sure doing that breaks the terms and conditions of the credit card companies. If they find out, they will remove your ability to take credit card payments...
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:58
Joined
Jul 9, 2003
Messages
16,244

Vulpeccula

New member
Local time
Today, 10:58
Joined
Sep 5, 2020
Messages
23
Hello to all you Access-Programmers,
I am appealing for help on a simple, two table credit card app: tblCreditCard and tblTransactions.
Associated forms are: frmCcList (unbound with list box lstProvider), and frmTransactions (unbound with listbox lstTransactions).
So, we double-click on a card provider and via qryTransactions, we populate lstTransactions – straightforward.
To the crux of the matter: - reports; I need to forecast the minimum payment required at statement date then predict the “payment due” date.

Problem: some providers require payment within 21 days of statement, others require payment within 28 days, for some it is PCM (per calendar month).

The challenge: I can work out a calculation for “payment due” based on the number of days interval as stated by each provider, but how do I calculate PCM ?

Any assistance would be gratefully received

Thanks in advance guys...
Ok, seems I have been misunderstood - please allow me to clarify:

1. This a personal project; it breaks no GDPR regs, it breaks no T&C regs nor any laws that I am aware of.
2. As my tables contain my own, personal financial details, I most certainly will not be publishing them here!

Problem re-stated: I have a Field (fldStatementDate), which is the date any Credit card provider publishes my financial statement. I wish to create another field which calculates the "Due Date" of payment of that statement based on the number of days in the relevant providers "payment cycle", e.g. 21 days after statement, 28 days after statement, 30 days after statement. This appears relatively simple: I would create a number field to contain "cycle days" and another ("DueDate") to contain the result. But some providers use PCM (per calendar month) in their calculations, which is not a number.

The solution may lie in a query, a nested query, a vba sub-routine or a bit of both but the penny just won't drop for me.

Any help and/or constructive criticism would be welcomed

Hello to all you Access-Programmers,
I am appealing for help on a simple, two table credit card app: tblCreditCard and tblTransactions.
Associated forms are: frmCcList (unbound with list box lstProvider), and frmTransactions (unbound with listbox lstTransactions).
So, we double-click on a card provider and via qryTransactions, we populate lstTransactions – straightforward.
To the crux of the matter: - reports; I need to forecast the minimum payment required at statement date then predict the “payment due” date.

Problem: some providers require payment within 21 days of statement, others require payment within 28 days, for some it is PCM (per calendar month).

The challenge: I can work out a calculation for “payment due” based on the number of days interval as stated by each provider, but how do I calculate PCM ?

Any assistance would be gratefully received

Thanks in advance guys...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:58
Joined
Oct 29, 2018
Messages
21,357
But some providers use PCM (per calendar month) in their calculations, which is not a number.
Hi. Can you please explain this part further? It sounds like it's not as simple as "cycle days". Is there a link you can post to help us understand the requirement? Thanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:58
Joined
Feb 19, 2002
Messages
42,970
Why are you using unbound forms? Access is a RAD (Rapid Application Development) tool. The whole point of using Access is to use its RAD feature .

The Credit card table needs to provide the rules for each CC company.

Since this is your OWN data. You KNOW the day of the month your CC payment is due. It doesn't vary. Just put the day in the table. It's value will be 1-28 so that it doesn't change month to month. Each company also has minimum payment rules. The rule will be x% of the balance or a fixed minimum if the calculated percentage falls below the minimum. Most minimums are $25. So if the calculated amount is $23, then you have to pay the minimum. Otherwise, your balance can never go to zero except through a rounding error.
 

Vulpeccula

New member
Local time
Today, 10:58
Joined
Sep 5, 2020
Messages
23
Why are you using unbound forms? Access is a RAD (Rapid Application Development) tool. The whole point of using Access is to use its RAD feature .

The Credit card table needs to provide the rules for each CC company.

Since this is your OWN data. You KNOW the day of the month your CC payment is due. It doesn't vary. Just put the day in the table. It's value will be 1-28 so that it doesn't change month to month. Each company also has minimum payment rules. The rule will be x% of the balance or a fixed minimum if the calculated percentage falls below the minimum. Most minimums are $25. So if the calculated amount is $23, then you have to pay the minimum. Otherwise, your balance can never go to zero except through a rounding error.
Hi Pat,
Firstly may I say its good to meet you and thank you for an interesting response. I know from over 26 years as an Access "practitioner" that the "Bound/Unbound" argument is a real "pot-boiler" and I respect your POV.

I very much agree that the key value of Access lies in its RAD capabilities. For "proof of concept" projects I would use all the rich array of tools that Access provides. However, (in my book) in the development of databases in the commercial world, the business rules are written in the code, and no-one gets access to the tabulated data without passing through rigorous validation processes that can only be imposed by code but can easily be circumvented via unbound forms.

With regard to my posted problem, may I present you with this scenario:

1. I get paid twice each month - on the 7th and 24th; It never changes!
2. I have a credit account that is due every 28 days. :- It is clear that the "Due Date" creeps forwards leaving me in jeopardy of failing to meet my payment commitments. This is covered in your response - I see that!

But I am trying to construct a predictive model that covers all providers with differing payment cycles including PCM.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:58
Joined
May 7, 2009
Messages
19,169
i think you already know the answer to this but just cannot translate.
for pcm, just put "same" date (+1 month) to the last payment you made to your due date field
and leave the "number of days" field blank.
 

Vulpeccula

New member
Local time
Today, 10:58
Joined
Sep 5, 2020
Messages
23
Surely you aren't storing credit card details in an Access app? It really isn't secure enough

i think you already know the answer to this but just cannot translate.
for pcm, just put "same" date (+1 month) to the last payment you made to your due date field
and leave the "number of days" field blank.
Thank you arnelgp, I knew it would be blindingly obvious - just couldn't see the wood for the trees - that will do nicely sir, thanks again. :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:58
Joined
Feb 19, 2002
Messages
42,970
the business rules are written in the code, and no-one gets access to the tabulated data without passing through rigorous validation processes that can only be imposed by code but can easily be circumvented via unbound forms.
Incorrect. You have absolute control over whether or not a dirty record gets saved if you just use the FORM's BeforeUpdate event correctly.
I have a credit account that is due every 28 days
What company does that? I can't say that it doesn't happen. All I can say is I've not seen it - at least in the time I've been using computers to track bills.

I have a dozen credit cards plus the usual monthly bills. Each is due on the SAME day each month. I've even done projects for SNET (the telephone company for Connecticut before ATT acquired them). Their bill dates are 1-28 for EXACTLY the reason you mentioned. If eh they allow 29, 30, or 31, then the date will vary month to month and that confuses people. Feels to me like you are making this more complicated than it needs to be.
 

Vulpeccula

New member
Local time
Today, 10:58
Joined
Sep 5, 2020
Messages
23
Incorrect. You have absolute control over whether or not a dirty record gets saved if you just use the FORM's BeforeUpdate event correctly.

What company does that? I can't say that it doesn't happen. All I can say is I've not seen it - at least in the time I've been using computers to track bills.

I have a dozen credit cards plus the usual monthly bills. Each is due on the SAME day each month. I've even done projects for SNET (the telephone company for Connecticut before ATT acquired them). Their bill dates are 1-28 for EXACTLY the reason you mentioned. If eh they allow 29, 30, or 31, then the date will vary month to month and that confuses people. Feels to me like you are making this more complicated than it needs to be.
Hi Pat, have a look at Jacamo... They are a retailer of clothing for outsized people. Their statements are published every 28 days (its in their T&Cs)!
I first discovered this when, having already set up a "direct debit" payment for the account, I received a £12 failure of payment charge. I took the matter up with them as DD is their prefered method of payment. I highlighted the fact that if their payment "cycle" was 28 days but my monthly DD was set up for a specific day of the month. It follows then, that at least once or possibly twice per year I would fall into this trap. They simply replied "That's the system"! This is what prompted me to "knock up" a simple Access application that would alert me to this kind of anomaly, and it is an anomaly! Hope this clarifies...
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:58
Joined
Sep 21, 2011
Messages
14,038
Sorry, but a DD is down to the merchant, not the customer.?
Different to a SO you might create?
I always use a DD for my CC companies, so it is up to THEM! to get the money in time.?
If that was me, I'd be reporting them?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:58
Joined
Feb 19, 2002
Messages
42,970
have a look at Jacamo
Companies can do whatever they want but as Gasman said, you should be able to request a specific due date. The major companies used the method I suggested.
 

Vulpeccula

New member
Local time
Today, 10:58
Joined
Sep 5, 2020
Messages
23
Sorry, but a DD is down to the merchant, not the customer.?
Different to a SO you might create?
I always use a DD for my CC companies, so it is up to THEM! to get the money in time.?
If that was me, I'd be reporting them?
Thank you for that Gasman, but the story is true nonetheless. I should point out however, that this is a "store account" not a CC - don't know if that makes a difference.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:58
Joined
Feb 19, 2002
Messages
42,970
Store accounts use their own software and may not be able to accommodate the fixed date request. But all you have to do is to add 28 to the last due date to find the next one.
 

Users who are viewing this thread

Top Bottom