Acess 2003

  • Thread starter Thread starter jb
  • Start date Start date
J

jb

Could you please see what is wrong with this formula, because it doesnt give
the right result. I think the criteria isnt well written.

DLookup("[Descrição_Artigo]";"Tipo_artigos_stock";"[Tipo_Familia_Stock]=[Familia]"
and "[Tipo_SubFamilia_Stock]=[SubFamilia]" and "[Refª_Artigo]=[refª_produto]")

thank you
 
I can tell you that your syntax is wrong in the DLookup expression. The
generic syntax for multiple criteria is this:

DLookup("FieldToLookUp", "TableOrQueryName", "Field1 = AValue And Field2 =
AnotherValue")

or (if you're using controls on a form to give the values, and those values
are numeric:

DLookup(("FieldToLookUp", "TableOrQueryName", "Field1 = " &
Me.ControlNameOnForm.Value & " And Field2 = " &
Me.AnotherControlNameOfnForm.Value)

or (if you're using controls on a form to give the values, and those values
are text:

DLookup(("FieldToLookUp", "TableOrQueryName", "Field1 = '" &
Me.ControlNameOnForm.Value & "' And Field2 = '" &
Me.AnotherControlNameOfnForm.Value & "'")
 
On Sat, 8 Nov 2008 05:52:00 -0800, jb <[email protected]>
wrote:

You need commas between the arguments, not semicolons.
The third argument needs to be a single string.
Otherwise you are not providing enough information for me to
understand how to write the third argument.

-Tom.
Microsoft Access MVP
 
Tom van Stiphout said:
You need commas between the arguments, not semicolons.
Click to expand...

Many/most non-English European versions use ; instead of , for argument
delimter.
 
DLookup("[Tipo_medida]";"Tipo_artigos_stock";"[Tipo_Familia_Stock]= " &
Me.[Familia].Value & " And [Tipo_SubFamilia_Stock]= " & Me.[SubFamilia].Value
& " And [Refª_artigo]= " & Me.[refª_produto].Value)

Now it tells me that a operator is missing. The values are in controls on a
form witch are numeric.

Thank you

Ken Snell (MVP) said:
I can tell you that your syntax is wrong in the DLookup expression. The
generic syntax for multiple criteria is this:

DLookup("FieldToLookUp", "TableOrQueryName", "Field1 = AValue And Field2 =
AnotherValue")

or (if you're using controls on a form to give the values, and those values
are numeric:

DLookup(("FieldToLookUp", "TableOrQueryName", "Field1 = " &
Me.ControlNameOnForm.Value & " And Field2 = " &
Me.AnotherControlNameOfnForm.Value)

or (if you're using controls on a form to give the values, and those values
are text:

DLookup(("FieldToLookUp", "TableOrQueryName", "Field1 = '" &
Me.ControlNameOnForm.Value & "' And Field2 = '" &
Me.AnotherControlNameOfnForm.Value & "'")

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


jb said:
Could you please see what is wrong with this formula, because it doesnt
give
the right result. I think the criteria isnt well written.

DLookup("[Descrição_Artigo]";"Tipo_artigos_stock";"[Tipo_Familia_Stock]=[Familia]"
and "[Tipo_SubFamilia_Stock]=[SubFamilia]" and
"[Refª_Artigo]=[refª_produto]")

thank you
Click to expand...
Click to expand...
 
Are any of the controls' holding alphanumeric text (string text) and not
numbers? If yes, you must delimit that value with ' characters. For example,
if the Tipo_Familia_Stock control holds a string value:

DLookup("[Tipo_medida]";"Tipo_artigos_stock";"[Tipo_Familia_Stock]= '" &
Me.[Familia].Value & "' And [Tipo_SubFamilia_Stock]= " &
Me.[SubFamilia].Value
& " And [Refª_artigo]= " & Me.[refª_produto].Value)


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


jb said:
DLookup("[Tipo_medida]";"Tipo_artigos_stock";"[Tipo_Familia_Stock]= " &
Me.[Familia].Value & " And [Tipo_SubFamilia_Stock]= " &
Me.[SubFamilia].Value
& " And [Refª_artigo]= " & Me.[refª_produto].Value)

Now it tells me that a operator is missing. The values are in controls on
a
form witch are numeric.

Thank you

Ken Snell (MVP) said:
I can tell you that your syntax is wrong in the DLookup expression. The
generic syntax for multiple criteria is this:

DLookup("FieldToLookUp", "TableOrQueryName", "Field1 = AValue And Field2
=
AnotherValue")

or (if you're using controls on a form to give the values, and those
values
are numeric:

DLookup(("FieldToLookUp", "TableOrQueryName", "Field1 = " &
Me.ControlNameOnForm.Value & " And Field2 = " &
Me.AnotherControlNameOfnForm.Value)

or (if you're using controls on a form to give the values, and those
values
are text:

DLookup(("FieldToLookUp", "TableOrQueryName", "Field1 = '" &
Me.ControlNameOnForm.Value & "' And Field2 = '" &
Me.AnotherControlNameOfnForm.Value & "'")

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


jb said:
Could you please see what is wrong with this formula, because it doesnt
give
the right result. I think the criteria isnt well written.

DLookup("[Descrição_Artigo]";"Tipo_artigos_stock";"[Tipo_Familia_Stock]=[Familia]"
and "[Tipo_SubFamilia_Stock]=[SubFamilia]" and
"[Refª_Artigo]=[refª_produto]")

thank you
Click to expand...
Click to expand...
Click to expand...
 
DLookup("[Tipo_medida]";"Tipo_artigos_stock";"[Tipo_Familia_Stock]= " &
Me.[Familia].Value & " And [Tipo_SubFamilia_Stock]= " & Me.[SubFamilia].Value
& " And [Refª_artigo]= " & Me.[refª_produto].Value)

Now it tells me that a operator is missing. The values are in controls on a
form witch are numeric.
Click to expand...

Might any of them be NULL, empty? If so you will get a criteria string like

[Tipo_Familia_Stock]= 312 And [Tipo_SubFamilia_Stock]= And [Refª_artigo]=22

which will give this error.

Note also that it is not necessary to use the .value property, since that is
the default. It doesn't hurt but it clutters your code; try just using
Me![SubFamilia] (and so on) instead.
 
Back
Top