• Status: Solved
• Priority: Medium
• Security: Public
• Views: 278

# Need help with building an expression in Access

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
Gerhardpet
• 4
• 3
• 2
• +2
3 Solutions

Commented:
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

Author Commented:
let me try and will let you know if it works
0

Commented:
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

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

Commented:
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

Commented:
See attached database, hope that works for you.

Daniel

QtyOnHand.zip
0

Commented:
Or arguable simpler if rounding down is OK ...

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

Author Commented:
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

Commented:
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

Author Commented:
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

Commented:
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

Author Commented:
Great! Thank you Daniel.

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

## Featured Post

• 4
• 3
• 2
• +2
Tackle projects and never again get stuck behind a technical roadblock.