lookup value

[peljo via AccessMonster.com]

In my form i want to lookup a field in the query and multiply this to the
value of a control.I get however errors.is it possible to do what i am doing :


Dim NewUnitprice As Double
set NewUnitPrice = " CCur(Nz(DLookUp("NewUnitPrice";"qryAmount";"OrderID=" &
[Orders].[OrderID]);0))"

Me!extendedprice = NewUnitprice

Where is the error in my code ?
Thank you in advance

 

[tina]

[Orders].[OrderID]

if OrderID is a field in the RecordSource of the form from which the code is
running, then just refer to it as OrderID, as

CCur(Nz(DLookUp("NewUnitPrice";"qryAmount";"OrderID=" _
& OrderID);0))

if OrderID is in a different form, that form must be open when the code runs
(whether or not it is Visible), and you must use the full form reference, as

CCur(Nz(DLookUp("NewUnitPrice";"qryAmount";"OrderID=" _
& Forms!FormName!OrderID);0))

replace FormName with the correct name of the form that the field is in, of
course. the current syntax (in both examples above) is referring to OrderID
as a Number data type. if it is a Text data type instead, then surround the
value with single quotes, as

CCur(Nz(DLookUp("NewUnitPrice";"qryAmount";"OrderID='" _
& Forms!FormName!OrderID & "'");0))

also, you need to get rid of the double quotes surrounding the expression,
as

set NewUnitPrice = CCur(Nz(DLookUp("NewUnitPrice"; _
"qryAmount";"OrderID='" & Forms!FormName!OrderID & "'");0))

and finally, i'm assuming that the use of semi-colons is appropriate for
your copy of Access. in the US (English?) version of Access, those
semi-colons would be replaced with commas.

hth