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

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
Asked:
Gerhardpet
  • 4
  • 3
  • 2
  • +2
3 Solutions
 
Dale FyeCommented:
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
 
GerhardpetAuthor Commented:
let me try and will let you know if it works
0
 
datAdrenalineCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Dale FyeCommented:
Never realized that Round() worked that way.  Good thing I rarely use it.
0
 
GRayLCommented:
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
 
danishaniCommented:
See attached database, hope that works for you.

Daniel

 QtyOnHand.zip
0
 
datAdrenalineCommented:
Or arguable simpler if rounding down is OK ...

Int([Quantity_Onhand]/iif(myFld="BVSTKUOM",1,[Qyt_Factor]))
0
 
GerhardpetAuthor Commented:
datAdrenaline,
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
 error
0
 
Dale FyeCommented:
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
 
GerhardpetAuthor 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
 
danishaniCommented:
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
 
GerhardpetAuthor Commented:
Great! Thank you Daniel.

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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