Forecasting using SUMPRODUCT and dates

I am having a problem with counting data that is within a certain date.
for example, I have 4 columns.  SERVERNAME, LOCATION,  TYPE
LEASEENDDATE.  I am currently using SUMPRODUCT to count the number o
servers I have for a particular location and type.

A                     B                C        D    
SERVERNAME  LOCATION  TYPE  LEASEENDDATE
Server1           MN             Web  6/30/2004
Server2           CA              App   8/15/2004

SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))

On another sheet, I would like to forecast of how many servers I hav
that are not expired yet for every month of the year and have 1
columns for each month, JAN, FEB, MAR, etc.  I am thinking of a formul
like:

For January:
IF((D2:D200>1/31/2004,SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))

For February:
IF((D2:D200>2/29/2004,SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))

How do I ONLY count the number of 'Web' servers in 'MN' that have no
yet exprited as of (date in column D)?

Thanks

--
Message posted from http://www.ExcelForum.com

0
4/9/2004 3:51:59 PM
excel.misc 78881 articles. 5 followers. Follow

15 Replies
1319 Views

Similar Articles

[PageSpeed] 31

Hi
try something like
=SUMPRODUCT((D2:D200>DATE(2004,1,31))*(B2:B200="MN")*(C2:C200="Web"))

--
Regards
Frank Kabel
Frankfurt, Germany


> I am having a problem with counting data that is within a certain
> date. for example, I have 4 columns.  SERVERNAME, LOCATION,  TYPE,
> LEASEENDDATE.  I am currently using SUMPRODUCT to count the number of
> servers I have for a particular location and type.
>
> A                     B                C        D
> SERVERNAME  LOCATION  TYPE  LEASEENDDATE
> Server1           MN             Web  6/30/2004
> Server2           CA              App   8/15/2004
>
> SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))
>
> On another sheet, I would like to forecast of how many servers I have
> that are not expired yet for every month of the year and have 12
> columns for each month, JAN, FEB, MAR, etc.  I am thinking of a
> formula like:
>
> For January:
> IF((D2:D200>1/31/2004,SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))
>
> For February:
> IF((D2:D200>2/29/2004,SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))
>
> How do I ONLY count the number of 'Web' servers in 'MN' that have not
> yet exprited as of (date in column D)?
>
> Thanks!
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
4/9/2004 4:04:58 PM
Frank,

After our conversation with Norman earlier this week

=SUMPRODUCT((D2:D20>(--("2004-01-31")))*(B2:B20="MN")*(C2:C20="Web"))


-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:utOroxkHEHA.664@tk2msftngp13.phx.gbl...
> Hi
> try something like
> =SUMPRODUCT((D2:D200>DATE(2004,1,31))*(B2:B200="MN")*(C2:C200="Web"))
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> > I am having a problem with counting data that is within a certain
> > date. for example, I have 4 columns.  SERVERNAME, LOCATION,  TYPE,
> > LEASEENDDATE.  I am currently using SUMPRODUCT to count the number of
> > servers I have for a particular location and type.
> >
> > A                     B                C        D
> > SERVERNAME  LOCATION  TYPE  LEASEENDDATE
> > Server1           MN             Web  6/30/2004
> > Server2           CA              App   8/15/2004
> >
> > SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))
> >
> > On another sheet, I would like to forecast of how many servers I have
> > that are not expired yet for every month of the year and have 12
> > columns for each month, JAN, FEB, MAR, etc.  I am thinking of a
> > formula like:
> >
> > For January:
> > IF((D2:D200>1/31/2004,SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))
> >
> > For February:
> > IF((D2:D200>2/29/2004,SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))
> >
> > How do I ONLY count the number of 'Web' servers in 'MN' that have not
> > yet exprited as of (date in column D)?
> >
> > Thanks!
> >
> >
> > ---
> > Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
4/9/2004 5:22:31 PM
Bob Phillips wrote:
> Frank,
>
> After our conversation with Norman earlier this week
>
> =SUMPRODUCT((D2:D20>(--("2004-01-31")))*(B2:B20="MN")*(C2:C20="Web"))


:-)
yes I remember....
Frank

0
frank.kabel (11126)
4/9/2004 5:28:53 PM
Hi Frank and Bob!

A very productive week! Sorted out European fast date entry and 
discovered the uses of --"2004-04-10" date entry.



-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments) 
available free to good homes.
"Frank Kabel" <frank.kabel@freenet.de> wrote in message 
news:O5mFiglHEHA.3376@TK2MSFTNGP09.phx.gbl...
> Bob Phillips wrote:
>> Frank,
>>
>> After our conversation with Norman earlier this week
>>
>> =SUMPRODUCT((D2:D20>(--("2004-01-31")))*(B2:B20="MN")*(C2:C20="Web"))
>
>
> :-)
> yes I remember....
> Frank
> 


0
njharker (1646)
4/9/2004 5:39:26 PM
Obviously VB works - I think you should send Frank and I a case each, and it
may work for us too.

Bob


"Norman Harker" <njharker@optusnet.com.au> wrote in message
news:ucrydmlHEHA.2556@TK2MSFTNGP12.phx.gbl...
> Hi Frank and Bob!
>
> A very productive week! Sorted out European fast date entry and
> discovered the uses of --"2004-04-10" date entry.
>
>
>
> -- 
> Regards
> Norman Harker MVP (Excel)
> Sydney, Australia
> njharker@optusnet.com.au
> Excel and Word Function Lists (Classifications, Syntax and Arguments)
> available free to good homes.
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:O5mFiglHEHA.3376@TK2MSFTNGP09.phx.gbl...
> > Bob Phillips wrote:
> >> Frank,
> >>
> >> After our conversation with Norman earlier this week
> >>
> >> =SUMPRODUCT((D2:D20>(--("2004-01-31")))*(B2:B20="MN")*(C2:C20="Web"))
> >
> >
> > :-)
> > yes I remember....
> > Frank
> >
>
>


0
bob.phillips1 (6510)
4/9/2004 6:11:42 PM
Bob Phillips wrote:
> Obviously VB works - I think you should send Frank and I a case each,
> and it may work for us too.
>
> Bob

Bob
very good idea <vbg>

Norman: I think UPS can deliver this in a couple of days <ebg>
Frank

0
frank.kabel (11126)
4/9/2004 6:20:02 PM
Hi Frank!

I've got a couple of slabs, but it's like Guinness and just doesn't 
travel. You'll have to come and drink it here.

Bob's had too much already celebrating Arsenal's win!

I sent the European date solution to Chip complete with the 
attribution to VB (Victoria Bitter).
-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments) 
available free to good homes. 


0
njharker (1646)
4/9/2004 6:30:19 PM
Excellent.  That works.  Thank you very much.

In addition to this formula, how would I implement --within the sam
formula-- the automatic counting of of rows in my sheet?  ..instead o
choosing a range of  (for example) B2:B200, have my SUMPRODUCT formul
automatically count and utilize rows that are filled since the amoun
of rows of data in my sheet changes daily.  Also, there will always b
data in column A, no blanks.

Thx

--
Message posted from http://www.ExcelForum.com

0
4/9/2004 6:54:28 PM
Will those rows have blanks or not? If not, just change B2:B200 to
B2:OFFSET(B1,COUNTA(B:B)-1,0), etc.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"rerhart >" <<rerhart.14glkq@excelforum-nospam.com> wrote in message
news:rerhart.14glkq@excelforum-nospam.com...
> Excellent.  That works.  Thank you very much.
>
> In addition to this formula, how would I implement --within the same
> formula-- the automatic counting of of rows in my sheet?  ..instead of
> choosing a range of  (for example) B2:B200, have my SUMPRODUCT formula
> automatically count and utilize rows that are filled since the amount
> of rows of data in my sheet changes daily.  Also, there will always be
> data in column A, no blanks.
>
> Thx.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
4/9/2004 7:21:21 PM
>>Will those rows have blanks or not? If not, just change B2:B200 t
B2:OFFSET(B1,COUNTA(B:B)-1,0), etc.


-> Column B will not have blanks, but others may have blanks.  I assum
it would not matter and I only need to count rows using data from on
column that has no blanks, correct?

Could I also put a range of B2:B65536 to cover the entire spreadsheet?

Thx

--
Message posted from http://www.ExcelForum.com

0
4/9/2004 7:41:32 PM
"rerhart >" <<rerhart.14gnr6@excelforum-nospam.com> wrote in message
news:rerhart.14gnr6@excelforum-nospam.com...

> -> Column B will not have blanks, but others may have blanks.  I assume
> it would not matter and I only need to count rows using data from one
> column that has no blanks, correct?
>

Yes that is good enough.


> Could I also put a range of B2:B65536 to cover the entire spreadsheet?
>

You could but not recommended. It will imapir performance.


0
bob.phillips1 (6510)
4/9/2004 8:27:58 PM
I entered the following formula:

=SUMPRODUCT((D2:D200>(--("12/31/2003")))*(B2:OFFSET(B1,COUNTA(B:B)-1,0)="MN")*(C2:C200="Web"))

But get #N/A for a result??

=SUMPRODUCT((D2:D200>(--("12/31/2003")))*(B2:B200="MN")*(C2:C200="Web"))

...Works fine other than I would like to...
within the same formula, how can I get rid of all my ranges (D2:D200
B2:B200, C2:C200, etc.) and replace the formula with something tha
auto counts filled-in rows...keeping in mind that column B will have n
blanks?

Thanks!
-

--
Message posted from http://www.ExcelForum.com

0
4/12/2004 4:57:15 PM
All of the ranges must be the same size. I thought that was what you
referred to in the previous post.

Try this

=SUMPRODUCT((D2:OFFSET(D2,COUNTA(B:B)-1,0>(--("2003/12/31")))*(B2:OFFSET(B1,
COUNTA(B:B)-1,0)="MN")*(C2:OFFSET(C2,COUNTA(B:B)-1,0="Web"))

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"rerhart >" <<rerhart.14m05d@excelforum-nospam.com> wrote in message
news:rerhart.14m05d@excelforum-nospam.com...
> I entered the following formula:
>
>
=SUMPRODUCT((D2:D200>(--("12/31/2003")))*(B2:OFFSET(B1,COUNTA(B:B)-1,0)="MN"
)*(C2:C200="Web"))
>
> But get #N/A for a result??
>
> =SUMPRODUCT((D2:D200>(--("12/31/2003")))*(B2:B200="MN")*(C2:C200="Web"))
>
> ..Works fine other than I would like to...
> within the same formula, how can I get rid of all my ranges (D2:D200,
> B2:B200, C2:C200, etc.) and replace the formula with something that
> auto counts filled-in rows...keeping in mind that column B will have no
> blanks?
>
> Thanks!
> -R
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
4/12/2004 5:41:28 PM
Once again, thank you very much for your help.

With the follwoing formula:

=SUMPRODUCT((D2:OFFSET(D2,COUNTA(B:B)-1,0>(--("12/31/2003")))*(B2:OFFSET(B1,COUNTA(B:B)-1,0)="MN")*(C2:OFFSET(C2,COUNTA(B:B)-1,0="Web"))))

I am now receiving a #VALUE error.  Not sure where the problem is.

Here is my sample data:

Server Name	Location	Type	Lease End Date
Server1	                MN	Web	6/30/2004
Server2	                CA	App	8/15/200

--
Message posted from http://www.ExcelForum.com

0
4/12/2004 6:06:53 PM
This works for me. It is imperative that you enter the date in the correct
format as I have done

=SUMPRODUCT((D2:OFFSET(D2,COUNTA(B:B)-1,0)>--("2003/12/31"))*(B2:OFFSET(B2,C
OUNTA(B:B)-1,0)="MN")*(C2:OFFSET(C2,COUNTA(B:B)-1,0)="Web"))

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"rerhart >" <<rerhart.14m3df@excelforum-nospam.com> wrote in message
news:rerhart.14m3df@excelforum-nospam.com...
> Once again, thank you very much for your help.
>
> With the follwoing formula:
>
>
=SUMPRODUCT((D2:OFFSET(D2,COUNTA(B:B)-1,0>(--("12/31/2003")))*(B2:OFFSET(B1,
COUNTA(B:B)-1,0)="MN")*(C2:OFFSET(C2,COUNTA(B:B)-1,0="Web"))))
>
> I am now receiving a #VALUE error.  Not sure where the problem is.
>
> Here is my sample data:
>
> Server Name Location Type Lease End Date
> Server1                 MN Web 6/30/2004
> Server2                 CA App 8/15/2004
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
4/12/2004 7:38:21 PM
Reply:

Similar Artilces:

Date format issue when submitting from a userform to a spreadsheet
Hi, I have a userform that I've generated which routes dates onto a spreadsheet based on the users input. I am having a bit of a frustrating time with the dates, it would appear that in the process of moving the date from the userform to the spreadsheet some dates are switched/transposed. I'll give an example. If someone enters 09/02/2004 on the userform excel seems to look at 09 and assume it is a month and transposes the dates to 02/09/2004(this does not appear to be a US/UK format issues as I have already gone down that road). However, if the date 13/09/2004 is entered i...

Using iterations to calculate circular references.
I am working with data that requires using circular references in some calculations. I checked the "iterations" box to allow for these calculations to work, and Ecxel seems to be calculating everything correctly. The problem is that after I close the workbook, when I later reopen it to continue working, all the cells that are a part of the circular reference display the #VALUE! error. I can fix this by deleting one cell that is part of the loop and then undoing that command, or by copying the formula from a cell, deleting it, and pasting it back into the same cell. I have to d...

Microsoft CRM using Firefox
Hello, Would anyone by chance know if CRM will work correctly on Firefox. Currently, I have employees that are using Firefox and are encountering the same issue as IE [Internet Explorer bombs out and states that it has encountered problems...]. I am just having employees use IE to prevent problem. Thank you for your comments in advance. Install a Firefox extension called IETab "Marcos" <Marcos@discussions.microsoft.com> wrote in message news:19B93E29-757D-46FF-B747-A17EB112ED94@microsoft.com... > Hello, > > Would anyone by chance know if CRM will work cor...

Using crm:5555 rather than a custom hostheader
All I would just like to know what's the recommended solution for a CRM (v3) deployment:- use the standard crm:5555 or a custom hostheader e.g. crm.mycompany.net. Will the custom hostheader cause me any problems? Will external users be able to logon remotely using this host header? Many thanks, Nick. ...

Office 2003 Pro
I cant get Word to be my email editor. I think its a result of installing and having problems with McAfee on my system. I have uninstalled Office 2003 and reinstalled it 2 times and I still get the same problem.I have searched support and havent found anything. This is a pretty new machine running Windows XP Pro. I get this error. Microsoft Word is set to be your e-mail editor. However, Word is unavailable, not installed, or is not the same version as Outlook. The Outlook e-mail editor will be used instead. An OLE registration error occurred. The program is not correctly installed. Run Setu...

Between using Sumproduct
Is there a way to enter a date value that is between 2 dates in sumproduct or another function that will look at the same column and return the sum of a different column? If the value is greater than May 1,2007 but less than August 31, 2007 in column A it will return the sum of the matching rows in column B. I have tried this with either greater than OR less than and it works but, when I try them in the same sumproduct() it fails. Thanks for reading and any information to the right direction. -- Lee Coleman =SUMPRODUCT(--(A2:A500>=DATE(2007,5,1)),--(A2:A500<=DATE(2007,8,31)),B2...

Creating a template using Publisher
I need help creating a neighborhood directory template using Publisher and merging an Excel spreadsheet into it. It keeps printing multiple pages of the first page after I merge. What version Publisher? If you have 2003 use the catalog merge for your directory. http://office.microsoft.com/en-us/assistance/CH010504381033.aspx Otherwise you need to setup your page as though it is labels. Determine the size of the area you need for each entry, select labels in page setup, type the size you determined in the page setup. Adjust the gaps and margins when you are ready to print (2000 and bel...

Is anybody using Symantec Enterprise Vault?
Hi, A vendor has recommended this solution to us, I have looked at the datasheet for the product and it speaks the right language but I was wondering if anybody on here uses it in a live environment and what their opinion of it is. Cheers Sean ...

iPhone Email to Entourage Using Exchange
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When I answer an email on my iPhone, it does not register it in Entourage as answered. This function worked fine until the business I work for put everyone on Exchange. Is there a setting for this that I can change? <br><br>Thanks! ...

Using Sumproduct when some of the values are null
I am having a problem using Sumproduct when some of the cells have null values. I will try to describe the problem. Please let me know if you need additional information. Do any of you have any ideas of how I can fix that sumproduct to get it to work? A B C Row Trend Claims 5 Emergency $0 6 Emergency $0 7 Emergency $0 8 Emergency 81.68% $24,444 9 Emergency 35.00% $164,758 10 Emergency 35.00% ...

Keyboard shortcut for current date and time
Hi, Ctrl+ ; inserts current date and Ctrl+Shift+; inserts current ti me Ctrl+Shift+; inserts the current time with the date serial as 0 and not the current date's date serial. Presently I am adding the two (ie current date and time) to get the current date and time. Is there a keyboard shortcut that does this? Thanks in advance. Regards, Raj CTRL+; then SPACE then CTRL+SHIFT+; -or- =Now() -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware "Raj" <rspai9@gmail.com> wrote in message Hi, Ctrl+ ; inserts current dat...

SUMPRODUCT 01-21-10
The following formula works fine: =SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),(INDIRECT($AW$3&"!$CI$1:$CI$6000"))) However, I cant seem to get the following to work: =SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A","B"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01","XR01"),(INDIRECT($AW$3&"!$CI$1:$CI$6000"))) I need to be able to summarise (using a headcount indicator (1) in Column CI) ...

Server being used to send out viruses
Problem: When I select "Clients can only submit if homed on this server" in the CONNECTIONS tab in the IMC, my remote POP3 users cannot send email through the server. The email just sits in their Outbox. These POP3 clients do have mailboxes (email accounts) on the server so this should work. When I set my server to ACCEPT CONNECTIONS FROM ANY HOST, my server becomes a target for others (as known as evil people) to use my email server to send out viruses. Any ideas? Thanks, everyone who contributes to this forum. This is great! David Hi, If you are using Exchange, w...

Unable to repy using word editor
When repyling to an e-mail I receive the following message, "This form requires Word as your e-mail editor, but Word is either busy, or cannot be found. The form will be opened in the Outlook editor instead". I found article 284900, that states you may get this message when you have office 2000 and office xp installed on the same computer. This is not the case on my computer, Office 2000 does not exist, only office xp. Can anyone help me? There appear to be a whole host of causes for this error message. Most have no solution. Here are a few that do: http://support.microsoft...

How do I tell if Frontpage was used to design a website?
The developers of my website are long gone and I know nothing about what they did. I think it was deisgned in Frontpage, bit am not sure. Once I know that I would like to "convert" it to something that would allow me make changes to the site easily...maybe a visual website designer..is there such a thing? To see if your website is designed in FrontPage a Link to your website will help us. CoffeeCup.com Say:: Visual Site Designer is a WYSIWYG Website creator and editor that helps you make WebPages with no experience or HTML knowledge. Just drag and drop images or ...

question about using the correct schema namespace
I have a Access/VBA client that exports xml to the local drive, then posts it over http to an aspx page. The aspx page consumes it, then builds itself based on the xml data. This works find only if I first mannually change the root entry's namespace url as shown below. Does anyone know how I can get the two (cleint xml export and aspx xml consume) to work together with the appropriate namespace? Here is the top three lines of my xml export (prior to mannually changing it): <?xml version="1.0" encoding="UTF-8"?> <root xmlns:xsd="http://www.w3.org/200...

Compare Now() to a European date
This is driving me nuts, I have a list of certificates. In column B their expiry dates are entered as Europeans, some at least, do. Like today would be 20080524. I want these cells to change colour with conditional formating. For instance becoming yellow when there is less than three months between now and the expiry date, and then becoming red when there is less than one month to expiry. Else they should remain without colour. I have read through a hundred posts dealing with similar needs and seemingly fine replies, but I get nowhere with my particular sheet. When I format my B cell as ...

Formula for date field
1.I have simple dates in one column (say column A) . 2.In the next column(Column B) I would like the date five months after Column A to be displayed.Eg if Column A has an entry of 9th June 2007,Column B should display 8th November,2007. 3.A simple formula does not do the job as this does not take into account the different number of days in different months! regards S.Sanatani Your post is a bit ambiguous since you don't really say how the different number of days in months should be handled. One way: A1: <date> B1: =DATE(YEAR(A1),MONTH(A1)+5,DAY(A1)-1) In articl...

Date Format turn to Year
Hi, I tried to convert the date to YEAR and then the year plus 25 Years later. =Year(A1) I'm getting the result 1900 instead of 1965. I tried to add 25 years later to 1990 from 1965. Your help would be much apprecated. Thanks What's in A1? Are you sure it's a real date? "learning_codes@hotmail.com" wrote: > > Hi, > > I tried to convert the date to YEAR and then the year plus 25 Years > later. > > =Year(A1) I'm getting the result 1900 instead of 1965. > > I tried to add 25 years later to 1990 from 1965. > > Your ...

Using backup as import
Can I import a Money 2002 backup file into Money 2007? No but you should be able to restore it. "perple" <perple@discussions.microsoft.com> wrote in message news:0832C8E2-B5EB-4197-954E-03E64F66E32E@microsoft.com... > Can I import a Money 2002 backup file into Money 2007? ...

Stop use of Distribution List
Is there anyway that you can stop, or restrict the use of a distribution list? Since you have so much detail of how you are setup and if there are specific people or groups you want to restrict from using it, I will answer your question with the same amount of detail....YES. Add more detail and we can give you a better answer. "brendanrtw" <brendanrtw@discussions.microsoft.com> wrote in message news:6CB3EB2F-F428-46AD-A41E-A1495B8CF4CD@microsoft.com... > Is there anyway that you can stop, or restrict the use of a distribution > list? On Thu, 3 Nov 2005 12:00:...

using backslash ( \ ) inside Parameter
Hi I am trying to use backslash as a part of a parameter at stored procedure. And I am getting syntax error. For instance, to execute like this.. EXEC spSetupPermissions CORP\ABC, Sale with parameter I have set up like this.. CREATE PROC [dbo].[spSetupPermissions] @USERNAME varchar(50), @DEPARTMENT varchar(12) AS IF @DEPARTMENT = 'Sale' BEGIN EXEC Sale.dbo.sp_addlogin @loginame= @USERNAME EXEC Sale.dbo.sp_addrolemember 'db_owner', @USERNAME END ......etc... I should I go about doing this? I would appreciate if anyone would give...

using dates Part 2
Karl was great in helping me get to this point with dates, now I'm wondering if we can take it 1 step further? For Activity Dates prior to 2/1/2007 they are using a normal reporting year and the formulas below take care of Activity dates >2/1/2007? So for example prior to 2/1/2007 1/1/2006 would have a B_Qtr of 2006-1 1) B_Qtr - 2011-1 --- Format(DateAdd("m",11,[ActivityDate]), "yyyy - q") 2) Year - 2011 ---- Year(DateAdd("m",11,[ActivityDate])) 3) Qtr - Q1 ---- Format(DateAdd("m",11,[ActivityDate]), "q") -- Than...

Today's date on an Active X Calendar
Could anyone tell me how to set the properties so that the ActiveX calendar I have in the database, displays the current date when the program is openend. I thought this would have been easy, but obviously not! Thanks for any help. CEL504 wrote: > Could anyone tell me how to set the properties so that the ActiveX calendar I > have in the database, displays the current date when the program is openend. > > I thought this would have been easy, but obviously not! > > Thanks for any help. http://groups.google.com/group/comp.databases.ms-access/msg/1564d683ede98f8c Jame...

Installing Outlook 2003 using Custom Wizard
I am installing Outlook 2003 using the custom installation wizard. This was working fine for some time. Now, someone in my group must have modified the settings because when I install Office using this MST file the local Outlook Contacts are not showing in the GAL. When I go to the properties and try to add it the check box for "Show this folder as an email address book" is greyed out. I have to go in and manually add the Outlook Address book. Can anyone tell me what setting I can change in my Outlook custom install settings for the MST to make this available by default? ...