dlookup() syntax

[markjkubicki]

I've got this line of code, which is not good; I may be to close to it, as I an't find the problem. ... another set of eyes would help (please ?)



--------------------------
vcost = nz(DLookup("[amount]", [FixtureCatalogueCostHistory], [Manufacturer] = "'" & Me.Manufacturer & "' " & _
"and " & _
[CatalogNumber] = "'" & Me.CatalogNo & "'"),"")
--------------------------

all fields and table names are correct
[amount] is currency
[manufacturer] and [CatalogNumber] are text

the fields on my form: Me.Manufacturer and Me.CatalogNo are correct

many thanks in advance,
mark

[ajetrumpet]

in a query, the general syntax for domain functions is:

Dfunction("[field]", "table", "criteria")

in code it's a bit different:

Dfunction("field", "table", "criteria")

The criteria section gives problems too. You are specifically missing some quotes and the brackets I think also need to be taken out. E.G. -

vcost = nz(

DLookup("amount", "FixtureCatalogueCostHistory", 

"[Manufacturer] = '" & Me.Manufacturer & "'" & _

" and " & _

"[CatalogNumber] = '" & Me.CatalogNo & "'"), 0)

Also not sure if the lookup function is compatible with NULLS. You might have to use iif(iserror()) as a substitute but I doubt it.

[jzwp11]

oops, I see Adam got in ahead of me...

You might try this:

vcost = nz(DLookup("[amount]", "[FixtureCatalogueCostHistory]", "[Manufacturer] = '" & Me.Manufacturer & "' and [CatalogNumber] = '" & Me.CatalogNo & "'"),"")

[weekend00]

please mark it as solved if this solved you problem.

[markjkubicki]

perfect.. thanks