Multi-criteria relationship

[systwo]

For some reason I am struggling with a multi-criteria relationship and it is driving nuts! I am sure someone can put me out of my misery.

I have an order database, and an items database with a sub-table consisting of multiple items each with a quantity and price for discounts.

I have created a multi-criteria relationship between the order item and the discount table consisting of the Item Code (=) and the Qty (>=) in order to lookup the price based on the quantity.

As hard as I try I cannot get a match beyond two values. In other words in my discounts table I have 4 value quantites, 1, 12, 28, and 35. The relationship will bring back the first two but if the quantity is >= 28 it will not return it.

I think I am having a mad moment so can someone point out where my madness lies?

[Vaughan]

Not sure... but check that the quantity fields are number fields, and that they don't contain white space (spaces, returns, tabs etc).

[systwo]

Thanks for the reply. I have now solved the problem myself. Just for the record just in case it foxes anyone else it was related to the way the data in the sub-table was sorted in the relationship.

Thanks anyway