By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,791 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

MS Access Date Calculation

P: 3
I want to calculate my Invoice Date field along with my Terms field and have the result stored in my Invoice Due Date field? To make it simple, my Invoice Date field has 1/1/2011 in it, my Terms field has Net 30 days selected, so I want access to calculate this and place the date (1/31/2011) in my Invoice Due Date field... how can I do this in form design?
Jan 5 '12 #1
Share this Question
Share on Google+
7 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
Lets see if I'm understanding correctly. You have a control that you enter the invoice date (I'll call it txtInvoiceDate) and you have another control that has the amount of time that the customer has to pay the bill, which you call Term (I'll call the control txtTerm). You want to be able to enter a date in txtInvoiceDate and the number of days in which the company has to pay the bill in txtTerm and have a third control (I'll call it txtDueDate) be Term days after Invoice Date. Am I correct?

If so, in the Control Source of txtDueDate put the following:

Expand|Select|Wrap|Line Numbers
  1. =[txtInvoiceDate] + [txtTerm]
You will then be able to enter a date in txtInvoiceDate and the number of days for the term in txtTerm, and then when you select txtDueDate, the due date will appear.
Jan 6 '12 #2

P: 3
That is correct. Okay, so now how do I show that calculation result (=[txtInvoiceDate] + [txtTerm]) in my txtDueDate field itself. Right now I have a true field called InvoiceDueDate, I want that result (txtInvoiceDate)+(txtTerm) to automatically appear in InvoiceDueDate field on reports, forms, tables, etc. What do I have to do to make that happen?-- thanks.
Jan 6 '12 #3

Seth Schrock
Expert 2.5K+
P: 2,951
I tried everything that I know to make it appear automatically (like a requery on the control) and the only thing that I could get to work is to select the txtDueDate. After the txtInvoiceDate and txtTerm controls have been updated, selecting txtDueDate should make it work.
Jan 6 '12 #4

P: 3
That's fine from a form perspective, but I want that result to stored in a true field name (InvoiceDueDate) on the table side. If I place the code you gave me (=[txtInvoiceDate]+[txtTerm]) in the control source then I'm not identifying the correct field name in the control source, in this case InvoiceDueDate. Is there a way in the control source to show that I want the result to be stored in the InvoiceDueDate field, but the actual result is coming from another text box? I hope that makes sense.
Jan 6 '12 #5

Seth Schrock
Expert 2.5K+
P: 2,951
Sorry it took so long to reply. I just tested another method and it worked. You can leave the Control Source alone (allowing it to stay connected to the table. In txtTerm's AfterUpdate event, you can put the following:
Expand|Select|Wrap|Line Numbers
  1. Me.txtInvoiceDueDate = [txtInvoiceDate] + [txtTerm]
This assumes that you populate the invoice date first and then the term second. If you do it the other way, then switch the above code to the AfterUpdate event of txtInvoiceDate. Either way, as soon as you leave the control, the AfterUpdate event will trigger calculating the due date.
Jan 9 '12 #6

NeoPa
Expert Mod 15k+
P: 31,709
Just to make sure you know, the recommended way to handle a situation such as this is not to store the due date at all, but to calculate it as and when required. This is in accordance with Normalisation theory (See Database Normalisation and Table Structures) and you would be well advised to conform to those recommendations.
Jan 9 '12 #7

Seth Schrock
Expert 2.5K+
P: 2,951
I should have known that. I don't know why I didn't think about it. Thanks NeoPa for the reminder.
Jan 9 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.