Solved

Access 2000 Query to use the larger of two numbers

Posted on 2006-06-29
2
226 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

867 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

17 Experts available now in Live!

Get 1:1 Help Now