I have the following expression in a text box
=sum(Clng([Quantity]*[unitprice1]*(1-[discount])*100)/100)

My table has the following fields

Stock code, Unitprice1,Unitprice2,Unitprice3 etc

I would like to replace the [unitprice1] part of my expression to pick up the relevent unit price based on the colour selected. I have a table that has all the colours in along with the relevent unit price

Red Unitprice2
Blue Unitprice3
Green Unitprice2
etc

Can anyone help

Create an unbound field on your form that does the loookup to determine the correct price and refer to this in your expression.

Incidently is the fourmula correct as the brackets are not nested correctly

=sum(Clng([Quantity]*[unitprice1]*(1-[discount])*100)/100)

=sum(Clng(([Quantity]*[unitprice1])*(((1-[discount]))*100))/100)

If it is right then ok it just seemed unlikely to me that it would be ok.

David

Many thanks but how should the expression look?
I have created an unbound text box called "price" which returns the name of the field (ie unitprice3). I need the expression to calculate based on the contents of the field "unitprice3" but dont know the correct syntax.

On the after/before update of the control that selects/changes the colour selection or on the On Current event of the form you would need to call a sub routine

Prive Sub GetUnitPrice()

Select Case Me.Colour
Case "Red"
Me.UnitPrice = DLookup("[RedPriceField]","Table")
Case "Blue"
Me.UnitPrice = DLookup("[BluePriceField]","Table")
Case etc
Me.UnitPrice = DLookup("[EtcPriceField]","Table")
End Select

End Sub

Then use the field UnitPrice in you expression.
Remember these names I have supplied are for brevity only, use the correct names from your mdb.

David

Many thanks, I will try it