Dlookup struggle

[ncopeland]

Hi I have 2 forms a main form and a subform. I want to check a table for a unit cost based on the item no and customer no retrieved from the 2 forms.

Here is the code that dosent work.

Me![UnitCost] = DLookup("[Price]", "[Item-Price-File]", "[ItemUnique-No] = Forms![Purchase-Order-Input]![PO-Detail].Form![Item-No] And [Customer-No] = Forms![Purchase-Order-Input].Form![Combo90]")

Kind Regards

Neil.

 

[jrbarnett]

Try

Me![UnitCost] = DLookup("[Price]", "[Item-Price-File]", "[ItemUnique-No] = " & Forms![Purchase-Order-Input]![PO-Detail].Form![Item-No] & " And [Customer-No] = " & Forms![Purchase-Order-Input].Form![Combo90])

John

 

[ncopeland]

Hi

I get a data type mismatch in criteria expression.

Thanks.

Neil.

 

[Andrzejek]

How are the fields define in your DB: [tt]ItemUnique-No[/tt] and [tt]Customer-No[/tt] ?
Numbers? Text?
If text, you need single quotes around the values from your Form.

Try something like this:

Dim sCrit As String
sCrit = "[ItemUnique-No] = " & Forms![Purchase-Order-Input]![PO-Detail].Form![Item-No] & " And [Customer-No] = " & Forms![Purchase-Order-Input].Form![Combo90]

Debug.Print sCrit 

Me![UnitCost] = DLookup("[Price]", "[Item-Price-File]", sCrit)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[ncopeland]

Thanks Andy for tweaking the thought in my head the Customer was defined wrong in the table.

Thanks again.