I want to compare two prices to find te variance between the two. Each price is distinguised by a sales code; one for 2008 and one for 2009.
Originally I had specified these code in criteria but this eliminated all new poducts that didn't have a 2008 price from the comparison making it unusable overall as we want to include our 2009 products.
I then tried to make it so that if the price doesn't have the 2008 sales code it uses the 2009 sales code through this expression in the criteria field - IIf([salescode]=2008,2008,2009) (simplified)
now this works for new products which never had a 2008 sales code but if the product had a 2008 price code and has a 2009 price code it duplicates the record...obviously.
Now my question is, how can I stop it from duplicating? Is there a more robust expression I can use?
I also tried to put 2008 in the criteria and then put 2009 in the or field but when I try to run the query it comes up with a 0 division error
Thanks for your help
Originally I had specified these code in criteria but this eliminated all new poducts that didn't have a 2008 price from the comparison making it unusable overall as we want to include our 2009 products.
I then tried to make it so that if the price doesn't have the 2008 sales code it uses the 2009 sales code through this expression in the criteria field - IIf([salescode]=2008,2008,2009) (simplified)
now this works for new products which never had a 2008 sales code but if the product had a 2008 price code and has a 2009 price code it duplicates the record...obviously.
Now my question is, how can I stop it from duplicating? Is there a more robust expression I can use?
I also tried to put 2008 in the criteria and then put 2009 in the or field but when I try to run the query it comes up with a 0 division error
Thanks for your help