Solved

# Access 2000 Query to use the larger of two numbers

Posted on 2006-06-29
250 Views
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
Question by:schmir1
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 35

Accepted Solution

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

ID: 17014456
Thanks works great.
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
###### Suggested Courses
Course of the Month4 days, 14 hours left to enroll