Adding input to date

Hopefully the last time I will have to bug everyone!

Within the BMP database I am working on, I am trying to find a way to
calculate the next inspection date for each BMP, given the previous
inspection date [tblBMP.InspDate] and the inspection period
[tblBMP.InspPeriod] which is in years (e.g. a given BMP is inspected
every 3 years). I've seen examples of simply adding a static value to
the date, but how would I go about about using an input number and
adding it to the year of the previous inspection date?

Thanks again everyone!

0
stephen
5/25/2007 6:30:35 PM
access.queries 6343 articles. 1 followers. Follow

13 Replies
946 Views

Similar Articles

[PageSpeed] 5

If all are in years then use the DateAdd function.
    DateAdd("yyyy", [tblBMP.InspPeriod],  [tblBMP.InspDate])

-- 
KARL DEWEY
Build a little - Test a little


"stephen.belyea@gmail.com" wrote:

> Hopefully the last time I will have to bug everyone!
> 
> Within the BMP database I am working on, I am trying to find a way to
> calculate the next inspection date for each BMP, given the previous
> inspection date [tblBMP.InspDate] and the inspection period
> [tblBMP.InspPeriod] which is in years (e.g. a given BMP is inspected
> every 3 years). I've seen examples of simply adding a static value to
> the date, but how would I go about about using an input number and
> adding it to the year of the previous inspection date?
> 
> Thanks again everyone!
> 
> 
0
Utf
5/25/2007 6:58:02 PM
On May 25, 2:58 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
wrote:
> If all are in years then use the DateAdd function.
>     DateAdd("yyyy", [tblBMP.InspPeriod],  [tblBMP.InspDate])
>
> --
> KARL DEWEY
> Build a little - Test a little
>
> "stephen.bel...@gmail.com" wrote:
> > Hopefully the last time I will have to bug everyone!
>
> > Within the BMP database I am working on, I am trying to find a way to
> > calculate the next inspection date for each BMP, given the previous
> > inspection date [tblBMP.InspDate] and the inspection period
> > [tblBMP.InspPeriod] which is in years (e.g. a given BMP is inspected
> > every 3 years). I've seen examples of simply adding a static value to
> > the date, but how would I go about about using an input number and
> > adding it to the year of the previous inspection date?
>
> > Thanks again everyone!

Thanks for the response Karl.

[tblInspection.InspDate] is in date format, but
[tblInspection.InspPeriod] will simply be a single digit number. As an
example I have a date of 3/13/1999 for the last inspection, and the
inspection period ends up being 3 years. I want to use data from a
Number field to change the value of the year in the Date field. Is
there any way to do this?

0
stephen
5/25/2007 7:43:34 PM
What I posted does exactly that.   Did you try it? 


-- 
KARL DEWEY
Build a little - Test a little


"stephen.belyea@gmail.com" wrote:

> On May 25, 2:58 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
> wrote:
> > If all are in years then use the DateAdd function.
> >     DateAdd("yyyy", [tblBMP.InspPeriod],  [tblBMP.InspDate])
> >
> > --
> > KARL DEWEY
> > Build a little - Test a little
> >
> > "stephen.bel...@gmail.com" wrote:
> > > Hopefully the last time I will have to bug everyone!
> >
> > > Within the BMP database I am working on, I am trying to find a way to
> > > calculate the next inspection date for each BMP, given the previous
> > > inspection date [tblBMP.InspDate] and the inspection period
> > > [tblBMP.InspPeriod] which is in years (e.g. a given BMP is inspected
> > > every 3 years). I've seen examples of simply adding a static value to
> > > the date, but how would I go about about using an input number and
> > > adding it to the year of the previous inspection date?
> >
> > > Thanks again everyone!
> 
> Thanks for the response Karl.
> 
> [tblInspection.InspDate] is in date format, but
> [tblInspection.InspPeriod] will simply be a single digit number. As an
> example I have a date of 3/13/1999 for the last inspection, and the
> inspection period ends up being 3 years. I want to use data from a
> Number field to change the value of the year in the Date field. Is
> there any way to do this?
> 
> 
0
Utf
5/25/2007 8:14:00 PM
On 25 May 2007 11:30:35 -0700, stephen.belyea@gmail.com wrote:

>Hopefully the last time I will have to bug everyone!
>
>Within the BMP database I am working on, I am trying to find a way to
>calculate the next inspection date for each BMP, given the previous
>inspection date [tblBMP.InspDate] and the inspection period
>[tblBMP.InspPeriod] which is in years (e.g. a given BMP is inspected
>every 3 years). I've seen examples of simply adding a static value to
>the date, but how would I go about about using an input number and
>adding it to the year of the previous inspection date?
>
>Thanks again everyone!

DateAdd("yyyy", [tblBmp].[InspPeriod], [tblBmp].[InspDate])

will add InspPeriod years to the date in InspDate...

             John W. Vinson [MVP]
0
John
5/25/2007 8:24:28 PM
Karl and John, I appreciate the assistance. I'm sorry it's taken me so
long to get back to you. I've been out in the field the past few days
with our GPS equipment.

Is it possible to have this field that automatically calculates the
next inspection date as a field in [tblBMP]? How would I get this
argument (to calculate the new date) to appear on a form?

Thanks again!

John W. Vinson wrote:
> On 25 May 2007 11:30:35 -0700, stephen.belyea@gmail.com wrote:
>
> >Hopefully the last time I will have to bug everyone!
> >
> >Within the BMP database I am working on, I am trying to find a way to
> >calculate the next inspection date for each BMP, given the previous
> >inspection date [tblBMP.InspDate] and the inspection period
> >[tblBMP.InspPeriod] which is in years (e.g. a given BMP is inspected
> >every 3 years). I've seen examples of simply adding a static value to
> >the date, but how would I go about about using an input number and
> >adding it to the year of the previous inspection date?
> >
> >Thanks again everyone!
>
> DateAdd("yyyy", [tblBmp].[InspPeriod], [tblBmp].[InspDate])
>
> will add InspPeriod years to the date in InspDate...
>
>              John W. Vinson [MVP]

0
stephen
5/31/2007 5:36:34 PM
On 31 May 2007 10:36:34 -0700, stephen.belyea@gmail.com wrote:

>Karl and John, I appreciate the assistance. I'm sorry it's taken me so
>long to get back to you. I've been out in the field the past few days
>with our GPS equipment.
>
>Is it possible to have this field that automatically calculates the
>next inspection date as a field in [tblBMP]? How would I get this
>argument (to calculate the new date) to appear on a form?

I would strongly recommend that you NOT store the next inspection date in
tblBMP or in any other table, unless you want the calculated date to be just a
default which can be edited. To display it on a form just use

=DateAdd("yyyy", [tblBmp].[InspPeriod], [tblBmp].[InspDate])

as the Control Source of a textbox on the form.

If you have a good reason to actually store it in the table, post back - it's
possible with a bit of VBA code, but it's just likely to be a bad idea to do
so.

             John W. Vinson [MVP]
0
John
6/1/2007 12:44:45 AM
On May 31, 8:44 pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On 31 May 2007 10:36:34 -0700, stephen.bel...@gmail.com wrote:
>
> >Karl and John, I appreciate the assistance. I'm sorry it's taken me so
> >long to get back to you. I've been out in the field the past few days
> >with our GPS equipment.
>
> >Is it possible to have this field that automatically calculates the
> >next inspection date as a field in [tblBMP]? How would I get this
> >argument (to calculate the new date) to appear on a form?
>
> I would strongly recommend that you NOT store the next inspection date in
> tblBMP or in any other table, unless you want the calculated date to be just a
> default which can be edited. To display it on a form just use
>
> =DateAdd("yyyy", [tblBmp].[InspPeriod], [tblBmp].[InspDate])
>
> as the Control Source of a textbox on the form.
>
> If you have a good reason to actually store it in the table, post back - it's
> possible with a bit of VBA code, but it's just likely to be a bad idea to do
> so.
>
>              John W. Vinson [MVP]

Still no dice on the year addition.. Is this because I am trying to
add a Number field to a Date field? I've double checked the names of
my tables and fields, and everything is correct - except the text box
on my form always shows #Name?. What is causing this error, and is
there a way I can fix it?

Thanks again!

0
stephen
6/4/2007 6:53:41 PM
On Mon, 04 Jun 2007 11:53:41 -0700, stephen.belyea@gmail.com wrote:

>On May 31, 8:44 pm, John W. Vinson
><jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
>> On 31 May 2007 10:36:34 -0700, stephen.bel...@gmail.com wrote:
>>
>> >Karl and John, I appreciate the assistance. I'm sorry it's taken me so
>> >long to get back to you. I've been out in the field the past few days
>> >with our GPS equipment.
>>
>> >Is it possible to have this field that automatically calculates the
>> >next inspection date as a field in [tblBMP]? How would I get this
>> >argument (to calculate the new date) to appear on a form?
>>
>> I would strongly recommend that you NOT store the next inspection date in
>> tblBMP or in any other table, unless you want the calculated date to be just a
>> default which can be edited. To display it on a form just use
>>
>> =DateAdd("yyyy", [tblBmp].[InspPeriod], [tblBmp].[InspDate])
>>
>> as the Control Source of a textbox on the form.
>>
>> If you have a good reason to actually store it in the table, post back - it's
>> possible with a bit of VBA code, but it's just likely to be a bad idea to do
>> so.
>>
>>              John W. Vinson [MVP]
>
>Still no dice on the year addition.. Is this because I am trying to
>add a Number field to a Date field? I've double checked the names of
>my tables and fields, and everything is correct - except the text box
>on my form always shows #Name?. What is causing this error, and is
>there a way I can fix it?
>
>Thanks again!

If you're using tblBmp in the Control Source of a form control, it probably
won't work - you can only reference fields that are part of the Form's
Recordsource. 

What is the actual expression you're using on the form control? Copy and paste
it to a message here, please. Also copy and paste the Form's Recordsource SQL
if it's a query, or the name of the table and the relevant fields if it's a
Table.

             John W. Vinson [MVP]
0
John
6/4/2007 10:14:39 PM
On Jun 4, 6:14 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On Mon, 04 Jun 2007 11:53:41 -0700, stephen.bel...@gmail.com wrote:
> >On May 31, 8:44 pm, John W. Vinson
> ><jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> >> On 31 May 2007 10:36:34 -0700, stephen.bel...@gmail.com wrote:
>
> >> >Karl and John, I appreciate the assistance. I'm sorry it's taken me so
> >> >long to get back to you. I've been out in the field the past few days
> >> >with our GPS equipment.
>
> >> >Is it possible to have this field that automatically calculates the
> >> >next inspection date as a field in [tblBMP]? How would I get this
> >> >argument (to calculate the new date) to appear on a form?
>
> >> I would strongly recommend that you NOT store the next inspection date in
> >> tblBMP or in any other table, unless you want the calculated date to be just a
> >> default which can be edited. To display it on a form just use
>
> >> =DateAdd("yyyy", [tblBmp].[InspPeriod], [tblBmp].[InspDate])
>
> >> as the Control Source of a textbox on the form.
>
> >> If you have a good reason to actually store it in the table, post back - it's
> >> possible with a bit of VBA code, but it's just likely to be a bad idea to do
> >> so.
>
> >>              John W. Vinson [MVP]
>
> >Still no dice on the year addition.. Is this because I am trying to
> >add a Number field to a Date field? I've double checked the names of
> >my tables and fields, and everything is correct - except the text box
> >on my form always shows #Name?. What is causing this error, and is
> >there a way I can fix it?
>
> >Thanks again!
>
> If you're using tblBmp in the Control Source of a form control, it probably
> won't work - you can only reference fields that are part of the Form's
> Recordsource.
>
> What is the actual expression you're using on the form control? Copy and paste
> it to a message here, please. Also copy and paste the Form's Recordsource SQL
> if it's a query, or the name of the table and the relevant fields if it's a
> Table.
>
>              John W. Vinson [MVP]

This is the expression I am trying to use:

=DateAdd("yyyy",[tblProperty].[PropInspInterval],
[tblInspection.InspCompleted])

I have a form created from tblProperty, which has a subform created
from tblInspection. The only field being used from tblInspection is
the inspection completed date (which I guess leads me to another
question - how to make sure that it calculates the next inspection
date from the most recent completed inspection date). The actual
expression is being run in the tblProperty form, where
tblProperty.PropInspInterval is located (my expression).

0
stephen
6/5/2007 12:37:14 PM
On Jun 5, 8:37 am, stephen.bel...@gmail.com wrote:
> On Jun 4, 6:14 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
> wrote:
>
>
>
> > On Mon, 04 Jun 2007 11:53:41 -0700, stephen.bel...@gmail.com wrote:
> > >On May 31, 8:44 pm, John W. Vinson
> > ><jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> > >> On 31 May 2007 10:36:34 -0700, stephen.bel...@gmail.com wrote:
>
> > >> >Karl and John, I appreciate the assistance. I'm sorry it's taken me so
> > >> >long to get back to you. I've been out in the field the past few days
> > >> >with our GPS equipment.
>
> > >> >Is it possible to have this field that automatically calculates the
> > >> >next inspection date as a field in [tblBMP]? How would I get this
> > >> >argument (to calculate the new date) to appear on a form?
>
> > >> I would strongly recommend that you NOT store the next inspection date in
> > >> tblBMP or in any other table, unless you want the calculated date to be just a
> > >> default which can be edited. To display it on a form just use
>
> > >> =DateAdd("yyyy", [tblBmp].[InspPeriod], [tblBmp].[InspDate])
>
> > >> as the Control Source of a textbox on the form.
>
> > >> If you have a good reason to actually store it in the table, post back - it's
> > >> possible with a bit of VBA code, but it's just likely to be a bad idea to do
> > >> so.
>
> > >>              John W. Vinson [MVP]
>
> > >Still no dice on the year addition.. Is this because I am trying to
> > >add a Number field to a Date field? I've double checked the names of
> > >my tables and fields, and everything is correct - except the text box
> > >on my form always shows #Name?. What is causing this error, and is
> > >there a way I can fix it?
>
> > >Thanks again!
>
> > If you're using tblBmp in the Control Source of a form control, it probably
> > won't work - you can only reference fields that are part of the Form's
> > Recordsource.
>
> > What is the actual expression you're using on the form control? Copy and paste
> > it to a message here, please. Also copy and paste the Form's Recordsource SQL
> > if it's a query, or the name of the table and the relevant fields if it's a
> > Table.
>
> >              John W. Vinson [MVP]
>
> This is the expression I am trying to use:
>
> =DateAdd("yyyy",[tblProperty].[PropInspInterval],
> [tblInspection.InspCompleted])
>
> I have a form created from tblProperty, which has a subform created
> from tblInspection. The only field being used from tblInspection is
> the inspection completed date (which I guess leads me to another
> question - how to make sure that it calculates the next inspection
> date from the most recent completed inspection date). The actual
> expression is being run in the tblProperty form, where
> tblProperty.PropInspInterval is located (my expression).

I've tried searching the news groups for a similar problem, but I
haven't encountered any that are trying to add a value from one table
to a date in another.

0
stephen
6/8/2007 7:27:25 PM
On Fri, 08 Jun 2007 12:27:25 -0700, stephen.belyea@gmail.com wrote:

>I've tried searching the news groups for a similar problem, but I
>haven't encountered any that are trying to add a value from one table
>to a date in another.

You'ld use an Update query updating the date field to

DateAdd("yyyy", [onetable].[numberfield], [othertable].[datefield])

in a query joining the appropriate records.

Since you haven't posted descriptions of your tables, nor any indication of
how you want to determine which number you want to add to which date, it's a
bit hard to be specific.

             John W. Vinson [MVP]
0
John
6/8/2007 10:17:04 PM
On Jun 8, 6:17 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On Fri, 08 Jun 2007 12:27:25 -0700, stephen.bel...@gmail.com wrote:
> >I've tried searching the news groups for a similar problem, but I
> >haven't encountered any that are trying to add a value from one table
> >to a date in another.
>
> You'ld use an Update query updating the date field to
>
> DateAdd("yyyy", [onetable].[numberfield], [othertable].[datefield])
>
> in a query joining the appropriate records.
>
> Since you haven't posted descriptions of your tables, nor any indication of
> how you want to determine which number you want to add to which date, it's a
> bit hard to be specific.
>
>              John W. Vinson [MVP]

Oops! Sorry! Let me try to clarify my situation. Within tblInspection
is the date that the BMP was last inspected in a short date format.
This is my tblInspection.InspCompleted. For each Property that has a
BMP on it, we have a maintenance agreement that tells us that each BMP
must be inspected every 'x' years. This 'x' years is where I get my
tblProperty.PropInspInterval. Therefore, each property will have a
hand entered value for PropInspInterval, because agreements differ
between BMP types and the years that the agreements were signed (older
agreements have inspection lengths of ten years, newer ones have 3
years, etc.)

tblProperty is displayed in a form in which tblInspection is a subform
(as each property can have multiple inspections done on it).

I'm going to try to figure out how to make this work with an update
query, and I'll get back to you ASAP. Thanks for your help!

0
stephen
6/11/2007 1:00:13 PM
On Jun 11, 9:00 am, stephen.bel...@gmail.com wrote:
> On Jun 8, 6:17 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
> wrote:
>
>
>
> > On Fri, 08 Jun 2007 12:27:25 -0700, stephen.bel...@gmail.com wrote:
> > >I've tried searching the news groups for a similar problem, but I
> > >haven't encountered any that are trying to add a value from one table
> > >to a date in another.
>
> > You'ld use an Update query updating the date field to
>
> > DateAdd("yyyy", [onetable].[numberfield], [othertable].[datefield])
>
> > in a query joining the appropriate records.
>
> > Since you haven't posted descriptions of your tables, nor any indication of
> > how you want to determine which number you want to add to which date, it's a
> > bit hard to be specific.
>
> >              John W. Vinson [MVP]
>
> Oops! Sorry! Let me try to clarify my situation. Within tblInspection
> is the date that the BMP was last inspected in a short date format.
> This is my tblInspection.InspCompleted. For each Property that has a
> BMP on it, we have a maintenance agreement that tells us that each BMP
> must be inspected every 'x' years. This 'x' years is where I get my
> tblProperty.PropInspInterval. Therefore, each property will have a
> hand entered value for PropInspInterval, because agreements differ
> between BMP types and the years that the agreements were signed (older
> agreements have inspection lengths of ten years, newer ones have 3
> years, etc.)
>
> tblProperty is displayed in a form in which tblInspection is a subform
> (as each property can have multiple inspections done on it).
>
> I'm going to try to figure out how to make this work with an update
> query, and I'll get back to you ASAP. Thanks for your help!

I'm not sure how to approach this, creating an update query as a
record source for a text box (I'm assuming that's still the method I
should use). Can someone help me with this?

0
stephen
6/11/2007 5:24:11 PM
Reply:

Similar Artilces:

How can i use a command button to validate date and time
I have two command buttons, each on a different sheet. i need both of them to put the current date in one cell on its sheet and current time in another on its sheet. i also need these cells to be locked so the user can only edit these cells by clicking the command button. Any answers? ...

DTS Export fails on bad date
While trying to import an excel spreadsheet to a SQL Server table, DTS fails, pointing to a column with date values. I have looked at all the date values, and they appear correct. I need to get this excel spreadsheet loaded to a database table. Is there any way to pinpoint the exact cell causing the problem? Or, can you think of another way to export the data in the spreadsheet besides DTS that might not be so sensitive to data content? Thanks, Dean Slindee ...

Adding Space Between Rows?
I've imported a vocabulary list from Word into Excel, with one entry per cell in column A, and I want to put a little space between the rows for readability. In this case, I can't insert blank rows between the entry rows because of the contents of another column. Is there a way to make Excel increase the cell padding? Thanks to anyone who can help solve this quandary. P.S. I'm using Excel 2000 in Windows XP. "Joshua" <givetheword@yahoo.com> wrote in message news:10n3pmpdk2p5ne8@corp.supernews.com... > I've imported a vocabulary list from Word into ...

is the info in AD or Exchange?
Hi, Is the existence and location of mailboxes kept in AD or in Exchange? So if, for example a user's mailbox location was changed and then Exchange restored with a backup done before the move - will it work? Will the mailbox be moved back to the old location or will the restore fail? Thanks, Peter On Tue, 30 Aug 2005 19:58:17 +0700, Peter Kaufman <peterkhub-usenet@yahoo.ca> wrote: >Hi, > >Is the existence and location of mailboxes kept in AD or in Exchange? >So if, for example a user's mailbox location was changed and then >Exchange restored with a backup don...

How do I calculate escalation rates using dates and a set value
If I have a projected that needs to be escalated using start and completion dates by a certain percentage per year, how do I write the formula. For example if my project starts May 26, 2010 and lasts until may 26 2014 I want to add escalation to midpoint at 4% per year. ...

disc date for next month
We want to be able to choose a discount date for the next month the discount is allowed. Our terms are 2% 10th net 15th. We can choose the due date for the 15th of the next month, but we can't choose the discount date as the 10th of the next month. All the invoices entered into the system from the 1st thru the 10th calculate the discount for the current month instead of the next month. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the me...

combine text & date formula to show mmmm dd, yyyy?
How do I combine text and date formula to show the written date and not the Excel number date? I have text in a couple of cells and a date formula in another cell(input from a different worksheet). I have the formula =A3 & F3 & A4 in A1 and would like it to read: We went to the station on December 21, 2009 and took the train. However, it is reading We went to the station on 40168 and took the train. Thanks. Try something like this... A1 = Today is B1 = 12/21/2009 =A1&" "&TEXT(B1,"mmmm dd, yyyy") Returns: Today is December 21, 2009 ...

Is there a way to copy and paste the date into worksheets
Is there a way to copy and paste dates into the worksheet withou individually typing up each one -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2450 View this thread: http://www.excelforum.com/showthread.php?threadid=38137 Is there a way to insert it the date into the the worksheet tab (not th cells in the actual worksheet but the name of the worksheet itself -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: ht...

Formaula for less than date and name begins with...!!! HELP!!!
IM trying to create a formula that gives me a total count for cells that are less than 02/05/10 and the name begins with FHLM, heres what i came up with... =COUNTIF(E:E,">="&DATE(1900,1,1))-COUNTIF(E:E,">="&DATE(2010,2,5))+COUNTIF(E:E,DATE(2010,2,5))-COUNTIF(U:U,"FHLM*") I keep gettin a bunch of ###### or 0, depending on how I move the data around.. Any ideas???? =SUMPRODUCT(--(E1:E100<=DATE(2010,2,5)),--(LEFT(U1:U100,4)="FHLM")) "Senor Martinez" wrote: > IM trying to create a formula that gives me...

Adding the values on even rows/odd rows.
please refer to this post to prevent dupilcates http://www.mrexcel.com/forum/showthread.php?t=322724 Huh? Dave. ...

Grouping dates in pivot table
I am pulling data from a SQL Server database to create a pivot table. Excel (07) is not recognizing the field as a date. This is a field I would like to group by in the pivottable. I am aware of all the techniques to convert this to a date field, but I am searching for an answer as to why XL pivot tables cannot consume the dates directly from a sql query. I have played with bringing the dates back in a number of differenent formats with no success. Any insights into this would be appreciated. Probably coming in as text and yuo may only need to copy an unused cell and paste sp...

how do i change dates on my calendar and keep my pics
Publisher doesn't support this. You would need to do it manually. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com ~pay it forward~ This posting is provided "AS IS" with no warranties, and confers no rights. "Bob T" <Bob T@discussions.microsoft.com> wrote in message news:68671977-F71B-444F-AE72-801D2B359FEE@microsoft.com... > ...

Looking up a date
Trying to get this to work, but no success: =LOOKUP(A6,{DATEVALUE("3/26/2010"),DATEVALUE("6/24/2010"),DATEVALUE("9/22/2010")},{"3","2","1"}) The value in A6 is a function; not hard-coded. It seems to work with one date and one result vector, but not two, and ultimately I will need several, maybe 7 or 8. Any ideas? Thanks! Ryan-- You can't use functions in array constants. >ultimately I will need several, maybe 7 or 8. Why don't you just create a table in ascending order by date: .............A.......

Echoing keyboard input on a CEdit box
I have a CDialog object that has a CEdit data member, and numerical button resources. What is the best way to echo the corresponding number button pushed on the CEdit box, that is, just like in a calculator? -Zilla That would be to get the text from the edit control, add the new char to it and then set the text again. OnChar(uint nChar...) { CString Text; m_CalcEdit.GetWindowText(Text); Text += CString(nChar); m_CalcEdit.SetWindowText(Text); } Ali R. "Zilla" <zilla62XSPAM@bellsouth.net> wrote in message news:RMwSb.88$JA.32@fe02.usenetserver.com... > I...

Format Date 05-28-10
Hi, all, How do I format the date to show May, 2010? The closest I can get is May-10 from the drop down list. Thanks, Malcolm Malcolm wrote: > Hi, all, > How do I format the date to show May, 2010? The closest I can get is May-10 > from the drop down list. > > Thanks, > Malcolm Custom date format: mmmm, yyyy Custom Format mmmm, yyyy Gord Dibben MS Excel MVP On Fri, 28 May 2010 09:05:01 -0700, Malcolm <Malcolm@discussions.microsoft.com> wrote: >Hi, all, >How do I format the date to show May, 2010? The closest I can get is M...

Adding VLOOKUPS across multiple worksheets
Hi, I am trying to add several VLOOKUP results obtained from data in several worksheets. My formula is : - =VLOOKUP(A5,'Common Costs - Budget'!A5:X74,2,FALSE)+VLOOKUP(A5,'Brighton - Budget'!A5:X53,2,FALSE)+VLOOKUP(A5,'Hove - Budget'!A5:X56,2,FALSE) I have several worksheets all with similar budget information on and, for example, I need to all the payroll costs from each of the sheets. Not all the sheets will always have costs on so I was thinking of doing lookups and adding them together but I just keep getting the #N/A result. Is there a way to...

Formatting a # to auto + 1 when adding new info- not autonumber th
Hi all~ Sorry I am slow with all this and don't know VBA...I have a table that currently has a field name "CI#" with the data type as "number" with a format of "0000"- when I enter items into the form based on this table, I always manually enter the numbers (the numbers are YYNN)...I would like it to automatically add "+1" to it. I understand from reading changing this to autonumber is bad. So how do I do this? Thank you so much, Sarah Sarah Search on-line for "Custom Autonumber". The general gist is that you'...

Excel Date Formatting
Hi, In Excel the regional settings are set to short date format. However, if you try and format cells individually with a date, the date changes, but when the spreadsheet is closed and the reopened the formatting reverts to the short date format! We are using Excel 2000 with sp3 on XP (If thats any help!) If anyone has any ideas why this is occuring i'd love to know! Cheers, Hayden Are you saying that the same person on the same pc opens the file and the date format has changed? If it's another user (or another pc), then it could be the way excel picks up the date format if ...

SRS Default reports currency and date format
Hi there I have installed the SQL Reporting Services default reports supplied by Microsoft, which is very useful for the customer. However, I would like to know how to I change the date format and currency symbol on the reports. It currency displays the currency as '$', but we are in South Africa and the the date format to dsiplay dd/mm/yyyy and the currency to be 'R'. My regional settings are set correctly and the data within Dynamics displays correctly. Regards Robin Robin, You will need to change the Properties of the fields in question on the report itself. Ope...

Adding Contacts When Replying
I am using OL2003 and I do not seem to be able to figure out how to add senders' email addresses to my contact list or address book when I reply to their message. Other than MapiLab, are there any other good Add-Ins or Third Party Utilities that will automatically add senders to my contact list or address book when I reply to their message? Thanks! Steve Forrestor gfr92y at yahoo dot com Please see my response to your post in another group. --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Micr...

Adding a line after a date
I have a file that I am trying to sort and add a line between years. This file is 36,000 lines long and holds multiple records by a specific identifyer. The problem is I have multiple records per year and I only want the file to put a break after each year not each date. Please help! :-) Thanks in advance Use a helper column with this formula copied down, then Copy > Pastespecial > Values to get rid of the formula......then do your line-insertion based on this column..... =YEAR(A1) Vaya con Dios, Chuck, CABGx3 "Mpls501sMan" <Mpls501sMan@discussions.microsoft....

Printing only added items on Orders
Hello all, this is my first post, I'm new to GP. (Pro ver 9..) If an Order has already printed, and I add additional line items, is there a way to print ONLY items that have not been printed prior, on the Pick ticket and Packing list ? For example: I create Order for one item, print. Add another item later, print, only want the second line item to print. Add another line later, print, only want that third item to print. Is that an option I can select ? Thanks !! More info.... SImilar to what is available from the "Print Sales Doscuments" window, where it allows you to s...

CRM release date
Hi there, would you please help me know th release date for MSCRM version 1.2? -- Regards, Marwa Hi Marwa, 1.2 has been available for some time - it is the current release. Ian "marwa" <marwa@discussions.microsoft.com> wrote in message news:7D0959E2-8BF9-4F64-9B7D-B0FD00C0D98C@microsoft.com... > Hi there, > > would you please help me know th release date for MSCRM version 1.2? > -- > Regards, > Marwa > Thank you for your reply, but i want to know the exact date, when it is launched ? -- Regards, Marwa "marwa" wrote: > Hi there,...

Problem with dates in a DAvg query
Could someone please tell me why the following section of code gives -317 days average dwell when there is only one part, (for info [Date Raised] is 24/11/2000 and [theMonth] is 01/11/200)? DwellAvg: CInt(DAvg("datediff('d',Dateserial(year([Date Raised]),month([Date Raised]),day([Date Raised])), #" & DateSerial(Year([theMonth]),Month(DateAdd('m',1,[theMonth])),1) & "#)", Could it be something to do with UK/US date formats and if so how can I remedy this? I know it is a problem in this part of the code as the query gives the correct count of e...

Change date formats on cheque
We are using payable cheques with the stub on the top. We are trying to modify the date to ready dd-mmm-yyyy. I have followed the instructions in https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;871918 except to change the table name becuse I am on 8.0. This will print the month format as "september" what I am looking to do is have it read the alpha short form of the month (ie - Jan, Feb, Mar......) Is there any way to accomplish this? It currently is working and printing the full month. I am just interested in getting the short form of the month to print. ...