Need help to build an MS Access Expression

place this in a column of a query

iif([PRICING].[FACTOR_PRICES]=1,PRICING.PRICE_FACTOR * PRICING.BVRTLPRICE01,PRICING.BVRTLPRICE01)

What are the possible values of [Pricing].[Factor_Prices]?

How about:

[Pricing].[BVRTLPRICE01] * iif(Pricing.Factor_Prices = 1, Pricing.PriceFactor, 1)

The values are COBOL Decimal Comp-3 and COBOL NumericSA. I'm linking to a Pervasive SQL database tables with Access

Didn't ask what type of data you were accessing, but the possible values of the [Factor_Prices] field.  My guess is that this is boolean (0 or 1), in which case you could use:

[Pricing].[BVRTLPRICE01] * iif(Pricing.Factor_Prices, Pricing.Price_Factor, 1)

yes it is a boolean (0 or 1)

I have tried all three now and either of them work. In Expr1 I should have a value of $2500

PRICING.FACTOR_PRICES = 1
PRICING.PRICE_FACTOR = 5
PRICING.BVRTLPRICE01 = 500

E.g. 5 * 500 = 2500

can you add the [BVRTLPRICE01], [Factor_Prices] and [Price_Factor] fields to the query and take another screenshot?

Also, copy the entire SQL string and post it as well.

Here it is. I have also attached the Access database with a few records

SELECT INVENTORY.CODE AS SKU, UNIT_OF_MEASURE.CODE AS UOM_CODE, UNIT_OF_MEASURE.UOM_DESCRIPTION AS UOM_DESCRIPTION, PRICING.FACTOR_PRICES, PRICING.PRICE_FACTOR, [Pricing].[BVRTLPRICE01]*IIf([Pricing].[Factor_Prices],[Pricing].[Price_Factor],1) AS Expr1, PRICING.BVRTLPRICE01
FROM ((INVENTORY LEFT JOIN UNIT_OF_MEASURE ON (INVENTORY.WHSE = UNIT_OF_MEASURE.WHSE_NO) AND (INVENTORY.CODE = UNIT_OF_MEASURE.PART_NO)) LEFT JOIN PRICING ON (UNIT_OF_MEASURE.WHSE_NO = PRICING.BVSPECPRICEWHSE) AND (UNIT_OF_MEASURE.PART_NO = PRICING.BVSPECPRICEPARTNO) AND (UNIT_OF_MEASURE.CODE = PRICING.BVSPECPRICEUOM) AND (UNIT_OF_MEASURE.PRICESOURCECONST = PRICING.BVSPECPRICESOURCEID)) LEFT JOIN INVE_WB_MAIN ON (INVENTORY.WHSE = INVE_WB_MAIN.I_WHSE) AND (INVENTORY.CODE = INVE_WB_MAIN.I_PART_NO)
WHERE (((INVENTORY.WHSE)="00") AND ((INVE_WB_MAIN.M_ITEM)=True));

Select allOpen in new window

expert-exchange.accdb

Based on what you asked for above, those numbers look right to me.  Where is the problem?

Did you look at the attached database?

Base on the attached screenshot SKU CD-204 UOM_CODE PKOF5 Expr1should be 2500

if FACTOR_PRICES =1 then PRICE_FACTOR * BVRTLPRICE01 = 2500 (5*500=2500)

I have 0 for results

Or am I missing something?

ok you are right. I just tried it in Crystal and I get the same results.

Is something wrong with my logic?

Please be patient as I'm pretty new to Crystal and even newer to Access. I know enough to get myself in to trouble.

something is wrong with my logic and I'm not sure how to figure this out. I can post a new question if need be but here is what I want to do.

CD-204 is stocked in two units of measure. The IVENTORY.BVSTKUOM is the unit of measure that the part is stocked in.
PRICING.FACTOR_PRICES = 1 would only in any none stocked units of measure. If it is =0 that means it will have it's own price list
PRICING.PRICE_FACTOR = 5 is the factor for the non stocked units of measure PKOF5
PRICING.BVRTLPRICE01 = 500 is my price of the stocked units of measure EA

In the data above I have CD-204 stocking as EA and PKOF5 is non stocked.

What I want is if PRICING.FACTOR_PRICES = 1 the expression is to take PRICING.BVRTLPRICE01 from the EA and multiply it by PRICING.PRICE_FACTOR = 5 to derive the price of 2500

Does this make sense?

 

Are these separate records in the table?

Will there always be only 1 record that has an eaches price?
If not how do you choose which one to use?

mlmcc

They are no separate records in the table. But there will only be one record with the prices bases on IVENTORY.BVSTKUOM being the unit of measure that the part is stocked in.

IVENTORY.BVSTKUOM could be anything and not just EA but whatever that value is that is what the price should be based on.

Sample

CD-204 EA price is $500
CD-204 PKOF5 PRICING.FACTOR_PRICES = 1. If it would = 0 then it would have its own price.
The price for CD-204 PKOF5 would be
PRICING.PRICE_FACTOR of CD-204 PKOF5 = 5 * CD-204 EA price $500 = 2500

fyed,
Your solution would not work as per your last post.

Am I missing something?  You referred to [UOM], I assumed that was a column in one of your tables.  But I think I should be looking at: BVSTKUOM, so try:

[Pricing].[BVRTLPRICE01] * iif(Pricing.Factor_Prices, NZ(DLOOKUP("BVRTLPRICE01", "PRICING", "[SKU] = '" & INVENTORY.CODE & "' AND [BVSTKUOM] = 'EA'"), NULL), 1)
 

Another option would be to create another Query that just gives you all the SKU codes and the prices where the UOM is "EA".  It might look like:

SELECT SKU, BVRTLPRICE01 as PerUnitPrice
FROM Pricing
WHERE [BVSTKUOM] = "EA"

You would then join this to the rest of the query on SKU using an outer join to ensure that you would get records from the rest of the query even if a record did not exist for a specific SKU with a [UOM] = "EA".  Then you wouldn't have to use the DLOOKUP function, you would just use the PerUnitPrice from this query.

Fyed,
Your idea works to create another query but not your sample because as I said before already that  BVSTKUOM could have any value

This is what I have done to create another query to only show me only the stock UOM

 

SELECT INVENTORY.CODE, PRICING.BVRTLPRICE01
FROM INVENTORY INNER JOIN PRICING ON (INVENTORY.BVSTKUOM = PRICING.BVSPECPRICEUOM) AND (INVENTORY.WHSE = PRICING.BVSPECPRICEWHSE) AND (INVENTORY.CODE = PRICING.BVSPECPRICEPARTNO)
WHERE (((PRICING.BVSPECPRICESOURCEID)="I"));

Select allOpen in new window

At this point, I'm lost.  It would be a lot easier if you could post a sanitized version of your database.

Here is the database. Q_PRICING_STKUOM is the query that only shows the the records that show the stock unit of measure

Q_PRICING is the query where I want the expression. You will see that the column Expr1 does not work as I have explained before.

expert-exchange.zip

I cannot download the db while at work, but will take a look this evening if no one else has resolved the issue.

This is working now

 

iif([PRICING].[FACTOR_PRICES]=1,PRICING.PRICE_FACTOR * Q_PRICING_STKUOM.BVRTLPRICE01,PRICING.BVRTLPRICE01)

Select allOpen in new window

If you think this is the best way to make it work I will just stay with this

If your query will return a lot of records, then my guess is that using the query "q_Pricing_STKUOM" will probably result in the best query performance.

I will have up to 10000 or more records. Keep in mind that the tables are linked and not local Access tables

I would use the subquery if that is working the way you expect.

Well it is not working as I expected it would but not because of the expression you gave me. It is because different UOM scenarios and the way it is setup in the ERP.

It is rather complicated and I'm not sure I could explain it in writing. It would probably need to be in person explanation.

Although this did not provide the required result but you did help me with my original question I'm awarding the points. Thank you for your help.