DLookup- multiple criteria

[Jacinda]

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...

 

[akphidelt]

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]")

 

[akphidelt]

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

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...

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...

 

[Steve Schapel]

Jacinda,

Try it like this:

=DLookup("[price]","[tblPrices]","[Diameter]=" & [Diam] & " And
[material]=" & [matno])

This assumes Diameter and Material are both number data type.

 

[Jacinda]

Thank you both... simple syntax goes a long way... thank you.
--
-Jacinda

Jacinda,

Try it like this:

=DLookup("[price]","[tblPrices]","[Diameter]=" & [Diam] & " And
[material]=" & [matno])

This assumes Diameter and Material are both number data type.

--
Steve Schapel, Microsoft Access MVP

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...

 

[Jacinda]

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

 

[akphidelt]

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:

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...

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...

 

[Steve Schapel]

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])