Calculate date based on other table

Below are my 2 tables:-

Table1
ItemID (primary key)
Days

Table 2
Serial number(primary key)
ItemID 
dateExpired

I need the dateExpired to be auto calculated when I enter the ItemID. The
dateExpired is today's date + the days in the table 1...May I know the way?

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200705/1

0
EMILYTAN
5/21/2007 6:36:57 AM
access.formscoding 7493 articles. 0 followers. Follow

1 Replies
988 Views

Similar Articles

[PageSpeed] 36

It would be a good idea to create an index for the ItemID field in Table 2

You did not say whether there is a form involved of if you are doing 
recordset processing or if this is being done in a query.  Where you are 
doing this will determine how you do this.

Post some additional detail, please.
-- 
Dave Hargis, Microsoft Access MVP


"EMILYTAN via AccessMonster.com" wrote:

> Below are my 2 tables:-
> 
> Table1
> ItemID (primary key)
> Days
> 
> Table 2
> Serial number(primary key)
> ItemID 
> dateExpired
> 
> I need the dateExpired to be auto calculated when I enter the ItemID. The
> dateExpired is today's date + the days in the table 1...May I know the way?
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200705/1
> 
> 
0
Utf
5/21/2007 12:23:01 PM
Reply:

Similar Artilces:

Date entry
How can I enter a date without having to type a "/" between the month, day, and year? magstate, have a look here http://www.cpearson.com/excel/DateTimeEntry.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "magstate" <magstate@discussions.microsoft.com> wrote in message news:41CD9BD7-1836-447E-8811-32EE53D8EA8C@microsoft.com... > How can I enter a date without having to type a "/" between the...

Hyperlink to today's date #2
Sweet! Works like a charm, but... I have to share this sheet with othe and I think that the double-clicking-to-the-date may confuse them (i they try to edit a cell). Is there a way to map it to a hot key? Or make it so if I click in A it jumps to the date? (the scrolling to three rows up was a nice touch!) Thanks for the help again -- Grim/Do ----------------------------------------------------------------------- Grim/Don's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1445 View this thread: http://www.excelforum.com/showthread.php?threadid=26078 You could...

Owa and non forms based authentication
I am having trouble getting Owa forms based authentication to work with Oma(i have a previous post on this). I'm wondering just how much of a security risk Owa non FBA really is. Is the authentication information really sent in clear text(i know the session information is encrypted when using ssl with owa). Thanks FBA sends the authentication across the network in clear text, that is why SSL is required to implement it. IMHO, adding FBA increases your security by a pretty good factor? Want a number, how about "doubles it"? I'm just basing this on the fact that it is...

How to produce a chart based on number of cells not values in the cells
Hi I'm using Excel 2007. I have a requirement to base a chart on the number cells rather than the values in the cell. For example, here are some values I might enter against Monday to Friday: 1,3,1,1,2 In this example, I want my (pie) chart to show that 3/5 are value 1, 1/5 is value 2 and 1/5 is value 3 (i.e. the pie chart should have 3 sections). What excel does, is split the chart into 8 sections, and shows each individual value. Any suggestions on how I might achieve this, Thanks for any help Colin You need to do some calculations in the sheet first, then plot the results: http...

Audit Trails
Hi All, I have noticed that when using "Audit Trails", you cannot add tables with text columns (notes fields) into the Auditable tables section of the Audit Trail Maintenance screen. How can i get around this problem? I would have thought that all tables could be made auditable? regards, TP ...

Windows Live Mail "Received Date" issue
Hi, I have recently decided to move my Google Apps business email account to Office Live. The only way I have found to 'migrate' my emails to my Office Live email account is by dragging and dropping the emails from "GMail" to my Office Live account using the WLM GUI. WLM (GUI and Web) will display an incorrect received date - which is the migration date of when I have subscribed to Google Apps email. The only way I have found to show a correct date is to display the "sent" column in WLM GUI. Is there a way to correct this ? Maybe there is a ...

Look up data from a table in a text box
Hello, I have a text box on a form and I would like data to be viewed from a table. In the properties of the text box on the control source I place =[Date Ran]![DT_TM_RAN]. I thought this would show the data in the DT_TM_RAN field in the text box. But when I open the form it shows #Name? Can someone please direct me in the right direction to fix this issue. Thanks for your time. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1 I think the Dlookup function is what you want. Help on that function should tell you e...

Simple question: Difference between two dates?
Suppose cell A2 has 6/27/05 - 7/1/05 A3 has 7/4/05 - 7/8/05... and so I need to know the formula to coomputer the number of days betwee those dates, which is obviously 5. The reason it need to keep the rang in one cell is this will be used in a VLookup function. Thanks -- dstoc ----------------------------------------------------------------------- dstock's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2422 View this thread: http://www.excelforum.com/showthread.php?threadid=38132 Anyway you slice it, you have to get the 2 dates seperated... So you can have ...

Paste data into Knowledge Base in 4.0
We have a client that actively uses the knowledge base. To create new articles in 3.0 they frequently used the copy and paste function to get the data into the template. In 4.0 this causes CRM to lock up, run slow, take forever to save, take forever to open (if it ever does save) and generally behave in an unacceptable manner. The only way we have gotten this to work at all is with a plain text document. Any formatting in Word or Excel, and it does not function. Has anyone else seen this? Any thoughts on how we can address to make functional? Bill Hi Bill, I'm not sure ...

Sum products prices that are between two dates
Hi all, I was wondering if someone could tell me what the formula would be if I wanted to add the prices of products that were purchased between two dates. If column A was formatted with the dates between 01/01/06 and 31/01/06 and column B with the prices. Thanks in advance Maddoktor Hi Maddoktor In cell D1, enter the Start date required in cell E1 enter the end date required. In F1 =SUMPRODUCT(--($A$1:$A$100>=D1),--($A$1:$A$100<=E1),$B$1:$B$100) Change the ranges to suit. -- Regards Roger Govier "Maddoktor" <maddoktor@clinic.com> wrote in message news:e1v...

Converting Access 2000 table to Access 97
How do you link an Access 2000 table to an Access 97 database? Thanks for your time. ...

Saving Pivot Table as a Web Page Excel 2003
When trying to save a Pivot Table as a Web Page I go through the following steps : Click and drag the entire Pivot Table. Pull Down the file menu and Click Save as Web page and then click the Publish button within the save as dialog box. From there I am to name the web page and save the folder that corresponds with the name of my instruction book. After Clicking on the publish button the save as dialog box disappears and I am no longer able to name the web page or the folder that I am working from. I also finish checking the rest of the boxes in the Publish as Web Page Dialog bow. ...

Copying tables with hidden cells
I have a table in Excel 2000 with hidden columns and I want to copy it to Word without the hidden columns reappearing. Is there anyway I can do this? Not to worry. I've worked out how to do it eventually. >-----Original Message----- >I have a table in Excel 2000 with hidden columns and I >want to copy it to Word without the hidden columns >reappearing. Is there anyway I can do this? >. > ...

I cannot change the date format to English canada
Hi- if you'd like to change the format of one cell, select the cell and reformat it. After selecting the cell, go tot he format menu, choose the first option: cells. From the pop-up form, goto the Number tab (should be the first one)- in the category section- choose date, in the locale section choose English(Canada)- then choose the format in the Type section. choose OK. It should work. If this is not what you ment, please let me know. Also, indicate what you've tried and why it hasn't worked- Good Luck! pim -- Pim ---------------------------------------------------------...

Flagging up dates in cells #2
Cheers Alex All sorted now -- midase ------------------------------------------------------------------------ midase's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15863 View this thread: http://www.excelforum.com/showthread.php?threadid=273554 ...

How do I sort by date (not days, weeks, months) in Excel 2000?
Hi Tony not sure what you're asking here, if you could type out say five lines of your data and then what you want to see, i'm sure we'll be able to help (please, however, do not attach a workbook) Regards julieD "Tony" <Tony@discussions.microsoft.com> wrote in message news:C78C1E27-F4A9-4DB4-BA16-EBACE47042A5@microsoft.com... > ...

averaging a value between two serial dates
Hi all, I have two spreadsheets. The first spreadsheet contains two columns, one for start date/time (in serial format) and the second for stop date/time (in serial format). The second spreadsheet contains two columns. One containing a serial date/time and the other containing a meteorological value (eg. temperature). I want Excel to read the start and stop date/time from the first spreadhseet and then search the second spreadsheet for all temperature values which were recorded during this time frame. Lastly, I want Excel to average these values. Is this possible using a single Excel func...

Business Portal Share Point 2007 Form based authentication
Does or will business portal run on Share point 2007 server (not services) and will it support form based authentication? Neil There is no official information on this topic. However, GP 10 is supposed to work with Office Sharepoint Server 2007. -- Charles Allen, MVP "Neil" wrote: > Does or will business portal run on Share point 2007 server (not services) > and will it support form based authentication? > > Neil Charles, is there a timeline for GP 10? What about form based authentication? "Charles Allen" wrote: > There is no official informati...

Calling Lookup popup window and filter values based on parameters
Hi All, Need your all's help for the following. On one of the form we have four lookup controls in following order. Account, Project, Project Task, Incident. Last 3 are custom entities. We need a functionality where, - when i open a Account lookup, it gives me account only related to current crm user. - After that when i open a Project lookup it should give projects only relted to account we have selected. - In the same way ProjectTask should be as per Project - While case should be as per account. Whats the best way of doing it? Awaiting reply. Thanks and Regards, Darshan ...

Choosing records to print based on latest revision
I have a training matrix that has the following fields: Procedure Employee Rev Date trained There can be multiple revisions for the same procedure. I would like to print a report by procedure that only shows the latest rev number. How would I do this? Thanks! "madmckenna" <madmckenna@discussions.microsoft.com> wrote in message news:05FAE845-9000-49D4-AF3F-CD05B199184D@microsoft.com... >I have a training matrix that has the following fields: > Procedure > Employee > Rev > Date trained > > There can be multiple revi...

calculating by color
Is there a way to calculate various total depending on the color of the data entered scenario: row of mixed numerical data. there is no pattern for the data. at the end of the row there are four cells that need totals to be placed in them from the cells in the row with certain colors. take for example a time sheet for a job...one color for sick days, another for vacation, holidays, etc...is it possible? Tamesh, See my reply a couple of days ago at http://tinyurl.com/379lx -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email ad...

mdf file date wrong
I hvae a client that has the log and the data file in the same place for years. Same drive, same folder, etc. )I know it's a good idea to put the log elsewhere but it's not necessary here). Anyway, my question is regarding the date of the mdf file. THis is in SQL 2000 running on Windows Server 2003. Everything is up to date. We're moving things over to SQL 2005 so I was going to do some testing last night when I noticed the date on the log file was 1/10/2010 but the date on the data file is 11/15/2009 11:57 AM. Their office has been up and running this morning for a f...

Date Formula Needed-Business Days
I need a formula that calculates business days, but the NETWORKDAYS function is not adequate. I need the formula to return the number of days between two dates, excluding a range of holidays. Since my store is open seven days a week, the NETWORKDAYS function will not suffice. When I used Quattro Pro in the past, it had a BDAY function, and it accepted parameters to indicate if weedends were to be included or not. Is there a comparable function in Excel? I am trying to calculate the number of days the store is open every week. I thought of using NETWORKDAYS and simply adding 2 (Satu...

Problem Changing Data Source In Pivot Table
Hello all. I have a workbook with multiple pivot tables pulling from a odbc datasource. The database has been moved to a new server and I a finding that even when I update the SQL with the new location in an one query, it tells me it cannot graphically represent the SQL an cannot pull the table. I am unsure as to whether or not I am mis-typin something (although not really sure how i could do that) or if i a missing some other place i need to update the link. Also, is there way to do it for all of the pivot tables at once? I have Excel 2000. Thank you!!! An example of the sql as it is (scr...

calculating time formula
Hi, i want to set up a time summation calculation in excel. for example i put in how many hours i've working on something over a number of days down one column and a "Total hours and mins worked" cell somewhere else just adds all my inputs in hour and min format. eg. 03:20 (ie. 3 hours 20 mins) - thanks for any comments. Hi just enter your time values in Ex^cel's time format and use a formula such as =SUM(A1:A10) Important: Format your target cell with the custom format [hh]:mm -- Regards Frank Kabel Frankfurt, Germany "Nick" <ad@asd.com> schrieb im ...