請教各位前輩    工作環境﹕Access資料庫形態﹐Delphi 6 ADO存取﹒ 欄位名﹕IDNO, NAME, AMOUNTTOTAL, MEDICALINSURANCEPAY 現在想用SQL語法在其中多加入兩個欄位﹐其中一個計算欄位是用MEDICALINSURANCEPAY的值除以4.5再乘以5﹐已經完成﹐另一個計算欄位需要判斷MEDICALINSURANCEPAY的值是否大于1,000,000﹐如果大于﹐則只顯示1,000,000﹐如果小于1,000,000﹐則顯示AMOUNTTOTAL的值（以下紅色處﹐目前沒有做判斷）﹐SQL敘述如下﹐請各位前輩指點紅色處該如何修改﹕

begin
  ADOQuery8.Close;
  ADOQuery8.SQL.Clear;
  ADOQuery8.SQL.Add('Select B.IDNO, B.NAME, A.MEDICALINSURANCENO, ');
  ADOQuery8.SQL.Add('B.AMOUNTTOTAL, B.AMOUNTTOTAL As MIPAY, B.MEDICALINSURANCEPAY, ');
  ADOQuery8.SQL.Add('Round(B.MEDICALINSURANCEPAY/SelfPercentTemp*CompanyPercentTemp) As MEDICALINSURANCECOMPANYPAY ');
  ADOQuery8.SQL.Add('From Employee A, DSalarySecond B Where ');
  ADOQuery8.SQL.Add('A.IDNO = B.IDNO and B.MEDICALINSURANCEPAY <> 0 ');
  ADOQuery8.SQL.Add('and B.MONTHNAME = '''+MonthRemark+'''');
  if suiComboBox3.Text <> '' then
    ADOQuery8.SQL.Add(' and A.SECTIONNAME = '+QuotedStr(suiComboBox3.Text));
  ADOQuery8.SQL.Text := StringReplace(ADOQuery8.SQL.Text, 'SelfPercentTemp',
    FloatToStr(ADOTable2.FieldByName('SELFPAYPERCENT').AsFloat), []);
  ADOQuery8.SQL.Text := StringReplace(ADOQuery8.SQL.Text, 'CompanyPercentTemp',
    FloatToStr(ADOTable2.FieldByName('COMPANYPAYPERCENT').AsFloat), []);
  ADOQuery8.Open;
end;

謝謝﹗ ================================= 有空來瞅瞅我﹗因為我是您的朋友﹐有您真好﹗ ================================

------
忻晟

cashxin2002大大您好：    

begin
  ADOQuery8.Close;
  ADOQuery8.SQL.Clear;
  ADOQuery8.SQL.Add('Select B.IDNO, B.NAME, A.MEDICALINSURANCENO, ');
  ADOQuery8.SQL.Add('B.AMOUNTTOTAL, iif(B.MEDICALINSURANCEPAY >1000000,1000000,B.AMOUNTTOTAL)) As MIPAY,  B.MEDICALINSURANCEPAY, '); //这是不是您要的？
  
  ADOQuery8.SQL.Add('Round(B.MEDICALINSURANCEPAY/SelfPercentTemp*CompanyPercentTemp) As MEDICALINSURANCECOMPANYPAY ');
  ADOQuery8.SQL.Add('From Employee A, DSalarySecond B Where ');
  ADOQuery8.SQL.Add('A.IDNO = B.IDNO and B.MEDICALINSURANCEPAY <> 0 ');
  ADOQuery8.SQL.Add('and B.MONTHNAME = ''' MonthRemark '''');
  if suiComboBox3.Text <> '' then
    ADOQuery8.SQL.Add(' and A.SECTIONNAME = ' QuotedStr(suiComboBox3.Text));
  ADOQuery8.SQL.Text := StringReplace(ADOQuery8.SQL.Text, 'SelfPercentTemp',
    FloatToStr(ADOTable2.FieldByName('SELFPAYPERCENT').AsFloat), []);
  ADOQuery8.SQL.Text := StringReplace(ADOQuery8.SQL.Text, 'CompanyPercentTemp',
    FloatToStr(ADOTable2.FieldByName('COMPANYPAYPERCENT').AsFloat), []);
  ADOQuery8.Open;
end;

============================ 为什么经过多年以后，得失的过程如此冷漠 ============================

感謝deity相助﹐已解決問題﹗    ================================= 有空來瞅瞅我﹗因為我是您的朋友﹐有您真好﹗ ================================

------
忻晟