DLookup in Make Table Query

[RickBooker]

I have a Make table query that contains an expression (expr1)This gives me value called Performance% that I then want to Lookup on another table that shows a corrosponding BONUS amount for a 100 various Performance% numbers.<br><br>My expr1 gives me a value of 1.26<br>In the BonusPercentTable the bonus for 1.26 is 1.310<br>I want this 1.310 to be in a field in the table.<br>My Expr1 is:<br>Expr1: IIf([LaborwithRates]![WorkCenter]=&quot;13&quot; Or [LaborwithRates]![WorkCenter]=&quot;14&quot; Or [LaborwithRates]![WorkCenter]=&quot;15&quot; Or [LaborwithRates]![WorkCenter]=&quot;16&quot; Or [LaborwithRates]![WorkCenter]=&quot;25&quot;,Mid(((([LaborwithRates]![NetQuantity]/1000)*([LaborwithRates]![StandardHours]))/[LaborwithRates]![RunTimeHours]),1,4),&quot;&quot<br><br>My DLookup, which runs part way then just stops, no errors no nothing just stops:<br>Expr2: DLookUp(&quot;[Bonus]&quot;,&quot;BonusPercentTable&quot;,&quot;[Performance%]=Mid(((([LaborwithRates]![NetQuantity]/1000)*([LaborwithRates]![StandardHours]))/[LaborwithRates]![RunTimeHours]),1,4)&quot<br><br>If I remove the criteria section of the DLookup the query runs fine except that it only shows the first value in the BonusPercentTable, not the one that corrosponds to the 1.26 value.<br><br>Am I going at this all wrong, or am I not doing some correctly here?<br>Any suggestions would be greatly appreciated<br>RickB........... My apologies for the long question but I wasn't sure how else to ask it.<br>

 

[famousb]

so is the field name created by Expr1 in the Make table query &quot;Expr1&quot; or &quot;Performance%&quot;?<br>secondly, you have your DLookup referring to the value in the BonusPercentTable that equals a mathematical equation, but are the fields in the equation located in that table?&nbsp;&nbsp;if not, you need to reference the entire table that they are in so Access can know which record to use to calculate the field.<br><br>i don't know if i am saying this in a way that you'll understand. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[RickBooker]

The BonusPercentTable contains two Fields.<br>One is Performance% and the other is Bonus<br>The Expr1 is in the make table query and calculates a value that I want looked up in the BonusPercentTable and return the corresponding Bonus value.<br>Example: Suppose Expr1 calculates a value of 1.26. There is a record in the BonusPercentTable in the Performance% field that equals 1.26 with a corrosponding value in the Bonus field of the same record. I want to return this Bonus value.<br><br>I guess what I'm trying to ask is can I use an expression as the criteria in the DLookUp? And if so what is the syntax? I can manualy enter the value after the = and it works fine. <br>As in DLookUp(&quot;[Bonus]&quot;,&quot;BonusPercentTable&quot;,&quot;[Performance%]= 1.26&quot This returns the Bonus value I'm after. It just won't work by using an expression rather than manualy entering 1.26<br><br>As I understand it the DLookUp works this way: <br>DLookUp(&quot;[Bonus]&quot;,&quot;BonusPercentTable&quot;,&quot;[Performance%]= A value&quot<br>Meaning to me that a value that is in the Performance% field is located and returns the value in the Bonus field in the same record. Am I understanding this correctly?<br><br>I sure do appreciate any suggestions.<br>Thanks a lot.<br>RickB

 

[harryrich]

When you say a query just stops, I take it you mean that the thermometer on the status line runs part way accross and just hangs there.&nbsp;&nbsp;Your query may be working, just taking an exceedingly long time.<br><br>I have lengthy queries where the thermometer sits in one place for fifteen minutes and have taken to watching the performance monitor and the disk light to make sure processing continues.<br><br>Your expression is very complicated and is of a type where idexes don't help with a lot of it.&nbsp;&nbsp;You may need to simplifiy it or give you system a performance boost.