IIf - Sum Problem

I have an employee database which tracks training.  I have a table with basic 
employee information and a seperate table to hold all trainings with dates 
completed, hours and if it was mandatory.  They are linked by an employee id.

I want to write a sum for each employee that shows the total hours earned if 
the completed date falls between the user entered start and end date of the 
report.  Some of the trainings show regardless of the date completed if they 
are identified as a mandatory training.  This is my thoughts on the IIf 
statement but I am missing something.

=IIf([Completed] IS BETWEEN (([Forms!frmDateSelect!Start]) AND( 
[Forms!frmDateSelect!End]))),(Sum([Hours])))
0
Utf
12/29/2009 7:24:02 PM
access.reports 4434 articles. 0 followers. Follow

1 Replies
636 Views

Similar Articles

[PageSpeed] 28

Try this --

=Sum(IIf([Completed] IS BETWEEN CVDate([Forms!frmDateSelect!Start]) AND 
CVDate([Forms!frmDateSelect!End]),[Hours], 0))

-- 
Build a little, test a little.


"helpmeplease" wrote:

> I have an employee database which tracks training.  I have a table with basic 
> employee information and a seperate table to hold all trainings with dates 
> completed, hours and if it was mandatory.  They are linked by an employee id.
> 
> I want to write a sum for each employee that shows the total hours earned if 
> the completed date falls between the user entered start and end date of the 
> report.  Some of the trainings show regardless of the date completed if they 
> are identified as a mandatory training.  This is my thoughts on the IIf 
> statement but I am missing something.
> 
> =IIf([Completed] IS BETWEEN (([Forms!frmDateSelect!Start]) AND( 
> [Forms!frmDateSelect!End]))),(Sum([Hours])))
0
Utf
12/29/2009 7:40:01 PM
Reply:

Similar Artilces:

Problem trying to move between cells on a protected worksheet.
I am using the TAB key to move between the unlocked cells on a protected worksheet. For some reason, when I get to the end of some rows, the cursor returns to the home cell on that row, not to the next row down. Is this because the cells are merged...if so, how can it be corrected? thanks ...

Subtotal problem
I have a spreadsheet that displays subtotals in a column I have compressed the spreadsheet to just show the subtotals. I want to somehow copy and paste or keep just the subtotal info in the spreadsheet without the undlerying values that make up the subtotals. Paul - This really belongs in an Excel Forum, not here. Here is one solution that lets you copy only the visible cells before pasting them elsewhere: With the spreadsheet compressed, select the range you want to copy. Then select from the menu items Edit | Go To | Special... and select Visible Cells Only. T...

RE: Outlook 2002
To whom it may concern: I recently received a spreadsheet with Names, email, phone, etc, and it needed to be inmported into Outlook Contacts. I made sure all columns were in their means, and converted it into a .csv file. Afterwards I imported this file into Outlook contacts, made sure that all fields were mapped, and voila. Everything seem to have worked out. I made an adjustment to each one afterwards, changing the company name to be the full name. I created a new Distribution List, and began adding in names, but when I did, the 'email' field associated with the name was i...

Problem in Excel Charting
Hi, I have the following problem in chart Steps Followed 1)Open a new Excel Workbook 2)Associate a database query (Select getdate() as TodayDate ) with A1 Cell. This would return "TodayDate" in A1 and current Date in A2 cell 3)Select the following from "Insert" Menu - "Name" - "Define" 4)There we can find the Names of the Workbook - Which includes the Name for the Data Range associated with the Query (added during STEP 2) 5)The value shown is =Sheet1!$A$1:$A$3 although only A1 and A2 are populated. Could someone please explain the incons...

Resizing picture frame/picture problems
Hi Everyone! I am doing a layout for a pictorial directory in Microsoft Publisher 2003. I created picture frames the size that I wanted, and inserted my picture, using "change picture" in the drop down menu. However, when the picture appeared, it did not appear in the size that I made the frame. In fact, the frame reduced in size to fit the picture. How can I make the picture that I insert into an empty frame the same size of the frame without the frame resizing itself to the picture? Is there a better way? (By the way, the picture was scanned and stored at 100% i...

problem with scatter plot an the millennium
I have been tacking several health stats over time since the 1980s and do a scatter plot against time. I have noticed that it does fine going through the year 2000, but after a certain range of source data, it starts plotting from the year 1900 and bunches my data points on the right side of the graph. (one of the graphs it is at 20 data points) I can reduce the source data range from either end and the graph looks fine, but I am missing data. This happens on excel 2002 and excel 2003. Is there some way to force the graph to use a specific range -- say 1980-2010 rather than 1900 to 2...

Redeploment wizard problem
Hi everybody, I have a problem running the redeploment wizard to mirror an existing CRM. The error gives this message: "An error occourred when populating Microsoft CRM user groups. Ensure that CRM accounts are accessible from the current domain and run the wizard again." but I don't know what is wrong. Has Anybody an idea??? Thanks a lot Martin ...

OWA problem with changing local path in IIS
I am migrating an Exchange 5.5 org to 2003 with 16 sites. We're about half way through the migration and have had a problem with OWA defaulting to the domain name of the site where the hub resides. In IIS on the Exchange virtual directory we can reset it to the local desired domain name, but it eventually resets back to the default domain. Example Exchange virtual directory local path \\.\BackOfficeStorage\domain1.edu\MBX is the initial setting. We change to the local domain \\.\BackOfficeStorage\domain13.edu\MBX . But it eventually resets to the first setting. ...

Money upgrade 2004 ->2008 file problems
I was using money 2004 for so many years that I lost my CD ( but still have the paper box product 105-00462 origin ireland ). Now I changed computer, and as I dont have the cd I downloaded money 2008 amd tried to opened my file. It doesn't open. It says that it is not compatible with this version of money ? How can i solve this problem? I need to use money and to have back all the old data. Please help See http://umpmfaq.info/faqdb.php?q=1. "Abilis" <Abilis@discussions.microsoft.com> wrote in message news:DC7EA4C7-1A3B-4FAD-9542-46571B71BE18@microsoft.com... >I w...

MS CRM 1.2 Sales for Oulook Problem
I've installed SFO into my laptop. Yesterday everything worked fine, but today I faced some problems. I cannot create CRM appoitments/CRM Contacts/CRM tasks from the SFO CRM buttons. Nothing happens while clicking these buttons. Have somebody else faced this kind of problems and what should I do to overcome this?? BR, Saku I've the same problem, but only with crm appointments. crm e-mails, contacts and tasks work fine! Regards Thomas "Saku" wrote: > I've installed SFO into my laptop. Yesterday everything worked fine, but > today I faced some problems. I...

Regarding Answer for "CRM oulook client startup problem"
I have tried this fix and it does not work. Are there any other options. I have worked on this for over two weeks now and none of the fixes I have found work. I really need this to work. Thanks -- Heather Is .Net framework 2.0 installed on the machine? It's not compatible with the current build of CRM Outlook Client. "heather" <heather@discussions.microsoft.com> wrote in message news:8DB4F027-ED6C-43BD-BEB8-5EF16C0A5CDA@microsoft.com... >I have tried this fix and it does not work. Are there any other options. I > have worked on this for over two weeks now a...

Autofilter
Hi, I'm using one excel document with Data >- Filter >- Autofilter function. I'd like to put autosum command but it doesn't work with autofilter's columns. Please help. Have you try to use =subtotal(9,range) "Dan" <dan_REMOVE_@ihson.com> ���g��l��s�D:O0fqZzW0GHA.3568@TK2MSFTNGP03.phx.gbl... > Hi, > I'm using one excel document with Data >- Filter >- Autofilter function. > I'd like to put autosum command but it doesn't work with autofilter's > columns. > > Please help. > "bobocat" <bobo.c...

SUM IN REPORT
Hello folks, I've have the following A table called: "TBL_CEDULE" with the fields names: Index, TIME, TRANSPORTEUR OU CONTAINER, SEAL#, DOOR#, SHOES, ALDO, FEETFIRST, TRANSIT, GLOBO, HANDBAGS, ALDO1, NONALDO, ACCESSORIES, ALDO2, NONALDO1. A report based on the table, and in the detail section of the report I have the following text boxes: SHOES, HANDBAGS, ACCESSORIES which I will like to have a formula that calculate the sum of the quantity entered in the previous fields. For example: If I enter 100 in "ALDO" , 100 in "FEETFIRST", 100 in "TRANSIT&quo...

VBA code to sum a row: syntax needed
I've got some code going, and need to perform a function if the sum of cells between columns DZ and EP on every row exceeds zero. What is the code to sum a range? Range("dz" & selection.row).value + Range("ea" & selection.row).value + etc is too cumbersome. What's the better way? Thanks! One way: Sub test() With Selection Range("dx" & .Row) = WorksheetFunction.Sum _ (Range("dz" & .Row & ":ep" & .Row)) End With End Sub Regards Trevor <slingsh0t@hotmail.com> wrote in message news:1121...

Sending Problem
I have an Exchange 2003 server that was working fine until I installed SP1. I have a problem sending or receiving attachments more than 2 mb. I have gone into system manager and changed my limits to no limit and I still having the same problems. Are there any other settings that I need to look at? I have also set all of the user accounts to the defaults. Have you ruled out your Exchange based Anti-virus software. Some of the new versions also have attachment control which will override your SMTP settings. Teo >-----Original Message----- >I have an Exchange 2003 server that was wor...

print area printing problem
Hi: I am attempting to print a number of seperate columns which are all seperated by columns i don't want to print. When i select the columns(using the control button) and attempt to print the selection, each of the selected columns print on their own seperate page. How can I get them to print next to each other on the same page? Thank you. --- Message posted from http://www.ExcelForum.com/ "koki" <koki.yuhfy@excelforum-nospam.com> wrote in message news:koki.yuhfy@excelforum-nospam.com... > Hi: > > I am attempting to print a number of seperate columns which...

Problem with character set in Excel 2003 ?!
Firstly, I'm sorry if my english is not on appropriate level. I am a new user in Excel and I'm competent only in graphical Exce interface. I have a serious problem with my work with Excel 2003. I' opening *.wk1 files (Lotus 1,2,3) without problems. This filetype is i the list of Excel filetypes, so the window "Text import wizard" (whic appear after opening an unknown file for Excel, like *.txt file fo example) does not appear. These *.wk1 files were created in MS-DOS i the past and were written on cyrillic alphabet, so they have differen character set from the default i...

Problem with the Outlook interface on one client
We have problem with the outlook interface on one of our computers. The user gets blank windows when he is going into a specific card in MS CRM (like contact, account, task and so on) Lists work fine. This problem occurs after he has been using Outlook for a while. The problem never occur in the webinterface and if he restart outlook it also works fine, but only for a while... Does anyone have any idea? Sounds like a strange problem. I would try: 1. Clear the IE temp files 2. Repair the CRM Outlook Client and apply latest update rollup Good luck. Frank Lee, Microsoft Dynamics CRM MVP...

CAsyncSocket connect problem #3
Hi, I write a server-client architecture application. I put the client and server applictions on the same PC. The client connect successfully to 127.0.0.1. The server can get the message sent from client. In the afternoon, I put the client application to another PC and it work well. Now, I execute it , but I get the error code: ============================================== WSAEWOULDBLOCK (10035) Resource temporarily unavailable. This error is returned from operations on non-blocking sockets that cannot be completed immediately, for example recv when no data is queued to be read from ...

Ms CRM on IE7 Problem.
Hope there is a solution for this. I have client that has gone through UAT/Testing phase using IE6. One of the thing they complain is the amount of browser window (popup). After browsing certain entity/date, the browser windows that are opened for each entity are too many, and user quite confuse to browse around.Sometimes they need to open some window to do data comparing. But luckily, I can suggest them to see title bar/title window to determine which data they want to open. Windows XP has a nice way to group same window together, so that they can see each windows title easily in the Windows ...

Loan problem
Hi, I have a loan setup that last for another +1000 payments, it was working fine showing the breakdowns of interest versus principal and consolidating downloaded transactions that matched until December, 11th. As of then it has died, the transactions are still downloaded but are not referenced in the loan account. Anyone have any suggestions what to look for and how to put these payments to the loan so things are back on track? In microsoft.public.money, Ted wrote: >Hi, I have a loan setup that last for another +1000 >payments, it was working fine showing the breakdowns of &...

Problem with Avg calculation in subform
Hello, I am having a problem with an average calculation on my form. I have a continuous form with a field called "Score" and that field can either have a numerical value in it or an "NA" in it. I have the following code in "score." =IIf([Score]=0,"NA",[Score]) The problem is, I want to take an average of all the NUMERICAL scores in that in subform, and not include the NAs (0s). However....I am getting an average INCLUDING those zero amounts and I want to exclude those. here is the code i'm currently usinig in that average score field: =Av...

Problem with menu & toolbar
Hi, I created a chunk file by extracting the new code to a dictionary and then transferred the altered forms & reports to this dictionary. Now when the chunk file auto installs sometimes the menu I added is missing. On some machines menu comes up & the toolbar is missing. So I tried just creating the chunk file from the entire modified Dynamics.dic. If I this chunk file is installed then I always see the menu & toolbar. I think its something to do with some settings... Any help is appreciated... Thanks & Regards, Sup Hello sup, Creating a chucnk file from the whole dic...

Windows Positioning problem
Hi, I want to position the windows belonging to same thread and process to be in position such a way that no other window should appear/positioned in between them. Is there any way to do this please let me know. Thanks Anup <anup.kataria@gmail.com> wrote in message news:1163678463.382822.101820@k70g2000cwa.googlegroups.com... > Hi, > > I want to position the windows belonging to same thread and process to > be in position such a way that no other window should appear/positioned > in between them. > You can use EnumThreadWindows() to iterate the thread's wi...

Outlook 2007 + Exchange 2003 problem
When i setup outlook 2007 on our internal network everything works fine. However when the user takes the computer to an external network Outlook 2007 does not work. It just says trying to connect and will never go to a connected status. Once i delete the outlook profile and recreate it everything works fine. We have had this same problem happen on about 5 different machines. All with outlook 2007, why is this happening? How can i resolve this problem? How is he trying to connect to the exchange server? RPC over HTTP or classic Exchange? -- Diane Poremsky [MVP - Outlook] Author, Teach ...