Solved

# Need help with building an expression in Access

Posted on 2011-05-03
259 Views
I need help with building an expression in Access. I’m new to SQL so I’m wondering if someone can create the expression for me?
See the attach excel file for my table
This is what I need:
If CODE = BVSTKUOM then QUANTITY_ONHAND and
If CODE <> BVSTKUOM then QUANTITY_ONHAND / QYT_FACTOR
On the <> part if QUANTITY_ONHAND / QYT_FACTOR is 42.54 then it should be rounded up to the nearest full number (e.g. 42.54 = 43). Also if QUANTITY_ONHAND / QYT_FACTOR <= 0.99 then it should be 0

Q-STOCK-ONHAND.xlsx
0
Question by:Gerhardpet

LVL 47

Assisted Solution

Expr: iif([YourField] = "BVSTKUOM", [Quantity_OnHand], iif([Quantity_OnHand]/[Qyt_Factor] < 1, 0, Round([Quantity_OnHand]/[Qyt_Factor], 0)))

Replace "YourField" with "Code",  EE won't let me put "Code" within brackets.
0

LVL 1

Author Comment

let me try and will let you know if it works
0

LVL 11

Assisted Solution

Where are you planning on using this expression?  Are you apposed to a VBA User Defined Function?

Also, the Round() function will perform bankers rounding:
? Round(42.5,0)
42

? Round(43.5,0)
44

So if you want the "next highest number", the I would suggest a slight mod to Fyed's solution:

Expr: iif([YourField] = "BVSTKUOM", [Quantity_OnHand], iif([Quantity_OnHand]/[Qyt_Factor] < 1, 0, 0-Int(0-[Quantity_OnHand]/[Qyt_Factor])))

0

LVL 47

Expert Comment

Never realized that Round() worked that way.  Good thing I rarely use it.
0

LVL 44

Expert Comment

This is a lot simpler but instead of rounding, it uses the Int() function.  Close enough?

iif(myFld="BVSTKUOM",[Quantity_Onhand,Int([Quantity_OnHand]/[Qyt_Factor]))
0

LVL 12

Expert Comment

See attached database, hope that works for you.

Daniel

QtyOnHand.zip
0

LVL 11

Expert Comment

Or arguable simpler if rounding down is OK ...

Int([Quantity_Onhand]/iif(myFld="BVSTKUOM",1,[Qyt_Factor]))
0

LVL 1

Author Comment

I tried yours and this is the error I got. There will be no user funtions in the database. The database is just to export data from an ERP system

0

LVL 47

Expert Comment

This error is because that field exists in more than one table included in your query.  You need to explicitly identify which table that field should come from.  It would look something like:

[tablename].[fieldname]

replace 'fieldname' with 'code'
0

LVL 1

Author Comment

Ok I have taken the expression from Daniel’s database

Question...Why did you give me two expressions? I'm too new to really figure out the difference
0

LVL 12

Accepted Solution

Oh, I am sorry, that was for testing purpose to make sure I had the correct results;

Attached database with one Expression.

HTH,
Daniel

QtyOnHand2.zip
0

LVL 1

Author Comment

Great! Thank you Daniel.

I will award some points to the other Experts as well. I appreciate the help
0

## Featured Post

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
Viewers will learn how the fundamental information of how to create a table.