Help with invalid syntax 1

[brendageorge]

I have this expression:

= IIf(forms![Order Details]![Line]=[Oakhill Sq],then [Unit Price]=DLookup("[Price]","[Oakhill Sq]","[Product ID]='"&[Product ID]&"'"))

It tells me that expression contains invalid syntax. What have I done wrong?

 

[lameid]

IIF is a function that takes three parameters.... I see 2.

forms![Order Details]![Line]=[Oakhill Sq]
then [Unit Price]=DLookup("[Price]","[Oakhill Sq]","[Product ID]='"&[Product ID]&"'")

Secondly, everything should be an expression (logical or otherwise) that returns a single value (Then is used with the IF statement in code not in a control source)....

A valid statement (control source) would be...

=IIF(forms![Order Details]![Line]=[Oakhill Sq],DLookup("[Price]","[Oakhill Sq]","[Product ID]='"&[Product ID]&"'"),"")

Now whether that has anything to do with what you are trying to accomplish, I don't know.

 

[brendageorge]

Thanks lameid. What I am trying to do is this: I have a form "Order Details" inwhich using a combo box I select a line of cabinets that the estimate is for ("Line"). On the subform I select the Product ID and need the "Unit Price" to lookup the "Price" in the table which corresponds to the selection in "Line". I have several different lines and maybe I should not be placing this in the Unit Price's source code. Any suggestions would be appreciated. BTW when I typed in what you suggested the Unit Price field displays #Name?

 

[lameid]

First I have to ask whether you really want to store the price in the table...

It seems like you can pull the price later from the table it exists in. On the otherhand if the pricing can change and you want the promised price vs. current price then you should store it.

Or maybe you just want to display it here?

That being said, the #Name? error suggests there is an error in one of the source names / controls used...

Because of that and not having a clear sense of what data you have, it may be helpful if you post your relevant table names, any key fields used to match them and fields that are immediately relevant tot he problem at hand.

 

[MacroScope]

I saw one of your earlier posts and responded to it. You don't have the correct syntax to look at the subform. Here's a DLookup statement I copied from a db I wrote years ago. You'll need to modify table/query names to suit your application.

DLookUp("[Unit Price]","[Order Details2]","[Customer ID]=Forms![Orders]![Customer ID]" & "And [Part Number]=Forms![Orders]![Orders Subform].Form![Part Number]")

You haven't identified the subform you're trying to draw data from.

 

[brendageorge]

Thanks! Now it works! Thanks so much.