Numerical Wildcard in an Access table

[gaker10]

I have a table which tracks material quotes. I have two fields for the quantity range called PriceBreakBegin and PriceBreakEnd. If I have a part that has contract pricing (no limit on the PriceBreakEnd), can I use a wildcard or something in the PriceBreakEnd field to represent any number, and not just the highest integer I can put in there like 99999?

[John_G]

If the field is a numeric type, then no. Numeric types can only accept the digits 0-9, +/-, or the decimal for data entry.

Could you enter a special value in the field, say 0 or -1?

[gaker10]

I suppose I could, but I have queries which match P/Ns and take the P/N qty and finds a record in my database in which that qty falls between PriceBreakBegin and PriceBreakEnd. How do I use -1 to really mean infinity so that my queries still work? For example, I want to find Part1's price with a quantity of 35. There's a record in my database for Part1 with a PriceBreakBegin of 1 and a PriceBreakEnd of -1 (infinity in my case). How would I adjust my query to return this result since it applies to my input?

[gaker10]

An "Or" criteria?

[John_G]

Something like:

(PriceBreakEnd < 0 AND (Quantity >= PriceBreakBegin)) OR (Quantity between PriceBreakBegin and PriceBreakEnd)

might work as criteria.

[gaker10]

Sounds good to me. I just re-designed my source tables so I will try this when I get to the queries. Thank you!