problem dlookup

[ladiross]

Hello,


I have used a DLookup in a calculated field in a query. but i Have an wrong result.
the query is:

GRAMMI_CAL: DLookUp("[GRAMMATURA]";"GRAMMATURA";"[COD_PRODOTTO]= '" & [FORMS]![SCHEDA GIORNALIERA]![PRODOTTO] & "'" And "[COMMESSA]= '" & [FORMS]![SCHEDA GIORNALIERA]![COMMESSA] & "'")

why?

[John_G]

You haven't given us anywhere near enough information to answer your question. The syntax of the DLookup seems to be OK, but beyond that, we can only guess.

What does "wrong result" mean?

It could be:

- Dlookup returns the wrong field
- where clause in the dlookup is wrong
- dlookup references the wrong form fields
- form data does not match any table data
- something else.....

Please give more information

[ladiross]

dlookup returns a wrong value. for example:

Mozzarella, TORINO, 5
LATTE, BARI, 10

IF DLOOKUP HAVE (MOZZARELLA, TORINO) AS PARAMETRES, IT RETURNS 10 (NOT 5!).

[June7]

Your DLookup has bad quote marks as shown in red. Remove them.

GRAMMI_CAL: DLookUp("[GRAMMATURA]";"GRAMMATURA";"[COD_PRODOTTO]= '" & [FORMS]![SCHEDA GIORNALIERA]![PRODOTTO] & "'" And "[COMMESSA]= '" & [FORMS]![SCHEDA GIORNALIERA]![COMMESSA] & "'")

Try to avoid field and table names that are same. Common convention is to use plurals for table names.

Title case is easier to read than all upper case. Also good to avoid spaces in object names.

[ladiross]

Your DLookup has bad quote marks as shown in red. Remove them.

GRAMMI_CAL: DLookUp("[GRAMMATURA]";"GRAMMATURA";"[COD_PRODOTTO]= '" & [FORMS]![SCHEDA GIORNALIERA]![PRODOTTO] & "'" And "[COMMESSA]= '" & [FORMS]![SCHEDA GIORNALIERA]![COMMESSA] & "'")

Try to avoid field and table names that are same. Common convention is to use plurals for table names.

Title case is easier to read than all upper case. Also good to avoid spaces in object names.

If [COMMESSA] is a calculated field, [COMMESSA_CAL] and not in a Maschera, what is the sintax?

GRAMMI_CAL: DLookUp("[GRAMMATURA]";"GRAMMATURA";"[COD_PRODOTTO]= '" & [FORMS]![SCHEDA GIORNALIERA]![PRODOTTO] & "' And [COMMESSA]=[COMMESSA_CAL] & "'")

which error in sintax?

[June7]

Don't understand. What is a Maschera?

Why would the criteria test one field against another field?

Are Comessa and Commessa_Cal number types?

Maybe:

GRAMMI_CAL: DLookUp("[GRAMMATURA]";"GRAMMATURA";"[COD_PRODOTTO]= '" & [FORMS]![SCHEDA GIORNALIERA]![PRODOTTO] & "' And [COMMESSA]=" & [COMMESSA_CAL])

or

GRAMMI_CAL: DLookUp("[GRAMMATURA]";"GRAMMATURA";"[COD_PRODOTTO]= '" & [FORMS]![SCHEDA GIORNALIERA]![PRODOTTO] & "' And [COMMESSA]=[COMMESSA_CAL]")