Im trying to write an if statement and not sure exactly how to write the following:
when the part code is between 01.0000 and 01.9999 or part code is between 04.0000 and 04.9999
If product_warehouse(wcc_nonexpedited_cost) <> 0
then use wcc_nonexpedited_cost
else if product_warehouse(standard_cost) <> 0
then use standard_cost
else use last_cost
when part code is between 02.0000 and 03.9999
If product_warehouse(wcc_pricing_cost) <> 0
then use wcc_pricing_cost
else use standard_cost
Finally if part code is not between 01.0000 and 04.9999 then I want a blank
If (Part_Code between 01.0000 and 01.9999 or Part_Code between 04.0000 and 04.9999)
Then If (product_warehouse(wcc_nonexpedited_cost) <> 0)
Then wcc_nonexpedited_cost
Else If ( product_warehouse(standard_cost) <> 0)
Then standard_cost
Else last_cost
Else If (Part_Code between 02.0000 and 03.9999 )
Then If (product_warehouse(wcc_pricing_cost) <> 0 )
Then wcc_pricing_cost
Else standard_cost
You don’t need to finish the final else, as by virtue of it not being populated, it will be blank.
Can’t guarantee that this will work for you straight away, by the logic is right, you might just need to play with it a little.
Sometime it is easier to test each IF statement by itself first to see if you are getting the values you want and then put them together when you have the results correct. This helps you test the report as well.
Good luck and let me know if you need any more help.