#### need to make a formula that would add a field value to current dat

```I have made a form in which I input different values.  On of the values is
(How Many Days).  Now I need to a assign a default value, or expression (not
sure which way to go about this) that will take the date value for (Date) and
add the value (How Many Days)

I figured that the formula should read =sum([Date]+[How Many Days])

But that is not giving me any results, thanx for your help in advance
```
 0
Utf
9/18/2007 9:08:06 AM
access.forms 6864 articles. 2 followers.

10 Replies
1027 Views

Similar Articles

[PageSpeed] 4

```=DateDiff("d", Date(), [How Many Days])

--
Wayne
Manchester, England.

"J Man" wrote:

> I have made a form in which I input different values.  On of the values is
> (How Many Days).  Now I need to a assign a default value, or expression (not
> sure which way to go about this) that will take the date value for (Date) and
> add the value (How Many Days)
>
>  I figured that the formula should read =sum([Date]+[How Many Days])
>
> But that is not giving me any results, thanx for your help in advance
```
 0
Utf
9/18/2007 9:54:01 AM
```Wayne thanx for your response, not sure what I'm doing wrong.  I tried your
formula in both default value and event with no result.

Jerry

"Wayne-I-M" wrote:

> =DateDiff("d", Date(), [How Many Days])
>
>
> --
> Wayne
> Manchester, England.
>
>
>
> "J Man" wrote:
>
> > I have made a form in which I input different values.  On of the values is
> > (How Many Days).  Now I need to a assign a default value, or expression (not
> > sure which way to go about this) that will take the date value for (Date) and
> > add the value (How Many Days)
> >
> >  I figured that the formula should read =sum([Date]+[How Many Days])
> >
> > But that is not giving me any results, thanx for your help in advance
```
 0
Utf
9/18/2007 12:16:02 PM
```Can you explain what you are trying to do

the formula I gave will give the number of days from today to the value of
the control called How Many Days.

Are you trying to get this or something else.

--
Wayne
Manchester, England.

"J Man" wrote:

> Wayne thanx for your response, not sure what I'm doing wrong.  I tried your
> formula in both default value and event with no result.
>
> Jerry
>
> "Wayne-I-M" wrote:
>
> > =DateDiff("d", Date(), [How Many Days])
> >
> >
> > --
> > Wayne
> > Manchester, England.
> >
> >
> >
> > "J Man" wrote:
> >
> > > I have made a form in which I input different values.  On of the values is
> > > (How Many Days).  Now I need to a assign a default value, or expression (not
> > > sure which way to go about this) that will take the date value for (Date) and
> > > add the value (How Many Days)
> > >
> > >  I figured that the formula should read =sum([Date]+[How Many Days])
> > >
> > > But that is not giving me any results, thanx for your help in advance
```
 0
Utf
9/18/2007 12:44:03 PM
```If you want to know the date a certain number of days from the current date,
try DateAdd.  For instance, as the control source of a text box:

The default value for a bound control (one that is linked to a table field)
is the value that is applied when the record is new.  Default value for an
unbound control on a form is the value applied when the form is first
opened.  If you change the value, it will retain the new value as you
navigate through the records.  I can't see that it has much use in an
unbound control.  In any case, you aren't looking for default value.
I'm not sure what you mean when you say you tried the expression "in ...
event", but if you are trying to use an event procedure such as the form's
Current event you either need to use a function as the Current event, or you
need to add code to the Event Procedure.  For instance:
However, using the expression above as the control source of a text box
should do what you need.

"J Man" <JMan@discussions.microsoft.com> wrote in message
news:DAE5D30A-2350-4C72-B5DC-8597991FC3DC@microsoft.com...
> Wayne thanx for your response, not sure what I'm doing wrong.  I tried
> your
> formula in both default value and event with no result.
>
> Jerry
>
> "Wayne-I-M" wrote:
>
>> =DateDiff("d", Date(), [How Many Days])
>>
>>
>> --
>> Wayne
>> Manchester, England.
>>
>>
>>
>> "J Man" wrote:
>>
>> > I have made a form in which I input different values.  On of the values
>> > is
>> > (How Many Days).  Now I need to a assign a default value, or expression
>> > (not
>> > sure which way to go about this) that will take the date value for
>> > (Date) and
>> > add the value (How Many Days)
>> >
>> >  I figured that the formula should read =sum([Date]+[How Many Days])
>> >
>> > But that is not giving me any results, thanx for your help in advance

```
 0
BruceM
9/18/2007 1:13:35 PM
```On Tue, 18 Sep 2007 02:08:06 -0700, J Man <J Man@discussions.microsoft.com>
wrote:

>I have made a form in which I input different values.  On of the values is
>(How Many Days).  Now I need to a assign a default value, or expression (not
>sure which way to go about this) that will take the date value for (Date) and
>add the value (How Many Days)
>
> I figured that the formula should read =sum([Date]+[How Many Days])
>
>But that is not giving me any results, thanx for your help in advance

=DateAdd("d", [How Many Days], [Date])

Note that Date is a reserved word for the built in "today's date" function,
and that you should not use it as a field or controlname.

John W. Vinson [MVP]
```
 0
John
9/18/2007 6:43:12 PM
```In my response I had taken the OP to mean adding days to the current date,
but upon rereading it does look as if Date is a field name, in which case my
expression will not work as intended.

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:f070f3978t6057etnskekhnl1582bt4flt@4ax.com...
> On Tue, 18 Sep 2007 02:08:06 -0700, J Man <J
> Man@discussions.microsoft.com>
> wrote:
>
>>I have made a form in which I input different values.  On of the values is
>>(How Many Days).  Now I need to a assign a default value, or expression
>>(not
>>sure which way to go about this) that will take the date value for (Date)
>>and
>>add the value (How Many Days)
>>
>> I figured that the formula should read =sum([Date]+[How Many Days])
>>
>>But that is not giving me any results, thanx for your help in advance
>
>
> =DateAdd("d", [How Many Days], [Date])
>
> Note that Date is a reserved word for the built in "today's date"
> function,
> and that you should not use it as a field or controlname.
>
>             John W. Vinson [MVP]

```
 0
BruceM
9/18/2007 7:33:07 PM
```Hi John,

Thanx for your post.  I can change the field/control name from date to
something else, but in reality I only need it to add value[How Many Days] to
the current date.  But just in case I ever need it to add to the value[Date]
I guess I will change the name.  However I did try to use your formula and
still am having no luck.  Just to be clear should I be using this formula in
the 'default value' or somewhere else?  Thanx for your time.  Usually with a
little work at it I can get these to work, but I've been trying to figure
this out for sometime and nothing is working.  I'm sure its an easy fix, but
I'm just not getting it.

Once again thanx
Jerry

"John W. Vinson" wrote:

> On Tue, 18 Sep 2007 02:08:06 -0700, J Man <J Man@discussions.microsoft.com>
> wrote:
>
> >I have made a form in which I input different values.  On of the values is
> >(How Many Days).  Now I need to a assign a default value, or expression (not
> >sure which way to go about this) that will take the date value for (Date) and
> >add the value (How Many Days)
> >
> > I figured that the formula should read =sum([Date]+[How Many Days])
> >
> >But that is not giving me any results, thanx for your help in advance
>
>
> =DateAdd("d", [How Many Days], [Date])
>
> Note that Date is a reserved word for the built in "today's date" function,
> and that you should not use it as a field or controlname.
>
>              John W. Vinson [MVP]
>
```
 0
Utf
9/19/2007 1:58:01 AM
```Thanx Wayne

What I'm trying to do is as follows

I have a form in which I input a value for [How Many Days]
[Date] is defaulted to current date, but can also be changed if say I'm
backdating or entering info for the future.  What I need is a formula that
will take the value [How Many Days] and add it to [Date] but so far nothing
is working.  In excel this is so much easier then in access.

Thanx for your time,
Jerry

"Wayne-I-M" wrote:

> Can you explain what you are trying to do
>
> the formula I gave will give the number of days from today to the value of
> the control called How Many Days.
>
> Are you trying to get this or something else.
>
>
> --
> Wayne
> Manchester, England.
>
>
>
> "J Man" wrote:
>
> > Wayne thanx for your response, not sure what I'm doing wrong.  I tried your
> > formula in both default value and event with no result.
> >
> > Jerry
> >
> > "Wayne-I-M" wrote:
> >
> > > =DateDiff("d", Date(), [How Many Days])
> > >
> > >
> > > --
> > > Wayne
> > > Manchester, England.
> > >
> > >
> > >
> > > "J Man" wrote:
> > >
> > > > I have made a form in which I input different values.  On of the values is
> > > > (How Many Days).  Now I need to a assign a default value, or expression (not
> > > > sure which way to go about this) that will take the date value for (Date) and
> > > > add the value (How Many Days)
> > > >
> > > >  I figured that the formula should read =sum([Date]+[How Many Days])
> > > >
> > > > But that is not giving me any results, thanx for your help in advance
```
 0
Utf
9/19/2007 3:04:04 AM
```My earlier posting on this topic explained that the expression goes into the
Control Source.  In that same posting I explained Default Value, and why it
isn't what you want.  I also mentioned that the expression could be used in
an Event Procedure.  I will say that I had misunderstood that Date is a
field (or control), but if you surround Date with square brackets you should
be OK, even though it is not a recommended procedure to use the name of a
function for a field name.  It can cause problems with code, among other
things.  That is why John mentioned it.  You can use Default Value to set
the value of the Date field to today's date, assuming that you are storing
the information.

"J Man" <JMan@discussions.microsoft.com> wrote in message
> Hi John,
>
> Thanx for your post.  I can change the field/control name from date to
> something else, but in reality I only need it to add value[How Many Days]
> to
> the current date.  But just in case I ever need it to add to the
> value[Date]
> I guess I will change the name.  However I did try to use your formula and
> still am having no luck.  Just to be clear should I be using this formula
> in
> the 'default value' or somewhere else?  Thanx for your time.  Usually with
> a
> little work at it I can get these to work, but I've been trying to figure
> this out for sometime and nothing is working.  I'm sure its an easy fix,
> but
> I'm just not getting it.
>
> Once again thanx
> Jerry
>
> "John W. Vinson" wrote:
>
>> On Tue, 18 Sep 2007 02:08:06 -0700, J Man <J
>> Man@discussions.microsoft.com>
>> wrote:
>>
>> >I have made a form in which I input different values.  On of the values
>> >is
>> >(How Many Days).  Now I need to a assign a default value, or expression
>> >(not
>> >sure which way to go about this) that will take the date value for
>> >(Date) and
>> >add the value (How Many Days)
>> >
>> > I figured that the formula should read =sum([Date]+[How Many Days])
>> >
>> >But that is not giving me any results, thanx for your help in advance
>>
>>
>> =DateAdd("d", [How Many Days], [Date])
>>
>> Note that Date is a reserved word for the built in "today's date"
>> function,
>> and that you should not use it as a field or controlname.
>>
>>              John W. Vinson [MVP]
>>

```
 0
BruceM
9/19/2007 11:12:35 AM
```BruceM,

Thanx for all your help, it all works now.
I could have sworn I tried it the other day when you first sent it, with no
success.  But it all works now, your a life saver.  I was ready to rip my
hair out.  lol

Thanx to everyone else for their help also.

Jerry

"BruceM" wrote:

> My earlier posting on this topic explained that the expression goes into the
> Control Source.  In that same posting I explained Default Value, and why it
> isn't what you want.  I also mentioned that the expression could be used in
> an Event Procedure.  I will say that I had misunderstood that Date is a
> field (or control), but if you surround Date with square brackets you should
> be OK, even though it is not a recommended procedure to use the name of a
> function for a field name.  It can cause problems with code, among other
> things.  That is why John mentioned it.  You can use Default Value to set
> the value of the Date field to today's date, assuming that you are storing
> the information.
>
> "J Man" <JMan@discussions.microsoft.com> wrote in message
> > Hi John,
> >
> > Thanx for your post.  I can change the field/control name from date to
> > something else, but in reality I only need it to add value[How Many Days]
> > to
> > the current date.  But just in case I ever need it to add to the
> > value[Date]
> > I guess I will change the name.  However I did try to use your formula and
> > still am having no luck.  Just to be clear should I be using this formula
> > in
> > the 'default value' or somewhere else?  Thanx for your time.  Usually with
> > a
> > little work at it I can get these to work, but I've been trying to figure
> > this out for sometime and nothing is working.  I'm sure its an easy fix,
> > but
> > I'm just not getting it.
> >
> > Once again thanx
> > Jerry
> >
> > "John W. Vinson" wrote:
> >
> >> On Tue, 18 Sep 2007 02:08:06 -0700, J Man <J
> >> Man@discussions.microsoft.com>
> >> wrote:
> >>
> >> >I have made a form in which I input different values.  On of the values
> >> >is
> >> >(How Many Days).  Now I need to a assign a default value, or expression
> >> >(not
> >> >sure which way to go about this) that will take the date value for
> >> >(Date) and
> >> >add the value (How Many Days)
> >> >
> >> > I figured that the formula should read =sum([Date]+[How Many Days])
> >> >
> >> >But that is not giving me any results, thanx for your help in advance
> >>
> >> Use DateAdd:
> >>
> >> =DateAdd("d", [How Many Days], [Date])
> >>
> >> Note that Date is a reserved word for the built in "today's date"
> >> function,
> >> and that you should not use it as a field or controlname.
> >>
> >>              John W. Vinson [MVP]
> >>
>
>
>
```
 0
Utf
9/19/2007 12:14:00 PM

Similar Artilces:

problem with index formula
The following fomula is contained in cell C16 and returns the correct values: =VLOOKUP(B6,'Active Clients'!\$B\$2:\$K\$954,10,FALSE) The following formula is contained in cell L18 and uses the result in C16 as a lookup value in the first "match": =INDEX(L21:M44,MATCH(C16,L21:L44,1),MATCH(M16,L21:M21,-1)) My promlem is that when I try to match on cell C16 my index formula returns an error. However, when I change the index formula to reference another cell (let's say cell D16) and I type in the value shown in cell C16, the index formula works fine. I think my problem has ...

Delete table values automatically
Hi All, I want to create a routine that will clean all records from a specific table automatically once a week (if possible) or each time that the MDB opens up. Any thoughts? Thanks a lot in advance Hi, the code to empty a table is simple Public Sub EmptyTable() Dim strSQL as String Dim db as DAO.Database Set db = dbengine(0)(0) strSQL = "DELETE FROM TableName" db.Execute strSQL, dbFailOnError Set db = Nothing Exit Sub the above code goes in a standard module when your database opens, you call the routine EmptyTable Jeanette Cunningham "Lp12" <Lp12@discussion...

How do I display a formula calculation in plot area of a chart?
I want to add a text box in the chart that returns the value from a formula that was entered on the data page. The formula value is not related to any series value. WiseGuyOne: You need to build your text expression in a cell, then insert that expression to the chart. First, to build your text expression: 1. Select your cell 2. Enter = first to let Excel =know you are adding a formula 3. You can now enter text within quotes and cell values .. here's an example =" Yesterdays average temperature was " & sheet1!\$a\$3 Note that the & is used to add ...

IF FORMULAS #2
Looking for help...... In column A1:A16, I have names. In B1:B16 I will assign random numbers to those names from 1-16. In another part of the sheet I have 16 columns lets say D:S, (D) being column 1 and (S) being column 16. when I assign a number in column B, I want the name to the left to appear above the appropiate column. EX, If B1 is 16 then the name in A1 will appear above column S. I am using Excel 2007 Windows Vista. I find this site to be the place for answers,everyone is great. Thanks in advance. D1: =INDEX(\$A\$1:\$A\$16,MATCH(COLUMN()-3,\$B\$1:\$B\$16,0)) Fill to S1. "JOHN...

The value become wrong when get value by using AppleScript while other worksheet is active.
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I just want to send a bug report. Summary: The value become wrong when get value by using AppleScript while other worksheet is active. Description: 1. Create an AppleScript file with below script. tell application "Microsoft Excel" activate set OriginalWorkbook to workbook "Workbook1" set OriginalData to string value of column 1 of row 1 of worksheet 1 of OriginalWorkbook end tell 2. Launch Excel 2008 and create a Workbook called "Workbook1". 3. Enter time data "11...

Choose highest value in a row
How do I tell excel to choose the highest value in a range of cells? A B C D E Highest 1 3 2 7 5 7 help please. =max(a1:e1) will return the highest value. sohum.shah@gmail.com wrote: > > How do I tell excel to choose the highest value in a range of cells? > > A B C D E Highest > 1 3 2 7 5 7 > > help please. -- Dave Peterson =MAX(A1:E1) returns 7 in your example. Gord Dibben MS Excel MVP On 23 Oct 2006 10:47:09 -0700, sohum.shah@gmail.com wrote: >How do I tell excel to choose the highest valu...

Report generated from text box fields
hello. i have a 3 column report. The 3 products compared on the report are assigned when the report opens and asks the user for the ID.. is there are way to create 3 unbound text fields and have the report compare the IDs of the products listed in the text box.. i have done something similar that searches based on what was entered in a text box, but wasnt sure how to have the report pick up three different text boxes.... this is an example of what i have in another application that opens up a query Like "*" & [forms]![SEARCHMENU]![text37] & "*" -- Message po...

Need to separate multiple numbers in one cell
I have multiple number in one cell and i need to somehow put each number into one separate cell for each number: I have : column A row1 2 5 8 10 I want: column B column C column D column E row1 2 5 8 10 How do I do it on Excel 2007? thanks EggHeadCafe - Software Developer Portal of Choice Map Stored Procedure Output To Class Properties http://www.eggheadcafe.com/tutorials/aspnet/2853a1aa-5db7-40d0-9cde-46847fa770ef/map-stored-procedure-outp.aspx Hi, Use Data > Text to columns and specify the delimiter as space ...

Need Formula to Average Monthly Increase
I have a spreadsheet where there are columns for each month of the year Each cell contains a number of loan submissions that graduall increases per month as we gain more business from each mortgage vendor I need a formula that can look at multiple cells in a row (i.e Jan-Dec) per vendor and then give me a result that shows the averag monthly percentage increase factor in submissions... Example: MONTH Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec A SUBS 1 5 18 29 30 40 57 85 150 275 ......... ..I will be using this data fo forecasting future submissions ...

Going Back in History formula?
How can I express a formula that would back 300 rows and look at every other row for the 3 cells with random single digits from 0 to 9 and calculate, the how many rows back since the oldest 0-9 digit occurred in cell 1 answer, cell 2 answer and 3 answer, then calculate which 0-9 digit has not occurred in cell 1, cell 2 and cell 3 the longest and displays how many row back since the last occurrence. So that would be 4 separate formulas ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.Exc...

How do I enter a time formula into a worksheet that determines if.
How do I, set about entering a formula within a worksheet that determines if the Time is greater or less that the time in a certian cell; eg =if(now()>K17,"then display this","") K17 holding the Value 08:30 AM formated to time =IF(MOD(NOW(),1)>K17,"then display this","") HTH Jason Atlanta, GA >-----Original Message----- >How do I, set about entering a formula within a worksheet that determines if >the Time is greater or less that the time in a certian cell; >eg =if(now()>K17,"then display this","") ...

Existing formula
I'm working with a spreadsheet that was built by someone else and cam across a formula in some of the cells that I'm puzzled by. The formul begins with "=", but the next character is "+", then a reference t another worksheet and cell. The "+" is what I'm wondering about, haven't seen it used in this context before, can someone help me ou here? Some examples of what I'm looking at are: =+Inputs!G9 =+H17-R20 Thanks, wwhit -- Message posted from http://www.ExcelForum.com It's an unnecessary character that is there because the person ...

copy value in cell above to cell below for a whole column
I have a spreadsheet that was saved from a report. this report only lists the employee name once. There is a way to copy the value of the cell above to the cell below. I can get as far as highlighting the blank cells with the Go To function, then I can not remember the steps to tell excel to copy the data into the blank cells. Can anyone help? Ctrl+D will copydown the value of the top cell to the highlighted cells below. "tarthur" wrote: > I have a spreadsheet that was saved from a report. this report only lists > the employee name once. There is a way to copy th...

formulas changing when emailed
At work, I have created a spreadsheet that has links to workbooks in a user drive..called the "U: drive." When I transfer this excel project to others via email, I make sure that the other workbooks also have the same name in their U: drive as well. However, whenever I email it... I have to change many of the formulas at their computers bc the references in my formulas change to a really long string. I have tried using "CNTL+H" to replace the "=" to a "+" but it doesn't work for me bc I have "+" "=" elsewhere in my formulas....

CRM 4.0
Hi! We have just made a migration from CRM 3.0 to 4.0, and everything went well, except one thing - field Resources is disabled (like it's in read-only mode, but it's not!), and we can't find the way to enable it... Any suggestions....? Best regards, Ivica I'm sure this is a bug - we had it on the fax entity with "to" - Team please take note I modifed the xml - after i look ed at a clean xml to see the diffence Pete "Ivica Ivancic" <ivica.ivancic@infinitas.hr> wrote in message news:5534F8F2-930B-45F1-A503-4D4DBA092E0F@microsoft.com....

Validation Rule for IBAN Account number in MS Access Form field
Hi, In my table (and the form field linked to it) I have a field for the IBAN account number, which must be 2 Upper case characters A to Z, followed by 18 manadatory digits, and up to 28 total. So in my input mask I have this: >LL00\-0000\-0000\-0000\-0000\-9999\-9999;; and it works fine. But when the user enters less than 20 characters total (including the two letters at the start and at least 18 digits) Access gives a very unfriendly ''The value you entered isn't appropriate for the input mask '>LL00\-0000\-0000\-0000\-0000\-9999\-9999;;' specified for thi...

Need mm-dd-yy for date but helpless
I am from India and by default, Vista accords dd-mm-yy for short dates, which is creating problem in Excel. I tried to look for options but in Vista, Regional settings, there is just no setting for having mm-dd-yy. What should I do? "Ghitorni" <padhye.m@gmail.com> wrote: >I am from India and by default, Vista accords dd-mm-yy for short dates, >which is creating problem in Excel. I tried to look for options but in >Vista, Regional settings, there is just no setting for having mm-dd-yy. What >should I do? I don't have Vista in front of me, ...

How to add customer's totalSales without transaction?
Some customers transaction record are lost. I only have their totalSales. Now I want to add these customers to rms with their totalSales. How can I do it? Jerry wrote: > Some customers transaction record are lost. I only have their totalSales. Now > I want to add these customers to rms with their totalSales. How can I do it? > You can add the customers and their totalSales only from QS Administrator with SQL query.. example: UPDATE Customer SET TotalSales = <AMOUNT> WHERE AccountNumber = <CustomerAccountNumber> bye antonio ...

Can anyone suggest good add-ins that support CHAID and CART clustering methodologies? ...

How to Add Streaming Video To Your Website 04-15-09
http://www.abcsun.net ...