How can I allow zero and Null Values In Key Field

[victory92]

I would like to create a file that may have zeros or nulls in the key -- and I keep getting an error message "Index or Primary Key cannot contain a Null value"

Here's what I had in mind... When there is a detailed specific rate on a customer and/or product level, then enter the customer and/or product values. Otherwise - leave it blank and it assumes "ALL others". For example

key-text Key-number Key-number non-key
Broker Customer Product Rate

ABC 123 981 3%
ABC 123 4%
ABC 981 2%
ABC 5%

If a rate is needed for Broker ABC for Customer 123 and product 981 - 3% is the rate to use. If it's product 765 - then the 4% would be used as there was a match on customer number - but not product.

If Broker ABC had an order for product 981 for a different customer -- say 444 - then rate 2% would be used.

Otherwise use 5 %.

I've used this logic on the AS400 and it worked well. By keeping it on one file it is easier for the user to see all the rates at once. And it's easier than creating Broker/Cust, Broker/Cust/Product and Broker/product tables.
And it runs faster - with less queries.

I could always use a special character if the fields were alpha/numeric -- but customer and product are numeric fields. I'd like to have them left at zero.

Any ideas????????????

I appreciate the help. Please realize that I am a rookie on Access!!!

Thanks

 

[KornGeek]

I recommend simply using a different field as the key. According to basic database design principles, key fields cannot be null. To get around this, simply create a non-null key. You can get away with a simple RateID field, which you can even hide from the end user if you want. It only serves as a unique record identifier.