Mark up rates

[whitebear]

Hi
I have a form where I enter the net price, but I want to calculate the Stock price automatically by running a query.

The query needs to look up the mark up rate in the mark up rate table, but the rates are different depending on the net price of the item.

How do i create a query that looks at the net price, looks at the mark up rates, chooses the correct mark up rate from the table and then calculates the Stock price.

I hope this makes sense!
WB

 

[khawar]

Can you provide detail of your tables

 

[whitebear]

Hey

I've attached a print screen of the two tables

 

[khawar]

Not visible

 

[whitebear]

there you go!

 

[Brianwarnock]

I think that you could use Dlookup as it finds the first occurence of a match so something like
Dlookup("[Markup]","markuprates","[Netprice] >=" checkedprice)

anyway lookup Dlookup in VBA help.

Brian

 

[khawar]

Here is a quick sample for you

 

[whitebear]

Thanks.

From a help page and your assistance i think it should be something like the following:

DlookUp("[Mark-Up Percentage]", "tblMark-Up Rates", "[Net Price]>=forms!frmStock Items![Net Price]")

Does that seem about right??

 

[Brianwarnock]

Not quite, you don't need the form bit and that would be outside of the quotes, but hey Dllokup causes many people syntax problems, me especially , however I have a simple working DB for you, it uses a function gp in module1 to return the gross price.

Brian

I think I may have done the calculation the wrong way but hey you can do the Maths.