 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / April 2008DLookup- multiple criteria
Hi... I am trying to create a form to calculate the price of our product based on two different criteria... The prices are fixed I just need the price control to return the value from the prices table based upon two criteria... Here is what I have, but I can't get this to work, I think my "grammer" is wrong. =Dlookup("[price]","[tblPrices]","[Diameter] = "& ("[Diam]") and "[material] = "& ([matno]")) This works fine when I only have [Diam], but when I add the second criteria it goes kaput!.. Any help would be great...  Signature-Jacinda Try this Im assuming Diam and Matno are the forms control values. When doing Dlookups you have to use the entire form syntax =Dlookup("[price]","[tblPrices]","[Diameter]=[Forms]![YourForm]![Diam] and [material]=[matno]") > Hi... I am trying to create a form to calculate the price of our product > based on two different criteria... [quoted text clipped - 12 lines] > > Any help would be great... I forgot to add that last Form Syntax should be like this =Dlookup("[price]","[tblPrices]","[Diameter]=[Forms]![YourForm]![Diam] and [material]=[Forms]![YourForm]![matno]") > Try this > [quoted text clipped - 20 lines] > > > > Any help would be great... Jacinda, Try it like this: =DLookup("[price]","[tblPrices]","[Diameter]=" & [Diam] & " And [material]=" & [matno]) This assumes Diameter and Material are both number data type. SignatureSteve Schapel, Microsoft Access MVP > Hi... I am trying to create a form to calculate the price of our product > based on two different criteria... [quoted text clipped - 12 lines] > > Any help would be great... Thank you both... simple syntax goes a long way... thank you. Signature-Jacinda > Jacinda, > [quoted text clipped - 21 lines] > > > > Any help would be great... One more question: my diameters are not fixed. Is there anyway for it to go to the nearest "price" based on the criteria... for example: my price table has a fixed price of $6.85 for diameter = .017 and Material = 11 if my user enters a diameter of .0175, I still want the DLookup to find the price to be $6.85... any thoughts? -Jacinda > Hi... I am trying to create a form to calculate the price of our product > based on two different criteria... [quoted text clipped - 12 lines] > > Any help would be great... That's a tricky situation, cause .0175 would be rounded to .018 in access. If there isn't too many choices in the price table I would think about creating comboboxes with the valid numbers they can choose. If not you can setup up input mask that won't allowed users to put any more then 3 digits after a decimal. The mask would look something like 0.000 > One more question: my diameters are not fixed. Is there anyway for it to go > to the nearest "price" based on the criteria... for example: [quoted text clipped - 24 lines] > > > > Any help would be great... Jacinda, There would be various ways to achieve what you are asking. It depends a bit what you mean exactly by "nearest". In the example you gave, as a starting point you could see how this goes: =DMax("[price]","[tblPrices]","[Diameter]<=" & [Diam] & " And [material]=" & [matno]) SignatureSteve Schapel, Microsoft Access MVP > One more question: my diameters are not fixed. Is there anyway for it to go > to the nearest "price" based on the criteria... for example: [quoted text clipped - 3 lines] > if my user enters a diameter of .0175, I still want the DLookup to find the > price to be $6.85... |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.