473,323 Members | 1,547 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

MS Access Date Calculation

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
7 2315
Seth Schrock
2,965 Expert 2GB
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
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
2,965 Expert 2GB
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
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
2,965 Expert 2GB
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
32,556 Expert Mod 16PB
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
2,965 Expert 2GB
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

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

Similar topics

1
by: Jacquo Johnson | last post by:
I am receiving the following error: MS Access Date/Time DBD::ODBC::st execute failed: , , " . "FROM WHERE > '2004/02/01 11:16:16'"; $sth = $dbh->prepare($sqlstatement); $sth->execute ||...
1
by: Apple | last post by:
May anyone can teach me how to calculate with date, my calculation is : Date field (2005/01) + numberic field (3) = 2005/04(YYYY/MM)
2
by: hyde | last post by:
How can I get the current date to appear in a form - but also be able to change that date permanantly example i have a start date and and end date for my clients these two date calculate in...
3
by: Lindie | last post by:
We need to track how often a book has been loaned. We enter the dates and need an automatic calculation how often in the preceeding 12 months it has been taken out. box 1: today's date box 2:...
1
by: untiroalaire | last post by:
Hi, here's what I'd like to do: get the time difference (Elapsed) between the "received date" field for all emails (running total) I've dumped into access from outlook and summarize at the...
3
by: yxq | last post by:
Hello, The XP Desktop clean wizard can get the last access time of desktop shortcut, i found that the info come from ...
2
by: smcgrath via AccessMonster.com | last post by:
I have a table listing accounts with a closed date - our Trial balance drops the account after 7 days but our database keeps the record forever - How do I calculate the end of month less 7 days so...
2
by: Kissi Asiedu | last post by:
I have a table with a del_date and mem_address fields. I need help in running a querry that will list all mem_address that have del_date 30 days from current date. Please help. Thanks.
1
by: chandurp | last post by:
Is there any attribute which indicate the last access date/time a user accessed DB2?
4
by: prao2005 | last post by:
Q)How to do the calculation for a date? Solution applied --> Defining a template xsl:template match="DBE:Object" mode="TestTable" Delivery Date Latest Possible Order Date
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.