How to calculate payment due dates

[Priya06052014]

I need help to calculate all the payment due dates for a hire purchase database. For example i got the invoice date to be 01/01/2014 and payment to be made Monthly upon 6 months.

So if the
Invoice_Date: 01/01/2014
Payment_Term: Monthly
No_of_Credit_Term: 6

I want to show all the payment due dates of 01/02/2014, 01/03/2014, 01/04/2014, 01/05/2014, 01/06/2014, 01/07/2014.

[jimatqsi]

Priya06052014,
Welcome to Bytes.com. Your question lacks some important context. Do you need to do this calculation on a form, filling in boxes on the form after entering the known data? Or do you need to write a query to calculate these dates across an entire table of entries?

I can tell you this. The calculation of dates could be done using the DateAdd function. Something like this would work:

DateDue=DateAdd("m",1,InvoiceDate)

would give you DateDue=1 month past the invoice date.

You'll find it helpful to study the common date functions for Access
http://office.microsoft.com/en-us/ac...010131676.aspx

Also, since you may be new to Access, spend some time with the Northwind sample database for MS Access. You should be able to find it under the Help section of your Access program.

Jim

[Priya06052014]

Hey Jim

I will need the payment due dates to be shown on the Payment Form.The Payment Form will contain the invoice date(01/01/2014), Payment Term (Monthly) and No of credit term (6).
After which i need all the payment due dates to be shown in the Payment Form.

[jimatqsi]

Have you created the form? Has the form got an text box for each of these dates and other fields mentioned?

If you are just now making that form be sure to name each text box and other object carefully. Don't just accept the meaningless names Access gives each new object. Give them meaningful names so your code makes sense when it is read. something like

me.Paymen1Due_txt=DateAdd("m",1,me.InvoiceDate)
me.Paymen2Due_txt=DateAdd("m",2,me.InvoiceDate)
me.Paymen3Due_txt=DateAdd("m",3,me.InvoiceDate)
me.Paymen4Due_txt=DateAdd("m",4,me.InvoiceDate)
me.Paymen5Due_txt=DateAdd("m",5,me.InvoiceDate)
me.Paymen6Due_txt=DateAdd("m",6,me.InvoiceDate)
 

Jim

[Priya06052014]

I have a table named payment dates with fields name StartDate and EndDate. If the StartDate is 01/01/2014 and Enddate is 01/07/2014. Then in payment dates sql query how can I code to show all the month of 01/02/2014, 01/03/2014,01/04/2014,01/05/2014 and 01/06/2014.

Thanks

[jimatqsi]

You may find these built-in date functions helpful
Month(some date) returns the month of some date
Day(some date) returns the day of some date
Year(some date) returns the year of some date

So your query could test for month(StartDate)=1 or (Month(StartDate)=1 And Day(StartDate)<=7)

Search in Access Help for other functions. Take the time to explore.

Jim

[zmbd]

First you have to be very careful here:
""I want to show all the payment due dates of 01/02/2014, 01/03/2014, 01/04/2014, 01/05/2014, 01/06/2014, 01/07/2014.""
I'm guessing that this is in dd/mm/yyyy format?
Remember that this site is global, my first thought was mm/dd/yyyy

Next do you want this on the form as below, in a table, or what?
Your statement ""Then in payment dates sql query how can I code to show all the month of 01/02/2014, "" is a bit open to interpretation.

NEXT

Access will ALWAYS refer internally to the date in #mm/dd/yyyy#
Pitfalls and Traps: International Dates in Access (allen browne)

FINALLY
You have a field with term = 6 months
and a field witht Enddate #07/01/2014#
Get rid of one or the other. I advise the [EndDate] as you already have the term in months, simply calculate the end date as needed.

[NeoPa]

"Access will ALWAYS refer internally to the date in #mm/dd/yyyy#" -ZMBD.

By this, he means that any SQL reference will require literal dates to be stipulated in SQL standard format which is #m/d/y#. See Literal DateTimes and Their Delimiters (#) for more.