Date + 10 days?

I have a field that shows the date I sent a notice to someone.  I want to 
create a field that automatically shows the date the notice is due back, 
which is 10 days from the sent date.  I would also like the field to show up 
in red when the due date is past.
0
Utf
7/10/2007 1:42:00 AM
access 16762 articles. 3 followers. Follow

4 Replies
940 Views

Similar Articles

[PageSpeed] 11

=?Utf-8?B?YXJjaDI1Mg==?= <arch252@discussions.microsoft.com>
wrote in
news:9F8D2485-C1BB-41DB-8B94-E5DA949F87C2@microsoft.com: 

> I have a field that shows the date I sent a notice to someone.
>  I want to create a field that automatically shows the date
> the notice is due back, which is 10 days from the sent date. 
> I would also like the field to show up in red when the due
> date is past. 

Set the control source of the new field to 
=dateAdd("d",10,[DateSent])

Set the conditional format of the control to 'less than' DATE()

-- 
Bob Quintal

PA is y I've altered my email address.

-- 
Posted via a free Usenet account from http://www.teranews.com

0
Bob
7/10/2007 1:35:39 AM
In your query, add the following calculated field:
    DueDate: DateAdd("d",[NameOfDateSentField],10)
replacing NameOfDateSentField with the actual name of your sent date field. 
Note that you should not store the DueDate field in a table, since it can be 
calculated as required.

In your form or report (based on the query containing the DueDate field), 
apply conditional formatting to the control bound to the DueDate field.  Set 
the condition to Field Value Is less than Date(), and select the text 
colour/background colour you want.

HTH,

Rob

"arch252" <arch252@discussions.microsoft.com> wrote in message 
news:9F8D2485-C1BB-41DB-8B94-E5DA949F87C2@microsoft.com...
>I have a field that shows the date I sent a notice to someone.  I want to
> create a field that automatically shows the date the notice is due back,
> which is 10 days from the sent date.  I would also like the field to show 
> up
> in red when the due date is past. 


0
Rob
7/10/2007 2:00:59 AM
Bear with me, this is waaaaaay over my head, but I created the query and 
added the calculated field information that you gave me and it is working 
correctly because it the field appears in the Query Datasheet View.  How can 
I add that so it appears as a column on my table?

"Rob Parker" wrote:

> In your query, add the following calculated field:
>     DueDate: DateAdd("d",[NameOfDateSentField],10)
> replacing NameOfDateSentField with the actual name of your sent date field. 
> Note that you should not store the DueDate field in a table, since it can be 
> calculated as required.
> 
> In your form or report (based on the query containing the DueDate field), 
> apply conditional formatting to the control bound to the DueDate field.  Set 
> the condition to Field Value Is less than Date(), and select the text 
> colour/background colour you want.
> 
> HTH,
> 
> Rob
> 
> "arch252" <arch252@discussions.microsoft.com> wrote in message 
> news:9F8D2485-C1BB-41DB-8B94-E5DA949F87C2@microsoft.com...
> >I have a field that shows the date I sent a notice to someone.  I want to
> > create a field that automatically shows the date the notice is due back,
> > which is 10 days from the sent date.  I would also like the field to show 
> > up
> > in red when the due date is past. 
> 
> 
> 
0
Utf
7/10/2007 3:50:00 AM
As I said in my original post, you should NOT store the DueDate as a field 
in your table.  Since it can be calculated on-the-fly, that is what you 
should do.

You simply base your form (or report) which is presenting the data on the 
query with the DueDate field, rather than on the table.  You are using a 
form for your date entry/display, aren't you?  Tables are for data storage, 
not for data entry/editing.  And the conditional formatting, which is what 
you need for displaying some records with high-lighted fields, will only 
work in forms and reports; you cannot use it in table or query datasheets. 
You can set up a form in continuous view so that it resembles a table/query 
datasheet if that's waht you want; or (better, and commonly) you design the 
form so that it provides a clear useable interface to your data.

If this is all clear-as-mud to you, then I suggest you do some background 
reading before going any further.  Either buy a suitable reference book, or 
browse some of the many web sites which offer Access tutorials.  As a start, 
here's a link to a compilation of Access resources and other information:
http://www.accessmvp.com/JConrad/accessjunkie.html

Again, HTH,

Rob

"arch252" <arch252@discussions.microsoft.com> wrote in message 
news:D2C5AA45-B094-489C-80E0-377B04CBFF11@microsoft.com...
> Bear with me, this is waaaaaay over my head, but I created the query and
> added the calculated field information that you gave me and it is working
> correctly because it the field appears in the Query Datasheet View.  How 
> can
> I add that so it appears as a column on my table?
>
> "Rob Parker" wrote:
>
>> In your query, add the following calculated field:
>>     DueDate: DateAdd("d",[NameOfDateSentField],10)
>> replacing NameOfDateSentField with the actual name of your sent date 
>> field.
>> Note that you should not store the DueDate field in a table, since it can 
>> be
>> calculated as required.
>>
>> In your form or report (based on the query containing the DueDate field),
>> apply conditional formatting to the control bound to the DueDate field. 
>> Set
>> the condition to Field Value Is less than Date(), and select the text
>> colour/background colour you want.
>>
>> HTH,
>>
>> Rob
>>
>> "arch252" <arch252@discussions.microsoft.com> wrote in message
>> news:9F8D2485-C1BB-41DB-8B94-E5DA949F87C2@microsoft.com...
>> >I have a field that shows the date I sent a notice to someone.  I want 
>> >to
>> > create a field that automatically shows the date the notice is due 
>> > back,
>> > which is 10 days from the sent date.  I would also like the field to 
>> > show
>> > up
>> > in red when the due date is past.
>>
>>
>> 


0
Rob
7/10/2007 4:21:32 AM
Reply:

Similar Artilces:

restoring GP 10.0 32-bit into GP 10.0 64-bit
Hello: We are going to install GP 8.0 on a 32-bit server in-house and restore a client's 8.0 data into these SQL databases. Pretty cut and dry. Next, we will upgrade this in-house server from 8.0 to 10.0. Continuing, we will install GP 10.0 on the client's new 64-bit server. Finally, we will restore the upgraded 10.0 databases from our in-house 32-bit server to the client's 64-bit server. Here's the question. Is there anything "wrong", from a technical standpoint, in restoring SQL databases from a 32-bit environment into a 64-bit environment? Thanks! chil...

Money Changes Transaction Dates
Hi all, How can I stop Money2004 from changing my transaction dates that I have already entered into the register? This happens when I accept/match downloaded transactions from my bank. Thanks! Well, I think I just answered my own question. I found a check box to untick under online options to fix this, but I will post back if necessary. "rustyfender04" <rustyfender1@hotmail.com> wrote in message news:eY3zaLITHHA.2124@TK2MSFTNGP06.phx.gbl... > Hi all, > > How can I stop Money2004 from changing my transaction dates that I have > already entered into t...

adding months to an inputted date
I need a function that will take a date that a user has typed in a different cell and will then add two months to the date. For instance, if I type "2/12/05" in B1, then I want C2 to be: "4/12/05". Thank you for any help that you may be able to give. Logan =DATE(YEAR(B1),MONTH(B1)+2,DAY(B1)) however what do you want the date to be in C2 if B1 is 01/30/05? Regards, Peo Sjoblom "BLW" wrote: > I need a function that will take a date that a user has typed in a different > cell and will then add two months to the date. For instance, if I type >...

Convert date to months
I have a database that has multiple dates in it. What I am trying to do is in one field I have a date and I am want that date to convert to months in another field. Can anyone help me on this. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200707/1 are you trying to find months between dates? or convert 4 to April? Look at format function for ways to display months, datediff function for months difference.. "bohon79 via AccessMonster.com" <u35329@uwe> wrote in message news:74ac2f3f40ce9@uwe... >I have a database ...

Last day of posting via Microsoft server
Well, I guess this is the last day of posting via the Microsoft server. http://www.teranews.com/ Your own free account ($3.95 one time setup fee) that allows posting or use public.teranews.com without an account (no posting & speed capped). You can use any standard news client you choose to read and post to any newsgroup. Or Google it: http://groups.google.com/groups/search?hl=en&q=microsoft.public.win98.gen_discussion&qt_s=Search God Bless America, Bill O|||||||O mailto:BillHughes@billhughes.com http://www.billhughes.com/jeep_bookmark.htm "D...

GP 10 and Terminial Server 2008
My customer is having issues with GP 10 sp4 and Terminal Server 2008. Whenever they are in one of the Navigation panes (Purchasing) and they choose an option in the navigation Pane other than the PURCHASING (ie VENDORS) they are kicked out of GP. Has anyone come across this? ...

Sales for Outlook Installation 10-24-05
I'm having trouble installing CRM sales for outlook. The installation errors literally on the point of finishing. The message is "Setup has failed because of an error. Setup was unable to install the .msi file for microsoft crm sales for outlook". I have searched the knowledge base and newsgroups to find any info on this matter but at the moment I have struggled to find any signifcant information. The pc used is running XP pro SP2. Thanks for any assistance in advance ...

Calendar/Dates Help
Hi All, I have an excel spreadsheet that lists every date in the year, with a particular code in the next cell. IE: Monday 3/01/2005 11M 22M 32M Tuesday 4/01/2005 11T 22T 32T Wednes 5/01/2005 11W 22W 32W Thursday 6/01/2005 11H 22H 32H Friday 7/01/2005 11F 22F 32F Saturday 8/01/2005 11S 22S 32S Sunday 9/01/2005 11N 22N 32N Monday 10/01/2005 11M 21M 33M Tuesday 11/01/2005 11T 21T 33T Wednes 12/01/2005 11W 21W 33W Thursday 13/01/2005 11H 21H 33H Friday 14/01/2005 11F 21F 33F What I need is to be able to search by the code eg "33T" and have all the dates listed for ...

Date comparison better method
Select col1, col2 from TableName where DateColumn BETWEEN '2010-06-01' and '2010-06-17 23:59:59.997' Select col1, col2 from TableName where WHERE DateColumn >= '2010-06-01' AND DateColumn < '2010-06-17 23:59:59.997' I am seeing in a project both the above methods of data range filering is happening in different SPs. I am trying to understand which is the better method of comparing two date values and why? [Btw i know BETWEEN considers both the upper and lower limit] Regards Pradeep I would say the following is the better approach: ...

Extracting Time from a cell that has both the date and the time
Hi Folks, I could do with some help here please. I am trying to extract the time only from a cell that has both the date and the time. Can anyone suggest a solution? Thanks in advance. :confused: -- Hani Muhtadi ------------------------------------------------------------------------ Hani Muhtadi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26794 View this thread: http://www.excelforum.com/showthread.php?threadid=466177 If you just wish the time to display, set the cell format to Time. If you wish to use the time portion then =A1-Int(A1) will give you...

installing GP 10.0 on workstations only
Hello: I understand that you can install GP 10.0 and forego installation of the application on the server. If you choose to go that route, then how do you path to SQL during the first-time run of GP Utilities when you create the company databases? I mean, if you install GP on the first workstation without installing GP on the server at all, you have to tell GP the path to the database (in the Database Setup window). Can you use UNC pathing in that window or do you have to use mapped drives? Also, what sort of security do you need to create the databases from the workstation? Domai...

Macro Question #10
Hi, I am working with a text file that I imported into Excel. It is a transactional report of sales by customer. The data will have the customer number and name spread out over three cells. What I want to do is to create a macro that will contatenate all three cell and then perform a paste special and paste them into another cell in another column. Since the number of transactions by customer can vary the customer name and number can appear anywhere on the page. I created a macro to do this using the first customer which appears in cell B5 through D5. The next customer appears...

Get Start date of Week number and Year
I’d like to build the following expression in my query GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate])) So if EnteredDate = 11/3/2009 the function would return 11/1/2009 But GetStartWeekNumber does not exist as an Access Built-In Function. Is there another way to do this as an expression in a query? I’m not familiar with creating my own functions. Thanks. That would depend on how you define the start of the week... One option would be to get the day-of-week number of the date (in my system/setup, Monday is day 2), then subtract one less than that...

Extender problem with GP 10
Hi all, I setup a Dynamics GP 10 system for a customer. The system is used from HRM staff. I used Extender to add a number of extender windows and detail windows. On the Sever all users can access the extender windows but when they login on a separate terminal server (with a client installation) the Extender windows are not accessible. Only the Extender Detail Windows can be seen. When trying to go to the windows all users including sa get the following message: "Not all required fields have been entered for the window <window name>." I went in the extender and opened ...

How do I sort data by date excluding time
I would like to sort a database by date and transaction type. The problem is the data sorts by time, even though the field is formatted to show date only, with the transaction type not sorting within the date because it shows up in the time order. How do I get rid of the time? If date/time column is A then you have to use an empty helper column filled with formula =INT(A2) and sort by it. -- Regards! Stefi „markd” ezt írta: > I would like to sort a database by date and transaction type. The problem is > the data sorts by time, even though the field is for...

Prevent excel from changing numeric data ranges into dates?
I want to prevent excel from changing my numeric data into dates (ie. 10-12, into 12-Oct). I want to import specific data ranges, not a date values. Whenever I paste or type a possible date, like 10-12, excel assumes I am writing Oct. 12, 2004. Then, Excel formats the cell into a Custom d-mmm format (see scrn shot @ www.vd4.org/excel_issue.gif). If I change the Custom format to General, excel converts the date into the DATE'S value (ie. 12-Oct, into 38272; which I think is the # of days since 1900?). How can I *prevent* excel from changing my data ranges into dates (ie keep 10-12 ...

Counting dates
I am trying to count the number of dates in a column that appear b month and year. For instance, I some that are 11/23/2003 and 11/24/200 and 11/21/2002. How can I makew a formula that will count the instance of 11/2003 -- Message posted from http://www.ExcelForum.com =SUMPRODUCT((YEAR(A1:A100)=2004)*(MONTH(A1:A100)=11)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "jdavis3874 >" <<jdavis3874.14770b@excelforum-nospam.com> wrote in message news:jdavis3874.14770b@excelforum-nos...

Cannot view inbox by date
After a successful test of an Exchange 2003 High Availability product last night a few users have reported issues in Outlook this morning. They cannot view/arrange their Inbox by Date. We have tried /cleanviews switch to no avail. All other views work fine. Any ideas so I can prevent this happening again? Thanks PocketJacks <dallano@hotmail.com> wrote: > After a successful test of an Exchange 2003 High Availability product > last night a few users have reported issues in Outlook this morning. > They cannot view/arrange their Inbox by Date. What happens when they try? -- ...

Recognise a payee with date and time in it
Hi Everyone Each time I withdraw cash from my bank account, it puts it in my statement as: 21Mar 10.13 Well't Sq for example. So each time I withdraw cash, the date and time are going to be different. I want money to recognise on my downloaded statements that any statement line with Well't Sq in it, means a cash withdrawal - i.e. ignore the date and time as obviously that will be different everytime I take cash out. Is there anyway of getting Money to recognise part of the payee rather than thinking all my cash withdrawals are different payees? Thanks! Hannah Quicken doe...

How Many Days Since... Formula -Help
Hi, can anyone help? I have a spreadsheet tracking several different Departments in our company and how often they have an accident. I need to track how many days they go without an accident. So if Dept A had their latest accident yesterday. And if someone opens the spreadsheet in 3 days, it should say "4 days w/o an accident for Dept A. I'm sure this can be done, I just have no idea how :roll: Thanks in advance, Scotty Assuming 8/5/2003 is the start date... =TODAY()-"8/8/2003"&" Days w/o an accident for Dept A" Lance >-----Original Message----- &...

date
Hello When I enter "1-Nov" cell changes to 11-Jan. How do I get the cell to stay as 1-Nov. Thanks suggest format as d-mmm and use 11/1 as your entry or use text to do it your way. -- Don Guillett SalesAid Software donaldb@281.com "Jack" <dmmclean@rogers.com> wrote in message news:cICdnSnW16n91RPcRVn-jA@rogers.com... > Hello > When I enter "1-Nov" cell changes to 11-Jan. How do I get the cell to stay > as 1-Nov. > > Thanks > > On Sun, 7 Nov 2004 11:07:55 -0500, "Jack" <dmmclean@rogers.com> wrote: >Hello >...

Counting based on Date Range
I have a an Excel spreadsheet that is linked to an Access Databas Table. Each day, the database is updated with new information including the date. I want to set up an automated Excel report tha will count the number of entries for a date range (monthly). In short I want to reference an entire column (the date column), and have th spreadsheet be able to count how many entries occurred in Jan 04, Fe 04, etc... I've played with counta and countif, but have had little success. Thoughts? Thankx, C -- Message posted from http://www.ExcelForum.com CT Here's an example using SUMIF,...

Trying to change x-axis date range
I have a scatter chart with dates on the x-axis and data on the y. When trying to change the range of my x-axis, I right click on the x-axis, click "format axis" and for "Minimum" and "Maximum" I click "Fixed" but it will only let me enter dates in the number format (i.e. 39983 instead of 6/19/2009). This is really annoying because I am constantly changing ranges to zoom in on the data and need to think about what the number format would be. I didn't have this problem with the earler version of excel (2003), but it started as soon as my offi...

Lock sets of cells in a row and sort by date linked to each set
I have a set of qualifications linked to a name that need to be presented in a row A = Name B = Qual Type 1 C = Expiry Date (eg 29/01/12) D = Qual Type 2 E = Expiry Date (eg 17/12/12) F = Qual Type 3 G = Expiry Date (eg 25/06/11) H = Qual Type 4 (has no expiry date) I want to sort each qualification into 'date expiry' order keeping it connected to the relevant Qual Type. Therefore, I want Qual Type 3 with it's exipiry date to move into Column B & C, Qual Type 1 with it's exipiry date to move into Column D & E and Qual Type 2 with it's exipiry da...

Need help with a date function formula
I have an excel sheet header which includes a date, the date is t represent yesterday's date ( =Today()-1 ), which then would come int the spread sheet as August 8th, 2005, assuming today was August 9th. The problem is on days like Monday when the previous day was sunday how can I make the formula output the Friday before the Monday instea of Sunday ...example on Monday, August 11th, the formula would read August 10th, I need it to read August 8th...? Please help, thank -- KA ----------------------------------------------------------------------- KAA's Profile: http://www.excelforum...