select lead week based on lowest price

[kabir_hussein]

hi i have a form containing data from different suppliers. Each supplier has a supllier ID, Name, lead week, price.

Based on the lowest price is there away i can send data to another field showing the lead week. The lead week is that of the best price
I have tried endless amount of ways to do this but have not succedded

Can anyone help.

Please check the picture as this would make things clearer

 

[RichO]

Do you want to find the lowest price overall or the lowest price within a specific lead week. And when you say "send data to another field" are you talking about appending a record to a table or just displaying a field or fields on the form?

Look in Access help for information on DMin. You can use this function to locate your best (lowest) price.

 

[kabir_hussein]

Hi

thanks for the reply mate, what i am trying to do is at present i have a table with data on suppliers who can supplier a specific product. Each supplier has a price and lead week.

I have a added a field just on the form and set the control to =min([price]), this works fine but i now need another field which works with the minimum price to show the lead week of the best price.

If you know what i mean

kabir

thank you

 

[RichO]

I want to make sure I understand what you are saying...

You have a field which shows the minimum price within a table "=min([Price])" So what you want is the lead date that corresponds with that mininum price in that table?

Set up another unbound field in your form. Set the control source to something like:

=DLookup("LeadWeek", "MyTable", "[Price] = " & Min([Price]))

This should find the Lead Week that matches the best price found by Min([Price]). If there is more than one record that matches the best price, this will only find one.

 

[kabir_hussein]

Hi

thank you for the reply. If you have a look at the picture it will explain what i mean. I have a table which has a unbound field on it which calculates the minimum price

is there anyway based on the minimum price i can also show the lead week of the minimum price row.

Sorry if it is a bit confusing

thank you

 

[RichO]

This is what I gather from your explanation: You want the [LeadWeek] field returned from the SAME row in the table that the Min([Price]) is taken from. Is that correct?

If that is the case then my previous reply would give you what you are looking for. If not please explain further, ie: table structure, a detailed example, etc.

 

[kabir_hussein]

Hi RichO

many thanks for the reply

I have tried to use the code you gave me and all i got was an error# sign

I have updated picture of the table and the form.

Basically how it works is i have a subform on a part forms. the subform is basically a list of the prices different suppliers have recommeneded. Using a seperate field i have set the control to min([price]) and this shows the minimum price. However i need another field which can show the lead week of the minimum price field. At present i am stuck on this.

All help will be greatful

thank you

 

[RichO]

Are you able to post a scaled down sample of your DB?

It looks like you have done it right and a sample table & form I put together the same way works for me.

 

[kabir_hussein]

Hi thanks for the reply

i have added a copy of my database. the form you should see is called frmaddpart.

many thanks

i have taken the code you gave me on that one.

many many thanks for all your help

 

[RichO]

I couldn't do much with your sample DB because there was network linking involved but I believe I found your problem.

In the DLookup expression, try putting the field name "Lead Week" in brackets [Lead Week]. The space in the field name is what I believe was giving you the error.