Due Date Calculated

I need assistance on an expression that allows for the due date of recurring 
training to be one year from date completed. Details are as follows:

I have a form named [frmTRNComplete] based on table named [tblTRNComplete]

Form controls are:  TRNID (combo box), DateComp (Text Box) , DateNext (text 
box), Reccuring (check box)

I would like the DateNext to be 1 year from DateComp if Reccuring is true 
and Date next to be blank if Reccuring is false.
-- 
Aloha,
Ron A.
0
Utf
4/6/2010 6:15:01 PM
access.forms 6864 articles. 2 followers. Follow

5 Replies
1672 Views

Similar Articles

[PageSpeed] 28

Ron

I'll assume that you want to use the form to calculate the DateNext, and 
that you are not trying to store that calculated value in your underlying 
table.

So, if the record is checked "Recurring", do you want to see ONLY 1 year 
after DateComp, or do you want to see the "anniversary" date (1 year and 
multiples thereof)?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Ron A." <RonA@discussions.microsoft.com> wrote in message 
news:A2A88479-8D7A-4AF7-B53B-9C89A430F817@microsoft.com...
>I need assistance on an expression that allows for the due date of 
>recurring
> training to be one year from date completed. Details are as follows:
>
> I have a form named [frmTRNComplete] based on table named [tblTRNComplete]
>
> Form controls are:  TRNID (combo box), DateComp (Text Box) , DateNext 
> (text
> box), Reccuring (check box)
>
> I would like the DateNext to be 1 year from DateComp if Reccuring is true
> and Date next to be blank if Reccuring is false.
> -- 
> Aloha,
> Ron A. 


0
Jeff
4/6/2010 7:11:07 PM
Actually, I do need the value to be stored in the underlying table. I am 
using the subform to populate the underlying table, I do not want to have 
type the NextDue date if access can calculate it for me, especially since our 
reoccuring training frequency is always 1 year. I would like the date to be 1 
year from the DateComp if the recurring check box is true and blank otherwise.
-- 
Aloha,
Ron A.


"Jeff Boyce" wrote:

> Ron
> 
> I'll assume that you want to use the form to calculate the DateNext, and 
> that you are not trying to store that calculated value in your underlying 
> table.
> 
> So, if the record is checked "Recurring", do you want to see ONLY 1 year 
> after DateComp, or do you want to see the "anniversary" date (1 year and 
> multiples thereof)?
> 
> More info, please...
> 
> Regards
> 
> Jeff Boyce
> Microsoft Access MVP
> 
> -- 
> Disclaimer: This author may have received products and services mentioned
> in this post. Mention and/or description of a product or service herein
> does not constitute endorsement thereof.
> 
> Any code or pseudocode included in this post is offered "as is", with no
> guarantee as to suitability.
> 
> You can thank the FTC of the USA for making this disclaimer
> possible/necessary.
> 
> "Ron A." <RonA@discussions.microsoft.com> wrote in message 
> news:A2A88479-8D7A-4AF7-B53B-9C89A430F817@microsoft.com...
> >I need assistance on an expression that allows for the due date of 
> >recurring
> > training to be one year from date completed. Details are as follows:
> >
> > I have a form named [frmTRNComplete] based on table named [tblTRNComplete]
> >
> > Form controls are:  TRNID (combo box), DateComp (Text Box) , DateNext 
> > (text
> > box), Reccuring (check box)
> >
> > I would like the DateNext to be 1 year from DateComp if Reccuring is true
> > and Date next to be blank if Reccuring is false.
> > -- 
> > Aloha,
> > Ron A. 
> 
> 
> .
> 
0
Utf
4/7/2010 12:27:01 AM
As Jeff explained, you should not store the DateNext in your table, as this 
violates a basic rule of data normalization and will cause you grief.

You could display the next date with a text box bound to an expression such 
as:
    =DMax("DateComp", "tblTRNComplete", "TRNID = " & Nz([TRNID],0)

If you want to automatically assign the date one year from the last entry 
when entering a new record, you could use the AfterUpdate event procedure of 
the combo to assign it:
Private Sub TRNID_AfterUpdate()
    Dim varLastDate As Variant
    If Me.NewRecord Then
        If Not IsNull(Me.TRNID) Then
            varLastDate = DMax("DateComp", "tblTRNComplete", "TRNID = " & 
Me.TRNID)
            If Not IsNull(varLastDate) Then
                Me.DateComp = DateAdd("yyyy", 1, varLastDate)
            End If
        End If
    End If
End Sub

If you need to calculate future dates on the fly as well, the techniques in 
this article may help:
    Recurring events
at:
    http://allenbrowne.com/AppRecur.html

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Ron A." <RonA@discussions.microsoft.com> wrote in message 
news:71B8B7C7-4705-4C0D-8E71-108A385D6894@microsoft.com...
> Actually, I do need the value to be stored in the underlying table. I am
> using the subform to populate the underlying table, I do not want to have
> type the NextDue date if access can calculate it for me, especially since 
> our
> reoccuring training frequency is always 1 year. I would like the date to 
> be 1
> year from the DateComp if the recurring check box is true and blank 
> otherwise.
> -- 
> Aloha,
> Ron A.
>
>
> "Jeff Boyce" wrote:
>
>> Ron
>>
>> I'll assume that you want to use the form to calculate the DateNext, and
>> that you are not trying to store that calculated value in your underlying
>> table.
>>
>> So, if the record is checked "Recurring", do you want to see ONLY 1 year
>> after DateComp, or do you want to see the "anniversary" date (1 year and
>> multiples thereof)?
>>
>> More info, please...
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Access MVP
>>
>> -- 
>> Disclaimer: This author may have received products and services mentioned
>> in this post. Mention and/or description of a product or service herein
>> does not constitute endorsement thereof.
>>
>> Any code or pseudocode included in this post is offered "as is", with no
>> guarantee as to suitability.
>>
>> You can thank the FTC of the USA for making this disclaimer
>> possible/necessary.
>>
>> "Ron A." <RonA@discussions.microsoft.com> wrote in message
>> news:A2A88479-8D7A-4AF7-B53B-9C89A430F817@microsoft.com...
>> >I need assistance on an expression that allows for the due date of
>> >recurring
>> > training to be one year from date completed. Details are as follows:
>> >
>> > I have a form named [frmTRNComplete] based on table named 
>> > [tblTRNComplete]
>> >
>> > Form controls are:  TRNID (combo box), DateComp (Text Box) , DateNext
>> > (text
>> > box), Reccuring (check box)
>> >
>> > I would like the DateNext to be 1 year from DateComp if Reccuring is 
>> > true
>> > and Date next to be blank if Reccuring is false.
>> > -- 
>> > Aloha,
>> > Ron A.
>>
>>
>> .
>> 
0
Allen
4/7/2010 2:13:03 AM
I am not sure I understand why the DateNext value would not be stored in the 
table. How then would I query employees that are overdue training if I don't 
store the value? Thanks for you patience.
-- 
Aloha,
Ron A.


"Allen Browne" wrote:

> As Jeff explained, you should not store the DateNext in your table, as this 
> violates a basic rule of data normalization and will cause you grief.
> 
> You could display the next date with a text box bound to an expression such 
> as:
>     =DMax("DateComp", "tblTRNComplete", "TRNID = " & Nz([TRNID],0)
> 
> If you want to automatically assign the date one year from the last entry 
> when entering a new record, you could use the AfterUpdate event procedure of 
> the combo to assign it:
> Private Sub TRNID_AfterUpdate()
>     Dim varLastDate As Variant
>     If Me.NewRecord Then
>         If Not IsNull(Me.TRNID) Then
>             varLastDate = DMax("DateComp", "tblTRNComplete", "TRNID = " & 
> Me.TRNID)
>             If Not IsNull(varLastDate) Then
>                 Me.DateComp = DateAdd("yyyy", 1, varLastDate)
>             End If
>         End If
>     End If
> End Sub
> 
> If you need to calculate future dates on the fly as well, the techniques in 
> this article may help:
>     Recurring events
> at:
>     http://allenbrowne.com/AppRecur.html
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> 
> "Ron A." <RonA@discussions.microsoft.com> wrote in message 
> news:71B8B7C7-4705-4C0D-8E71-108A385D6894@microsoft.com...
> > Actually, I do need the value to be stored in the underlying table. I am
> > using the subform to populate the underlying table, I do not want to have
> > type the NextDue date if access can calculate it for me, especially since 
> > our
> > reoccuring training frequency is always 1 year. I would like the date to 
> > be 1
> > year from the DateComp if the recurring check box is true and blank 
> > otherwise.
> > -- 
> > Aloha,
> > Ron A.
> >
> >
> > "Jeff Boyce" wrote:
> >
> >> Ron
> >>
> >> I'll assume that you want to use the form to calculate the DateNext, and
> >> that you are not trying to store that calculated value in your underlying
> >> table.
> >>
> >> So, if the record is checked "Recurring", do you want to see ONLY 1 year
> >> after DateComp, or do you want to see the "anniversary" date (1 year and
> >> multiples thereof)?
> >>
> >> More info, please...
> >>
> >> Regards
> >>
> >> Jeff Boyce
> >> Microsoft Access MVP
> >>
> >> -- 
> >> Disclaimer: This author may have received products and services mentioned
> >> in this post. Mention and/or description of a product or service herein
> >> does not constitute endorsement thereof.
> >>
> >> Any code or pseudocode included in this post is offered "as is", with no
> >> guarantee as to suitability.
> >>
> >> You can thank the FTC of the USA for making this disclaimer
> >> possible/necessary.
> >>
> >> "Ron A." <RonA@discussions.microsoft.com> wrote in message
> >> news:A2A88479-8D7A-4AF7-B53B-9C89A430F817@microsoft.com...
> >> >I need assistance on an expression that allows for the due date of
> >> >recurring
> >> > training to be one year from date completed. Details are as follows:
> >> >
> >> > I have a form named [frmTRNComplete] based on table named 
> >> > [tblTRNComplete]
> >> >
> >> > Form controls are:  TRNID (combo box), DateComp (Text Box) , DateNext
> >> > (text
> >> > box), Reccuring (check box)
> >> >
> >> > I would like the DateNext to be 1 year from DateComp if Reccuring is 
> >> > true
> >> > and Date next to be blank if Reccuring is false.
> >> > -- 
> >> > Aloha,
> >> > Ron A.
> >>
> >>
> >> .
> >> 
> .
> 
0
Utf
4/7/2010 4:43:01 PM
On Wed, 7 Apr 2010 09:43:01 -0700, Ron A. <RonA@discussions.microsoft.com>
wrote:

>I am not sure I understand why the DateNext value would not be stored in the 
>table. How then would I query employees that are overdue training if I don't 
>store the value?

By using a Query, and applying criteria to the calculated field *in the
query*.

It is NOT necessary to have a field stored in a table in order to display it,
search it, sort by it or much of anything else. All of these operations can
(and, in a case like this, should) be done on a Query.
-- 

             John W. Vinson [MVP]
0
John
4/7/2010 8:01:19 PM
Reply:

Similar Artilces:

Calculating system downtime, not including weekends help!
Sorry about posting on this exhaustive subject of adding time and date together. This should be a hint for Joe to create another book solel devoted to this subject. I need ONE formula to do several things. I track the downtime of our computers at work and use that to repor back to management to gauge it's impact on the dept. My dept is open from 7am-6pm M-F, no weekends so I only count the tim down during those 11 hrs. However, computers may go down on 2pm on Friday and we still can't access our applications until 10am on th following Monday. But, I don't want to count those...

Setting not only due date but due time in Tasks
I have attempted to enter both the date and time in the DUE DATE field but I get the following error: "You must specify a valid date and/or time. Check your entries in this dialog box to make sure they represent a valid date and/or time." Here is what I am attempting to input: Fri 6/29/2007 4:05 PM So, what should the format be? What you want to do is not possible. Tasks don't have that degree of = granularity. They know only about due dates, not due date/times. You = can, however, set the reminder to a particular time.=20 --=20 Sue Mosher, Outlook MVP Author of Microso...

Help with POPing errors due to messsages with suspect files
Hi: I'm having difficulty POPing mail from an Exchange 2000 server (using an Outlook 2003 client). Generally, everything works fine, however, occasionally, a message that is later confirmed to contain a virus, causes POPing to grind to a halt. Messages received after the suspect message will not download to the client! Both the server and the client are running Symantec anti virus plugins. What gives? Thanks for any help. -- Dab Cut off: yourhead to respond Dab <noThanks@hotmail.com> wrote: > I'm having difficulty POPing mail from an Exchange 2000 server (usin...

Queries about changing date format
I have a table that has a date in it and it also has the time. I need to get rid of the time and leave the date. What is the best way to do this? -- Thomas In a query? Just use format to return the date you want. DateOnly: Format([YourFieldName],"short date") -- Hope that helps! RBear3 .. "T Miller" <TMiller@discussions.microsoft.com> wrote in message news:7B0A8BD4-4E3B-44B5-9A8D-3522F150E469@microsoft.com... >I have a table that has a date in it and it also has the time. I need to >get > rid of the time and leave the date. What is the best w...

Outlook 2003 Vista Problem
Vista Ultimate x64, Outlook 2003 and IE7: Whenever I click on a link in my Outlook inbox, I receive an error message "this operation has been canceled due to restrictions on this computer". How can I change this? It's frustrating to have to copy and paste links into IE7. I am running as admin. It's an IE error. reset web settings in IE's tools, options. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outl...

A required action was not successful due to an unspecified error.
I can not longer send e-mail, the message "A required action was not successful due to an unspecified error" occurs. Anyone..? -- Martin Outlook version? Does it work in Safe Mode? Have you tried running scanpst.exe against your .pst file? How big is the .pst file? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft.com/KB/555375 After furious head scratching, Martin asked: | I can not longer send e-mail, the message "A require...

Can one sort entries without due dates after entries with due dates?
I'm using Outlook 2007. I would like to sort all to-do entries without due dates AFTER entries that have a due date. When I define a sort order of descending due dates, Outlook sorts those without a due date to the end but of course also those that do have a date in reverse temporal order which I find useless. Using *ascending* due dates sorts those without a date first, which I find annoying. So, is there a way to tell it to sort "non-existing" higher than any real date? Michael ...

Date Range Totals for Logical Fields
Hi All: I have a database I import from Outlook, and am keeping a list of all active recruits. Each week, I need to update 10 logical fields to see how many more entries were made to each logical question. What is the best way to get my results. I created a query with these fields that did a sum of all these filds, but when I try to add to the criteria true for "Active Recruit", it doesn't work; and if I put a lead date range of "Between #12/1/2007# And #1/31/2008#", It dosn't like that either. I like the way the sum queries work - it's one line with...

Imported dates have month first.
I have imported some dates. They are in the mm/dd/yy format. Half of the cells, excel recognizes as dates(days under 12 used as months), and the other dates as text(13 and above). My dates are in column A: 10/06/24 Cell B1 has the formula: =LEN(A1) Cell C1 has the formula: =IF(LEN(A1)=5,A1,DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))) This works for the text cells, but it doesn't reverse the date cells. I think I got this formula from this newsgroup, but I can't find it. Any help would be great. Joe If you're importing the data from a text file (.txt), then you should be able to ju...

Subform (Datasheet) Calculation (Syntax) Issues
I have a subform displayed in Datasheet view with the followign fields: Product Number (cbo), Product Name (cbo), Quantity, Discount %, Unit Price, SupplierID, and Product Number (Ref.) I want the user to select an item using the pull-down menu, enter a quantity and have the subform calculate the amount charged based on the discount that's stored elseware, the unit price and the quantity. This is the formula I have and it's not working for some reason: =IIf(IsNull([Quantity]),0,(Sum(([Quantity]*[Unit Price])-([Quantity]*[Discount]*100)/100*[Quantity]))) I'm getting an #ERR...

Formula with date
Hi, Hello, I need help with a formula for excel ... I tell you right away that I am a newbie to excel. I need the following things: Should I work with a formula so that if the date entered in a given cell A1 is older than 3 years in cell A2 is shown the text "old" How can i do that? TIA On Thu, 28 May 2009 11:50:14 +0200, "myname" <mynameis@try.com> wrote: >Hi, >Hello, >I need help with a formula for excel ... >I tell you right away that I am a newbie to excel. >I need the following things: >Should I work with a formula so that if the date ente...

::: Problem with "if between two dates" :::
Hello, I have a problem with "if between two dates". My problem is describe into the following Excel file http://cjoint.com/?hpqqXE0lBq Could anyone help me? Many thanks in advance. Jacques I looked at your sheet and I came up with the following formula: =IF($B$14<C19,"Too Late",IF(AND($B$14>C19,$B$14<C18-1),"Exit Now",IF($B $14>=C18,"Not Yet",FALSE))) I had a bit of trouble understanding your question but I think this is what you wanted. Hope that helped Mark Hi, I will try your solution. It looks very good, I think you understo...

Calculate average of a range, but exclude errors
Hello! In my workbook I have several worksheets. In cell B2 of every worksheet, there is an average from the data in that worksheet. As this is a template, some data is not entered yet, and some B2 cells show error (div by 0). Now, I wanted to calculate the average from B2 cells of all worksheets, but exclude all cells with errors. My formula =Average(Sheet1:Sheet12!B2) How do I modify this to work? Thakks Peter Hi Peter, Change the formulas of the cells that you want to average to =IF(ISERROR(YourFormula),"",YourFormula) or, even better, check for the divider being zero...

separating Date and Time
I need to separate a datetime field and just show date. I've seen the posts regarding TimeValue but I'm not having any luck with this. The field is set up as a text field and the data looks like this : 11/30/1999 2:17:52 PM I've used this in my query : Mod Action Time : TimeValue(Mid([ACTIONTIME],9,2) & ":" & Mid([ACTIONTIME],11,2)) where mod action time is a new blank field and ACTIONTIME is the existing text field described above. Please help. Thank you, Welthey On Tue, 22 Jan 2008 17:40:05 -0800, Welthey <Welthey@discussions.microsoft.com>...

Default Task due date
I've looked all over, is there a way I can set the due date on a new task to today? By default the due date ion a new task s "none". ...

time/date stamp on email
i used microsoft outlook for email. i sent an email to another outlook user and i wanted to find out if he's read it or not. is there any way to view the time/date that the person reads the email ? if you requested a read receipt and if the recipient uses a program that is capable of replying, yes. otherwise, no. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchang...

calculation 02-08-08
I have a graduate student database that tracks courses and requirements. A foreign language requirement needs to be fulfilled 2 years after they start their graduate program. Start dates are tracked as follows: 200701 = Fall, 2007 200702 = Winter, 2008 200703 = Spring 2008 200704 = Summer 2008 200801 = Fall 2008, etc. etc. How can I get a text box to automatically calculate the due date for the language requirement? I.e. if they started their program in 200701, their language needs to be fulfilled by 200901. This would be trivial if the columns were atomic. You would just add 1 to ...

age calculation #3
I am trying to find a calculation for "how old will this person be on this date" example: Johnny was born 08/10/1910 and I want to know as of 07/01/2005 how old Johnny will be, without rounding up. Answer: 94 years old Is this even possible? http://www.xldynamic.com/source/xld.html Regards, Peo Sjoblom "Teapot" wrote: > I am trying to find a calculation for "how old will this person be on this > date" > > example: Johnny was born 08/10/1910 and I want to know as of 07/01/2005 how > old Johnny will be, without rounding up. > ...

begginer Q, date
How to filter query to list all date field who have birthday from NOW date? Tnx Your question is unclear. Do you want anyone that has a birthdate after today? Essentially, no one has been born after today, so this would be entry errors Field: BirthDate Criteria: > Date() Do you want anyone that has a birthday on or after today's date for the remainder of the year? Field: MonthAndDay: Format(BirthDate,"MMDD") Criteria: >= Format(Date(),"MMDD") -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Ma...

Getting the current date/time
Hello, I'm trying to access a date field of a particular database and the code generated by MFC defines the date variable as "DATE". I believe this DATE referes to COleDateTime and I don't know how to get the current date/time to populate this field. I'm tried the following already, but none of these worked: ----------- Example 1 ----------- DATE test; test = COleDateTime::GetCurrentTime() --------------------------------- VC says that COleDateTime is not defined. ----------- Example 2 ----------- DATE test; test = ::GetCurrentTime() -------------------------------...

Trying to make cell "date sensitive" to specific date
How do I make a cell "date sensitive" to calculate a formula or input a value on a specific date? I suppose something like: =IF(TODAY()="1/18/2005", <your formula here>,"") In article <811D02E7-FAF6-43C8-A723-ABAD20EF39B2@microsoft.com>, "ebuzz13" <ebuzz13@discussions.microsoft.com> wrote: > How do I make a cell "date sensitive" to calculate a formula or input a value > on a specific date? ...

the resources available for the selected dates or times do not mee
the resources available for the selected dates or times do not meet the minimum resources for this service. I have set up the service with -- Robert D ...

Concatenate 2 columns date & time
Hi, I am working with Excel 2003 and have a ws that I need to combine column B which is a date, with column C which contains the time. No matter how I have formatted column D, I cannot get the date to display correctly. What I have is: B C 05/01/2010 10:55 24/12/2009 09:35 I need column D to show as: 05/01/2010 10:55 24/12/2009 09:35 I have tried several different formulas/formatting in Column D but always end u...

ERROR check if a date set entered violates a previously entered date set
I am devloping an Excel program that has a list of date sets, not necessarily in sequencial order. What I want is an ERROR CHECK if a date set violates any date set previously entered. What I was looking for was that a date set could not be typed in as follows: 1/10/04 to 4/20/04 2/10/04 to 3/20/04 The second date set falls within the first set and should be invalid setting off a message or an alert of an invalid entry. I am interested in making it idiot proof so that one could not enter date sets within or covering dates sets already entered. This one really throws me, Please help. Buzz, Ar...

Question on Sorting by (date, etc)
I have a column (A) which sre dates. If I select the column heading and click either the sort ascending or desending button, the date column alone sorts independent of the other eight columns that comprise the entire entry. How would I go about being able to sort by a particular column and have everything sort with it? I have other columns that I would like to sort by sometimes too, like "color", "type", etc. Thanks for reading! select all the columns and now go to data | sort . On Feb 20, 11:21=A0pm, "Ed" <2...@333.com> wrote: > I have a column...