using iif but coming up with error#

ok i have the if down but its causing me a brain drain lol

i want to divide my hours utilised by the amunt of pallets moved but all its 
coming out with is an error# sign

PPH: IIf([Delivery Type]="Palletised",[hours]/[pallets])

PPH = pallets per Hour

any ideas
0
Utf
12/5/2007 3:13:02 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
1161 Views

Similar Articles

[PageSpeed] 25

There are two things that stand out.
First, you have no False condition specified in your IIf.  That is what 
value to assign if  [Delivery Type]<>"Palletised"

The other is that you cannot divide by zero or Null.  You need to include 
code to transform the values of 0 and Null for the divisor and you need to be 
sure the number you are dividing is not Null:

PPH: IIf([Delivery Type]="Palletised",Nz([hours],0)/Nz([pallets],1),0)
-- 
Dave Hargis, Microsoft Access MVP


"Rivers" wrote:

> ok i have the if down but its causing me a brain drain lol
> 
> i want to divide my hours utilised by the amunt of pallets moved but all its 
> coming out with is an error# sign
> 
> PPH: IIf([Delivery Type]="Palletised",[hours]/[pallets])
> 
> PPH = pallets per Hour
> 
> any ideas
0
Utf
12/5/2007 3:25:03 PM
ok itried your string which worked exactly like my version but still gave an 
error i believe its because the hours is in time rather than integer

"Klatuu" wrote:

> There are two things that stand out.
> First, you have no False condition specified in your IIf.  That is what 
> value to assign if  [Delivery Type]<>"Palletised"
> 
> The other is that you cannot divide by zero or Null.  You need to include 
> code to transform the values of 0 and Null for the divisor and you need to be 
> sure the number you are dividing is not Null:
> 
> PPH: IIf([Delivery Type]="Palletised",Nz([hours],0)/Nz([pallets],1),0)
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "Rivers" wrote:
> 
> > ok i have the if down but its causing me a brain drain lol
> > 
> > i want to divide my hours utilised by the amunt of pallets moved but all its 
> > coming out with is an error# sign
> > 
> > PPH: IIf([Delivery Type]="Palletised",[hours]/[pallets])
> > 
> > PPH = pallets per Hour
> > 
> > any ideas
0
Utf
12/5/2007 3:35:00 PM
That may be the problem.  Time is a decimal value.  It may be you need to 
convert Hours to an integer
-- 
Dave Hargis, Microsoft Access MVP


"Rivers" wrote:

> ok itried your string which worked exactly like my version but still gave an 
> error i believe its because the hours is in time rather than integer
> 
> "Klatuu" wrote:
> 
> > There are two things that stand out.
> > First, you have no False condition specified in your IIf.  That is what 
> > value to assign if  [Delivery Type]<>"Palletised"
> > 
> > The other is that you cannot divide by zero or Null.  You need to include 
> > code to transform the values of 0 and Null for the divisor and you need to be 
> > sure the number you are dividing is not Null:
> > 
> > PPH: IIf([Delivery Type]="Palletised",Nz([hours],0)/Nz([pallets],1),0)
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "Rivers" wrote:
> > 
> > > ok i have the if down but its causing me a brain drain lol
> > > 
> > > i want to divide my hours utilised by the amunt of pallets moved but all its 
> > > coming out with is an error# sign
> > > 
> > > PPH: IIf([Delivery Type]="Palletised",[hours]/[pallets])
> > > 
> > > PPH = pallets per Hour
> > > 
> > > any ideas
0
Utf
12/5/2007 3:52:03 PM
Reply:

Similar Artilces:

Period Sales Analysis Report Error
I updated to ver 10 from 9 SP2 a couple weeks ago and everything has worked relatively well (Thanks to all newsgroup members who assisted) I am getting an error "Error in equation 'Net Sales'". Has anyone else seen this and what did you do? I'm guessing this is a modified report? Open the report in Report Writer and check out the Calculated field 'Net Sales'. You will probably find an error there. If I can't figure it out just by looking, I'll look at the v 9 report to see what the calc was trying to come up with and then correct the formula in v ...

After Re-Install
HELP!!! After a crash and a re-install of Office we are now getting user name and password errors in Outlook 2002. We can access the same accounts via a www interface with the same account information. PLEASE help. I've downloaded all the service packs for XP and Office and I've tried un-installing and re-installing including deleting all of the profile information I can find and deleting Outlook's registry, but to no avail. I'm obviously missing something. Any help would be fabulous. Please e-mail Monica@ViaSollertia.co.nz Thanks so much!!! Monica ...

How to use outlook address in Excel
Hello, I have an Excel sheet which I use as an invoicing-application. I would like to retrieve address-data from Outlook where I keep all my contact-data of my customers. So, I want to select a customer from my Outlook contactlist when I am writing a new invoice in Excel. In Word, I have a macro which does this, but unfortunately the Application.GetAddress does not work in Excel. Can somebody help me ? "Henny Slokker" wrote: > Hello, > > I have an Excel sheet which I use as an invoicing-application. I would like > to retrieve address-data from Outlook where I...

Exchange Extension Error
I'm trying update an email address and an error pops up. Microsoft Active Directory - Exchange Extension A Local Error has occured. Facility: Win32 ID no:8007203b Microsoft Active Directory - Exchange Extension Everything else seems to be working ok. Any ideas? Thanks, Q does this apply? http://support.microsoft.com/default.aspx?scid=kb;en-us;329642 "Quan" <anonymous@discussions.microsoft.com> wrote in message news:1a2001c4abc4$e8f2ea30$7d02280a@phx.gbl... > I'm trying update an email address and an error pops up. > > Microsoft Active Directory - Exchange...

Hidden files in Ms-Query cause ODBC connect errors or Query is wac
I have a Query fetch_from_bob that moves data (collapses records,unique key search, etc...) from excel spreadsheet "bob" to excel spreadsheet "fred". Fred and bob live in the same directory" c:\123directory" on the same computer. I allow this query to execute/refresh data automatically when one of the query paramaters is changed. This works great. I also have a macro that invokes the query to refresh the data whenever the user desires. This works great as well. The macro literally has all the VB code to execute the query. But I'm just a part time progra...

IE8 uses 95+% of cpu after update to sp3
I just updated to XP sp3 and did updates after that and IE8 is running so slow. I loaded task manager to see what was happening and IE8 was using up to 99%. My system was a slow XP sp2 but did a disk clean and defrag and it was running a lot faster. Apps load and run faster after sp3 but not IE8. Thanks for any suggestions mx5 wrote: > I just updated to XP sp3 and did updates after that and IE8 is > running so slow. I loaded task manager to see what was happening > and IE8 was using up to 99%. > > My system was a slow XP sp2 but did a disk clean and defrag and...

PLEASE HELP--ERROR MESSSAGE
I have two error messages. The reason why I am getting this is because I got a new computer and I copied my old outlook information and put it in the new computer. Everytime I open my outlook this message appears and the only thin I have to do is click ok and everything works. But how do I fix it? I did reload outlook serval times and there error message still comes even if I do not put my old information in it. Error 1: "The add-in "C:\Program Files\Microsoft Office\Office\SBCMSYNC.DLL" could not be installed or loaded. This problem may be resolved by using Detect and Repai...

FWD: See this corrective update which comes from the Microsoft
--aolkeahgr Content-Type: multipart/related; boundary="wbqnfbpivy"; type="multipart/alternative" --wbqnfbpivy Content-Type: multipart/alternative; boundary="lzvglunuqmeli" --lzvglunuqmeli Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Customer this is the latest version of security update, the "November 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to maintain the se...

GUID/Outlook format link giving error "Unable to open the seleted folder or item"
Hi, I have created custom forms in multiple public Outlook folders and when I send the GUID type link to the user, they receive an error: "Unable to open the seleted folder or item". The same happens if I send the Outlook type link. However, if I ask the user to go to the folder and find the appropriate posting, then the form opens and after that the link works, too. Is this due to some Exchange server setting? My clients are not the type that tolerate opening the form the long way, they want a one click solution. Please help, this is driving my team crazy. Any ideas are welcome...

550 5.7.1 relay error messae
I have select number of Clients (3) out of 27 who we can not send emails to, they are in a separate domain from ours. Your message did not reach some or all of the intended recipients. Subject: test 4:35pm Sent: 2/27/2007 4:34 PM The following recipient(s) could not be reached: Sean ***** on 2/27/2007 4:34 PM You do not have permission to send to this recipient. For assistance, contact your system administrator. <mail.n*******.org #5.7.1 smtp;550 5.7.1 Unable to relay for tsc16.****.****@***.local> Rhodes.messiah@gmail.com wrote in news:1172615546.953350.313930 @j...

VB: using a string to set a range object?
I'm a bit new to the excel "range" object type. I was suprised to see that while I can do: dim chunk as Range chunk = .Range(A5:B6) I apparently cannot do: dim chunk as Range dim stuff as string string = "A5:B6" chunk= .Range(string) How can I concatenate up a string describing a range, and then use it to define a range object's target cells? - Ross. Oops, I meant chunk = .Range("A5:B6") in the first example - I forgot the quotes. R. "RGK" <nothanks@nospam.go> wrote in message news:RqydnSWzbu_OEZbeRVn-2A@...

Rules in Error
When I open Outlook 2002 I receive a dialogue box which states Rules in Error - Server Requested Client Action MoveCopy, Unable to create dest msg. I don't know where this came from. Thanks for the help in advance. ...

How to use CSplitterWnd for Dynamic Nested Splitter window?
Using VC++ (.NET) I am trying to implement a Dynamic Nested Splitter window using CSplitterWnd. I haven't found any documentation that explains how to get this to work properly and the only examples I have found also do not work properly. There are plenty of examples of Static Nested Splitter windows, but I need an example of a Dynamic one. The static splitter windows won't work for my requirements. Are there any examples or documentation that explains how to properly implement a Dynamic Nested Splitter Window using CSplitterWnd? Thanks, Dave Hi Dave, > Using VC++ (.NE...

error message 01-01-10
Unable to send or receive messages for the Hotmail (**********) account. To send and receive messages in your Hotmail account, go to http://hotmail.live.com on the Web, or try again later. To get help from Windows Live Customer Support, go to http://support.live.com and click Windows Live Mail in the list of services. Subject 'Greetings' Windows Live Mail Error ID: 0x80070057 I want to know how to dump this popup. I don't need to send the message and everytime I open windows live mail, this error message regarding an unable to compete task shows up. How do I ge...

Purchase Order Entry Error Message
While in version 8.0g34 and Purchase Order Entry window opened. User accidentally minimized the window instead of closing the window. User closed window while minimized and the following message appeared. Unhandled script exception Object has no reference EXCEPTION_CLASS_SCRIPT_BAD_PARAM OK User clicks ok approx. 10 times and the window finally closes. Why does this happen? Perhaps a fix should be applied. Thanks! ---------------- 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...

C++ runtime error
When I open Outlook I get the following error: ______________________________________ Mcrosoft Visual C++ Runtime Libary Runtime Error! Progam: C:\Progra~1\Micros~2\Office10\Outlook.exe This application has requested the runtime to terminate it in an unusual way. Please contact the application's support team for more information. ______________________________________ I have already tried to run a repai and to uninstall and reinstall and I still get the same error. Any ideas? I GET THE SAME MESSAGE, BUT IT HAS ONLY STARTED SINCE I UPGRADED TO NORTON ANTIVIRUS 2004 TRIED NUNERO...

Conversion error
Hi, I have a table called stats that has the following columns: jobname, ahours, aminutes, aseconds, amilliseconds. I need to get an average of each job ran but I need to be able to concatenate ahours, aminutes, aseconds, amillisecons into one column. I am trying to run a simple query: SELECT JobName, CONVERT(NVARCHAR (100),(AVG (AHours) + ':' + AVG (AMinutes) + ':' + AVG (ASeconds) + ':' + AVG (AMilliseconds))) AS 'Average Time' FROM stats GROUP BY JobName I am getting the following error: Msg 245, Level 16, State 1, Line 1 Conversion failed whe...

Coding Convention for using Binary FlagWords?
Hi all --- I'm thinking of putting the .Tag property to use by using a binary ("bitwise") encoded flag word. Over time I've come across different possible uses for .Tag but have never standardized how I use it. Has someone developed a convention for "parsing" binary flag words? A simple IF works if I only need to test for one flag, but if I need to check for the presence of multiple bits the only construct that comes to mind is a series of IF statements .... something along the lines of: Enum FlagWord FlagA = 1 FlagB = 2 FlagC = 4 .......

Using Outlook client for CRM 3.0 in a remote office
I need to set up access to the corporate CRM from several remote offices. All of them have VPN connections (Windows or Checkpoint). What are the steps required to configure remote clients to be able to use CRM features in Outlook. Remote users can connect to CRM through Internet Expolorer. Thanks. Assuming they are connected, the isntallation over a VPN connection should not be any different than a typicaly installation. If the connection is slow though, the first synch & Go Offline process will be noticiably slower though. -- Matt Parks MVP - Microsoft CRM "mkatsev"...

Visio Error
I was working in Visio on a document and I accidently hit my power switch (the one on the floor on the cord) and my PC turned off. When my machine rebooted and I tried to access the same Visio file, a message tells me that I cannot access the file because it is NOT a Visio file???? I tried renaming it, didn't help. This is the second time this happened to me and I can't understand why the program is not recognizing the file. It's very frustrating....does anyone have any answers to this? - Sally Another option is to turn on "Autorecovery" It will automatic...

using std::deque in multiple threads
I have a thread that listens on a message queue and populates a std::deque with events from that message queue, but only holds the latest 100. In my main thread, I want to populate a listbox with the information from that deque. So, it ends up being that one thread can add/remove items from the deque while another thread is trying to iterate through them. How can I make this thread-safe? Thanks, PaulH The code looks a bit like this: std::deque<MESSAGE_TYPE> m_dequeMessages; CMyClass::MessageThread() { //... while (ReadMsgQueue(hMsgQueue, &msg,...)) { m_dequeMessages.pu...

Use a wildcard within edit/replace
Hello Is there a method of using a wildcard function within edit/replace (in Excel 2003) so that I can tweak the way a formula works? The example I have is the following formula =IF(ISERROR(VLOOKUP($A8,DataImport!$A:$J,5,FALSE)),"",VLOOKUP($A8,DataImport!$A:$J,5,FALSE)) which I would like to change to =IF(ISERROR(VLOOKUP(TEXT($C8,"0000),DataImport!$D:$E,2,FALSE)),"",VLOOKUP(TEXT($C8,"0000"),DataImport!$D:$E,2,FALSE)) The issue is (I think) that this formula is repeated many times over in one column over a number of worksheets - therefo...

error message when trying to install WMP 10 or 11
My operating system is Windows XP Home Edition 2002. I'm trying to download either WMP 10 or 11 and I get a message saying it was not possible to complete setup, see webhelp for more assistance, however there is no additional information when I click Web help. the Error message is 8007F0DA. Can anyone help me? Thanks. On Tue, 30 Mar 2010 07:54:01 -0700, Nate <Nate@discussions.microsoft.com> wrote: > >My operating system is Windows XP Home Edition 2002. I'm trying to download >either WMP 10 or 11 and I get a message saying it was not possible to &g...

Wordwrapping a long sentence using F2, how to end the process?
Using Excel 2002...I pasted in a long sentence, and it goes off the right side of the screen. I know that I can make it word wrap by hitting F2. I've done that and it works nicely. But I was told that I could end this process by hitting Alt-Enter, and that doesn't work... all that happens after Alt-Enter is a blank new row opens up below the word-wrapped rows. If I hit Alt-Enter again, another blank row opens up below. How can I end this F2 word-wrap and go on with other business. John alt-enter is used to force a new line in a cell. If you want to wrap the text a...

excel 2000 message
excel 2000 message - 'cannot use object linking and embedding' Were they hit by the MSBlast worm? One poster (Lutz Meyer) guessed that this was the cause of his problems. I haven't seen any confirmation/denial, but you may want to read his post: http://groups.google.com/groups?threadm=3F3971AF.FA4490F5%40msn.com Post back with your results. I'm curious if that was the problem. (It's come up quite a few times since MSBlast hit.) bill bootle wrote: > > excel 2000 message - 'cannot use object linking and > embedding' -- Dave Peterson ec35720@msn.c...