Calculations using DLookup

[Christine Jackson]

Hi I have a number of tables (all linked). In my form i have created some DLookup's through a text box to pull through info from another of the tables so that the information is correct and no manual input is required.

However I would like to do a calculation that involves 2 of these dlookup text box's. Is this possible and what would the correct expression be.

My lookups on the text boxes are =DLookUp("[P11D Value]","[Vehicles]","[Reg No]= '" & [Reg No] & "'") and =DLookUp("[% On Emissions]","[Vehicles]","[Reg No]= '" & [Reg No] & "'").

The first I have called List Price and the 2nd % on Emissions, and to calculate I have tried the following expression
=Sum([List Price]*[% on Emissions])
but it dosn't work.

Anyone any idea where I'm going wrong.

Thanks
Chris

 

[sxschech]

=Sum(
[List Price]*[% on Emissions])

The text boxes on your form containing the dlookup shouldn't be given the same name as what is in the dlookup. I noticed that one of your formula already contains this.

=DLookUp("[% On Emissions]","[Vehicles]","[Reg No]= '" & [Reg No] & "'")

What if you changed the name of this text box in the properties sheet from % to Pct. as the name of your text box and then change this in the control source of the text box containing your sum formula. You may also want to consider to give your field names words instead of the symbols.

=Sum(
[List Price]*[Pct on Emissions])

 

[Christine Jackson]

I'll give that a try and see how I get on - can I reserve the right to call on you again LOL

 

[Christine Jackson]

That worked great, many thanks

 

[sxschech]

glad it worked for you.