Help with an expression to calculate a profit from one of three fi

Using Access 2003 SP3 - in Forms

I know I'm probably asking a very complex question, so a very big THANK YOU 
to whomever can help.

I am doing a detailed inventory and need help with an expression in my form 
that will calculate my profit based on one of three fields. Only one field 
would have the end data.

To clarify what I am working with: Most stock items come by the "unit" and 
contain multiple "subunits" which, in turn, contain smaller "pieces."  
However, some "units" only come with "subunits" and some only come as "units."

Here is what I have done so far, along with the (format) and followed by the 
expression.

Unit Cost (currency) ~ CS:Unit Cost
Units on hand (number) ~ CS:Units on hand
Cost of all Units on hand (currency) ~ =Nz([UnitCost],0)*Nz([UnitsOnHand],0)
Number of Subunits per Unit (number) ~ CS:Number of Subunits per Unit
Subunit Cost (currency) ~ 
=IIf([NumbSubUnitsPerUnit]=0,Null,Nz([UnitCost])/Nz([NumbSubUnitsPerUnit]))
Subunits on hand (number) ~ CS:Subunits on hand
Cost of all Subunits on hand (currency) 
~	=Nz([SubUnitCost])*Nz([SubUnitsOnHand])
Number of Pieces per Subunit (number) ~ CS:Number of Pieces per Subunit
Piece Cost (currency) ~ 
	=IIf([NumbPiecesPerSubUnit]=0,Null,Nz([SubUnitCost])/Nz([NumbPiecesPerSubUnit]))
Pieces on hand (number) ~ CS:Pieces on hand
Cost of all Pieces on hand(currency) ~ =Nz([PieceCost])*Nz([PiecesOnHand])
Total Cost of All on Hand (currency) ~ 
=Nz([CostUnitsOnHand])+Nz([CostSubUnitsOnHand])+Nz([CostPiecesOnHand])
Selling At (currency) ~ CS:Sell At

Profit (currency) ~ =[SellingAt]-[??cost on hand??]**

**Here is where I need the help. I need an expression that will calculate 
the profit based on whichever of the following fields contain the final "cost 
on hand" data:

 Cost of all Units on hand  , Cost of all Subunits on hand , or Cost of all 
Pieces on hand

Typically the final cost on hand data will be in "Cost of all Pieces on 
hand", but occasionally it will be in one of the other two. Is there an 
expression that I can use that will find the last of the three calculated 
data fields and use that data?

If there isn't, how would I go about creating a combo box list that would 
include only the calculated data in those fields for only the current record? 
And if this is possible, could I then use the value displayed in the combo 
box to figure the profit?

Again, Thanks ahead of time.
J
0
Utf
5/29/2010 10:42:01 PM
access.forms 6864 articles. 2 followers. Follow

2 Replies
961 Views

Similar Articles

[PageSpeed] 54

mmm well my guess would be

[cost on hand]=3Diif(Nz([PieceCost])*Nz([PiecesOnHand])<>0,
Nz([PieceCost])*Nz([PiecesOnHand]),iif(Nz([PieceCost])*Nz([PiecesOnHand])<>=
0,
Nz([PieceCost])*Nz([PiecesOnHand]),Nz([UnitCost],0)*Nz([UnitsOnHand],
0)))

This checks if the cost of pieces is 0 if not then it says the cost on
hand is the cost of pieces if it is 0 then it checks the subunits if
the sub units cost is not 0 it will use the subunits cost as cost on
hand and if that is 0 it will use the units as cost on hand.

Though this code won=92t work if you have pieces lets say and they have
no cost to the company then I would use this code

[cost on hand]=3Diif(Nz([Pieces on hand])<>0,
Nz([PieceCost])*Nz([PiecesOnHand]),iif(Nz([Subunits on hand])<>0,
Nz([PieceCost])*Nz([PiecesOnHand]),Nz([UnitCost],0)*Nz([UnitsOnHand],
0)))

This will do the same but will check to see if the pieces on hand
value is 0 and so on rather than the cost value

Hope this helps

Regards
Kelvan
0
Lord
5/30/2010 9:27:56 PM

"Lord Kelvan" wrote:

> mmm well my guess would be
> 
> [cost on hand]=iif(Nz([PieceCost])*Nz([PiecesOnHand])<>0,
> Nz([PieceCost])*Nz([PiecesOnHand]),iif(Nz([PieceCost])*Nz([PiecesOnHand])<>0,
> Nz([PieceCost])*Nz([PiecesOnHand]),Nz([UnitCost],0)*Nz([UnitsOnHand],
> 0)))
> 
> This checks if the cost of pieces is 0 if not then it says the cost on
> hand is the cost of pieces if it is 0 then it checks the subunits if
> the sub units cost is not 0 it will use the subunits cost as cost on
> hand and if that is 0 it will use the units as cost on hand.
> 
> Though this code won’t work if you have pieces lets say and they have
> no cost to the company then I would use this code
> 
> [cost on hand]=iif(Nz([Pieces on hand])<>0,
> Nz([PieceCost])*Nz([PiecesOnHand]),iif(Nz([Subunits on hand])<>0,
> Nz([PieceCost])*Nz([PiecesOnHand]),Nz([UnitCost],0)*Nz([UnitsOnHand],
> 0)))
> 
> This will do the same but will check to see if the pieces on hand
> value is 0 and so on rather than the cost value
> 
> Hope this helps
> 
> Regards
> Kelvan
> .
> 

I have to apologize, I managed to screw up which fields I needed to use to 
perform the profit calculation.

I originally stated that I needed the calculation to use whichever of these 
three fields was the last field to have a data entry:
Cost of all Units on hand , Cost of all Subunits on hand , or Cost of all
Pieces on hand 

I also neglected to say that I was only trying to find the profit for a 
single item, in which case I may notice my big mistake about the fields. In 
basic math, the profit would be figured as follows: 

PROFIT = [SellingAt]- the cost of a single item

The "cost of a single item" would be whichever of these three fields was the 
LAST  field to have a data entry:

[UnitCost], [SubUnitCost], or [PieceCost]

each of which are calculated based on the previous set of fields. Subunits 
are based on units and pieces are based on subunits.

Typically the "cost of a single item" data will be in [PieceCost], but 
occasionally it will be in one of the other two. Additionally, if the final 
"cost of a single item" data is in the [PieceCost] field, there will also be 
data in [UnitCost] and [SubUnitCost] fields that helped calculate the 
[PieceCost]. If the final "cost of a single item" data is in the 
[SubUnitCost] field, then there will also be data in the [UnitCost] field and 
the [PieceCost] will be Null. If the final "cost of a single item" data is in 
the [UnitCost] field, then the [SubUnitCost] and [PieceCost] fields will both 
be Null.

Is there a calculated expression that I can use that will find the last of 
the three calculated data fields and use that data to figure the profit? 

If there isn't a way to do what I'm asking using calculated expressions, is 
there a way I can set the [Profit] field to ask me which of the three field's 
data I'd like to use? Or would it be easier to create a "cost of a single 
item" field that would allow me to pull in the data from one of those three 
fields so that the expression to calculate the profit in the [Profit] field 
would be as simple as the basic math example above?

Again, I apologize for my initial confusion and thanks again in advance.
J 
0
Utf
6/2/2010 7:40:57 PM
Reply:

Similar Artilces:

How to used HOOK in OutLook Express
Hello All : I want to use hook control .When outlook express received new mail and remove the new eamil to myself mailbox . PS:Use C++ or HOOK .(not outlook express rule) Thanks Stiwin Lee ...

Help with functions
If I have the following formula in a worksheet to bring up a message box =IF((F4-E4)>=10,Amend()+F4-E4,F4-E4) How can I use a function to transfer some information from different cells to a new worksheet if the formula above works? You can't have a function transfer information to another cell, it will return it's info in the cell where it's located, you would need a formula(s) in the cells where you want this information. Otherwise you would need VBA Regards, Peo Sjoblom "Monty" wrote: > If I have the following formula in a worksheet to bring up a me...

HELP!!!!!!!!
I am getting alot of spam sent to my domain that does not have an actual email address. The problem is that the server then tries to send a message to the sender (who is bogus) with a delivery failure. My ISP has since shut down may mail once already. How can i stop the server from responding to mail coming in with incorrect adresses. I am using exchange 2k3 and windows enterprise edition 2k3. I am new to exchange and still in the process of learning it. Any help would really be appreciated. Are the SMTP addresses incorrect or are they blank? You can configure Exchange's Sender F...

Help from Dave Peterson
Dave, Please see thread headed "Copying multiple sheets from one book 2 another and undertake special " dated 2/8/05. I have added some more text in there as I have finally got round to doing it and have experienced some problems. My name shows up as "Ann" rather than Pank as my good lady has been using my account. Thanks ...

two checking accounts data downloads to just one of the accts
I have two checking accounts with our bank and when I download transactions into Microsoft Money for either account, all the transactions go to only one of the accounts set up in Money. I was prompted for the first download to our "main" account. Then when I went to download for the other account, I wasn't prompted where to download to... and all the transactions went into the first account. Did that make sense? My bank required we switch to Money from Quicken, so I am new to this. Tried to search in users guide and online for help. Help would be much appreciated - ...

help need to match a string
Hi everyone Hope someone have a solution for me. I have a table with product name and price in column a & b in sheet1 I have the formula in sheet2 where I input the product name in A1 an it search the product and give me the price. =VLOOKUP($A$1,Sheet1!$A$1:$B$1377,2,0). It works ok. Here my problem: I have a product name called "HK-2859 plus" and I need this product t be identified if I enter any portion of this product ("HK" or "2859" o "plus"....). appreciate your help Thank -- Message posted from http://www.ExcelForum.com Hi one way: Use t...

Please help me solve my problem
Hi, I Have a cell say a1 that has a continuously changing number it in and I want to track the highest number that it prints, and the same for the lowest number in cell a2. I would like these results to show up in the cells below them Im very new to programming and your help would be much appreciated many thanks Sacha David On Mar 5, 1:01=A0pm, Sacha David <sach1...@googlemail.com> wrote: > Hi, =A0I Have a cell say a1 that has a continuously changing number it > in and I want to track the highest number that it prints, =A0and the > same for the lowest number i...

Ten calendar harm one of the products
Ten calendar harm one of the products www.laptop-battery-inc.co.uk/spy-pen.htm ...

Help needed with the last hurdle on this project
Hi I've finally reached the limits of my Excel knowledge (what littl there was) and need some help to complete my project. What's missing now is some automation to make what I need to do a lo more user friendly. My problem (for anyone generous enough to tackle it) is posted below. It's a lot of text but it's the most concise way of explaining what want to be able to do. I've attached a file with the Excel work as far as I have been able t do. Thanks to anyone who gives this a crack - it's very much appreciated. Martin PART 1 Take the data from Column A and Co...

Migrating Mailboxes from one domain to another domain
I am having a hard time trying to figure out how to do something and was wondering if anyone could point me in the right direction. I need to migrate user mailboxes from one server to another, easy enough right... well here is where my issues are. server one is in say abc.local domain server two is in say xyz.local domain there are the same users in both domains, with the same naming convention. so to clear that up if i have a user called ttest in domain abc, user ttest is also in xyz. the old administration had seperated these domains due to function and security, but now they want to...

Calculation / Format Error
I'm using this formula in cell AE69 =IF(AL69="NO","",AE68*(AM69/100)) When the logic is true I want the cell value to be zero, but show as blank. When this cell is summed up with others, a #VALUE! is returned. I am assuming excel is seeing this cell as text and thus returning an error. I have tried adjusting the formula to: =IF(AL69="NO","0",AE68*(AM69/100)) I have selected the accounting formating for AE69 with the "£" symbol. However, instead of getting the £ symbol to the left and a dash representing z...

Help please, can't start up!!
When I start the PC, after the loading bar for Microsoft thing finishes,instead of going to login screen it gives me a blue screen, and it says Page_fault_in_nonpaged_area. This just happened overnight!!!! Help please. :( -- Excessive Here are some hints ( 'Bug Check 0x50: PAGE_FAULT_IN_NONPAGED_AREA' (http://msdn.microsoft.com/en-us/library/ms793437.aspx) ). Try starting the system in safe mode (keep tapping F8 after power-on). -- whs Additional info: - I use Spyware Doctor scan every few days - I left my computer on for the night like always,and ...

[Help] OnPaint Skipped?
I have an CTestWnd defined as follow class CTestWnd : public CWnd { DECLARE_DYNAMIC(CTestWnd) public: CTestWnd(); virtual ~CTestWnd(); protected: DECLARE_MESSAGE_MAP() public: afx_msg void OnPaint(); }; and i create an instance of CTestWnd as a child window of hWndParent LPCTSTR lpClassName; lpClassName=AfxRegisterWndClass(CS_VREDRAW | CS_HREDRAW |CS_GLOBALCLASS, ::LoadCursor(NULL, IDC_ARROW),(HBRUSH) ::GetStockObject(WHITE_BRUSH), ::LoadIcon(NULL, IDI_APPLICATION)); RECT rect; ::GetClientRect(hWndParent, &rect); m_Wnd.CreateEx(NULL,lpClassName, _T("TEST"...

outlook express #32
password not remembered even tho box checked, just installed oe6 have Xp.what do i do ? This newsgroup is for support of Outlook 97, 98, 2000 & 2002 from the Office family for Windows PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.public.windows.inetexplorer.ie5.outlookexpress for OE 5.x http://support.microsoft.com/newsgroups/default.aspx?ICP=GSS3&NewsGroup=microsoft.public.windows.inetexplorer.ie5.outlookexpress&SLCID=US&sd=GN&id=fh;en-us;newsgroupsmicrosoft.public.windo...

Changing info from one worksheet to the next
Hi all. I have linked my 55 sheets to one which is great. What i need to know now if possible. Each sheet has the same question over 11 columns Each row is dated and a numeric number from 1 - 10 in each row Now on the master sheet where everything is linked, is there a way that If i changed the date on the master sheet it would reflect the answers from the row with that date? At present the answers showing is for 01/03/10, but i would like to look at the totals for 08/03/10 and show the answers from each sheet for that date. I could have a sheet for each week, but im hoping there i...

SBS 2k and Exchange 2k Help
Hi All I'm having a problem with Exhange 2000. I support an office of about 5 people, and they are running SBS2000 with Exchange 2000, during the past couple of days they haven't been able to send/receive e-mail, and they haven't been able to browse the internet. I initially thought the server was being used for Relaying, however the queues are not very high (about 14 e-mails) and the Telnet tests suggest it can't be used for relaying. When I got there last night the server was fine and after a quick re-boot I sent numerous e-mails to my home account which I received, and I...

VBA Help #4
Hi, I have a list of clients with their associated outstanding dollar amount and their return (%). I would like to know if there is code that would allow me to break up these clients by return (%) groupings. For example, I would like to group below 10%, 10% to 17%, and above 17%. So at the client on the list that is just before 10% I would like to add two rows and put a dollar subtotal in the immediate row and sum the dollar amounts. Can anyone help? Thanks, Marino Public Sub ProcessData() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim LastR...

RMS should allow you to choose more than one item at a time
RMS should allow you to choose more than one item at a time, this would be helpful in creating PO's. Mutiple item selection is a standard in every software; you really dropped the ball with this one. ---------------- 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 message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.micr...

We would love To Help You With Your Microsoft Dynamics GP Issues
If you are having issues with Microsoft Dynamics GP- reports, need training, upgrades, ongoing support, or anything of the such- please feel free to email us at mtatum@integritymbs.com, or call us at 888.869.4090 ext 705. We would love to assist you. We provide install, implementation, ongoing services, training, customization, report writing, troubleshooting and much more. ...

Nightmare Bug. !! Plz Help
hi all at once when i try to edit or open some forms i got such error msg "There isn't enough memory to perfrom this operation. close unneeded programs and try the operation again." I have 1GB Ram so it is sure silly msg !! what to do thanks plz help -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200802/1 What version of Access are we talking about here? By any chance does this involve moving from one version to another? -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2...

time calculation
I need to do a check on the time.. if it is after midnight but before 5am then I need to minus 1 day off of the date. How could I do this? Hi If the Date and Time is in cell A1, use =IF(AND(MOD(A1,1)>VALUE("12:00 AM"),MOD(A1,1)<VALUE("05:00 AM")),A1+1,A1) ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** ...shortened versions would be =A1+AND(MOD(A1,1)>VALUE("12:00 AM"),MOD(A1,1)<VALUE("05:00 AM")) ** Posted via: http://www.ozgrid...

outlook express 6 #34
I received an email from websiteline@aol.com I cannot delete it I cannot receive any more emails from my hotmaill address unless I go direct to hotmail I cannot send an email with outlook express in other words the whole thing is jammed up How do I delete this email It wont delete by clicking delete keith <anonymous@discussions.microsoft.com> wrote: > I received an email from websiteline@aol.com > I cannot delete it > I cannot receive any more emails from my hotmaill address > unless I go direct to hotmail > I cannot send an email with outlook express > in other ...

Help, my views got messed up and "folder options" is now restricted
I have no idea what has happened here, I think vista somehow messed something up when I connected to a file share, but even that seems odd since I've been doing this for 6 months. I'm using Windows 98SE and have files shares on a LAN to a Vista 64 Home Premium machine. I can't choose "folder options" in the My Documents folder or any random subfolder. When I try to do so, I get a message "This operation has been cancelled due to restrictions in effect on this computer. Please contact your system administrator". I _AM_ the administrator, and there i...

Tracing and tracking email on one mailbox
1. I want to use a current copy of the Exchange 2003 database and scrub it for all emails regarding one mailbox. Is there a tool that will assist me in this? 2. How do I tell Exchange 2003 to notify me of any emails transmitted to and from a particular address? And yes, this in conjunction with #1. I want to track one user's mail without using delegate or adding myself to their Active Directory profile and having to create another profile in Outlook. 1. Have a read through this: http://wm.quest.com/reg/marketing/landing/ediscoveryexchange/. This product is ideally suited for ...

Find and loop help-multiple columns
I an trying to write a macro to search for a cell value in one column and see if it occurs in other columns I'm having 2 problems: 1. How can I look from the last used row up to row 4 in "myrange+5" as set below? 2. How can I look in all other columns beside the "myrange+5" column named "Route Number(s)"? Am I completely off track?? With lastperiod 'Find the last used column myrange = ActiveSheet.UsedRange.Columns.Count ActiveSheet.Cells(1, myrange + 2).Select 'find the last row therow = ActiveSheet.Cells.Fi...