Solved

Access 2000 Query to use the larger of two numbers

Posted on 2006-06-29
2
231 Views
Last Modified: 2008-02-01
I need to use the larger of two numbers in a calculation in my query.

Pseudo code would be like:

If Inventory_Qty > Order_Qty then
  ' Inventory_Qty - Components_needed = Surplus (below is the SQL)
  ([Inventory].[Quantity]-([Forms]![BOM_Queries]![txtBuilds]*[Qty per Asm])) AS Surplus)  
else
  'Order_Qty - Components_needed = Surplus
  ([Orders Subtable].[Quantity]-([Forms]![BOM_Queries]![txtBuilds]*[Qty per Asm])) AS Surplus)
end if

It has to be SQL as it is part of a Access 2000 Query.
0
Comment
Question by:schmir1
2 Comments
 
LVL 35

Accepted Solution

by:
Raynard7 earned 500 total points
ID: 17013914
Have you tried using an IIF statement;

this is where the test is in the first arguement, the result for true is in the second and the result for false is in the third,

ie Surplus: IIF(Inventory_Qty > Order_Qty, ([Inventory].[Quantity]-([Forms]![BOM_Queries]![txtBuilds]*[Qty per Asm]))),  ([Orders Subtable].[Quantity]-([Forms]![BOM_Queries]![txtBuilds]*[Qty per Asm]))))
0
 

Author Comment

by:schmir1
ID: 17014456
Thanks works great.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now