help with this SQL script please

[fratezone]

just can't get this query to perform right.

I'm trying to modify my script and keep running into a circular reference error. Any suggestions would be appreciated.

SELECT DISTINCTROW tblRatesHeader.Quote,tblRatesHeader.ServType, 
tblRatesHeader.ServLevel, tblRateDetail.Currency, tblRateDetail.Rate, 
tblRateDetail.HidWgt, tblRateDetail.Weight, tblRateDetail.WgtBrk, 
tblRateDetail.Qty, IIf([QTY]="MC" Or [RateType]="FLAT",[RATE],(IIf([txtWeight]<=
[Weight],[Weight]/100*[Rate],[txtWeight]/100*[Rate]))) AS Net
FROM tblRatesHeader INNER JOIN tblRateDetail ON tblRatesHeader.Quote = tblRateDetail.Quote;

What I want is to check to see if the value of NET is lower than that of RATE when QTY = MAX. And if so, then NET = RATE

Tried the following but no success:

SELECT DISTINCTROW tblRatesHeader.Quote,tblRatesHeader.ServType, 
tblRatesHeader.ServLevel, tblRateDetail.Currency, tblRateDetail.Rate, 
tblRateDetail.HidWgt, tblRateDetail.Weight, tblRateDetail.WgtBrk, 
tblRateDetail.Qty, IIf([QTY]="MC" Or [RateType]="FLAT",[RATE],(IIf([txtWeight]<=
[Weight],[Weight]/100*[Rate],[txtWeight]/100*[Rate]))) AS Net2,
IIf([QTY]="MAX" AND [Rate] <=[Net2],[RATE],[Net2]) AS NET
FROM tblRatesHeader INNER JOIN tblRateDetail ON tblRatesHeader.Quote = tblRateDetail.Quote;

............ but it doesn't work

help!

 

[Jon K]

I don't see any reasons for the error of 'Circular Reference' in your queries.

When the queries were run, an input box should have popped up asking the user to Enter Parameter Value for txtWeight.

If [txtWeight] was a text box on a form, you should have referred to it as:
Forms![nameOfForm]![txtWeight]