I'm creating a database that will calculate price for a courier company. So, what I have to do is matching 2 zones to get the rate 1-10 where I look up the price in the weight sheet.



What I did in a query is generating the code. Pickup area code, delivery area code and weight code. the weight code I use function IIF which doesn't allow me to do it too long so I made it in many section for example

"CODE1: IIf([W]>400,"O",IIf([W]>300,"F",IIf([W]>200,"E",IIf([W]>100,"D",IIf([W]>50,"C",IIf([W]>25,"B",IIf([W]>0,"A","Z")))))))"

CODE2: IIf([W]>1000,"O",IIf([W]>900,"F",IIf([W]>800,"E",IIf([W]>700,"D",IIf([W]>600,"C",IIf([W]>500,"B",IIf([W]>400,"A","O")))))))

CODE3: IIf([W]>3000,"O",IIf([W]>2500,"F",IIf([W]>2000,"E",IIf([W]>1700,"D",IIf([W]>1500,"C",IIf([W]>1200,"B",IIf([W]>1000,"A","O")))))))

...... to CODE5

then plus them all together with the zone codes

for example minimumweight = AOOOO plus 2 charactors of 2 zone code for example LL and SR

All together LLSRAOOOO

I created a table with the Pricing Codes and the price, the ID of the table is the same of the value of generated code for example ID LLSRAOOO Price 7.80

On the main form that mostly enter everything (Order) I make a field called Price which use Look up wizard look into the Pricing Codes table.

My QUESTION is, can I set up default value to match up the ID in Pricing Codes Table, so I get the price? If so, What is the function code to do so, because as I know it was just a certain number but that doesn't do the work that I want. Because otherwise I would just show the code on the board and have to type in the text box to get value.

Is there any other way around that Access can do to look up for a value in from a table?

Thank you so much for reading and if you have an idea, please share