Calculated Fields in a Query

did you use Count function  !
ex:

Count (FieldName)
it will returns you the right result

Thanks for the comment TheLeader.  

That is not the problem.  I need to programatically add extra fields to the query at runtime.  I have several calculated fields per query which all do different calculations.

John

i duno what is about?
could you post some code of yours here?

Hi lamtl354

I have no problem adding calculated fields to a TQuery at design time.  I just double-click the TQuery component to bring up the fields editor and then click Add New Field.

I want to do the same thing in code at run time because when I clear out the SQL the extra fields I have added at design time disappear.

well !!!
do you want the Extra Feild to be Count then !?
eventhough it will be a count(feild)query

could you be more specific ?

OK here is one of my SQLs:

SELECT T.ApplNum, T.FIRST, T.LAST, T.Exempt1, C.Contract, C.UniqueVal, C.WEnded, C.TimeSheet, C.Code, C.Qty, C.UnitPrice, C.TUnit, C.Com, C.InvoiceNo, E.OurRef, E.SplitWith, E.SplitRate, E.HolPercent,C1.Company, C2.TCode, C3.EXECUTIVE, C3.PermRate, C3.TempRate, TGross(Qty,TUnit,TCode) AS Gross, HQty(Qty,Gross,Code) AS Daily_Hours
FROM Accounts.WageInfo W, Applic_Temp.TempEmployeesFile T, JobOrder.ClientChargeout C, JobOrder.ExtrasChargeable E, JobOrder.ClientOrderDetails C1, JobOrder.ClientInvoiceDescripts C2, Other.Consultants C3
WHERE T.UniqueVal=W.UniqueVal AND C.UniqueVal=T.UniqueVal AND C.EmployeeLink=E.EmployeeLink AND E.OurRef=C1.OurRef AND C1.Executive=C3.Executive AND C.Code=C2.Code AND C.Contract =0 AND C.Com =-1 TGross(Qty,TUnit,TCode)
AND C3.Executive=:var1
AND C.WEnded=:var2
ORDER BY C.UniqueVal

The parts that I need calculated fields for are the expression TGross(....) and HQty(...)

I do not want to make a huge SQL to cater for them.  I want to add extra fields to the TQuery at runtime instead of design time.  As mentioned previously, I can easily add them at design time in the same way you add calculated fields to a TTable.

All I want to know is what code do I use to add the extra fields programatically at runtime. - Something like Query1.Fields.Add('Somename'), but I do not know the correct syntax. I have tried all sorts of variations on that syntax but it does not work.

John

I have not tried this but it may work

qry.FieldDefs.Add('TGross', ftFloat, 0) ;
qry.Fields[IndexOfField].FieldKind := fkCalculated ;

Hope this helps.

Hi RickJ

Thanks for your reply.

I have tried your method and I cannot get it to work.  However, I have now stumbled upon the answer myself:

Procedure TForm1.AddCalcField(Dataset: TDataset; FldType: TFieldType; FldName :String);
var Fld : TField; N : integer;
begin
      Dataset.FieldDefs.Update;
      For N := 0 to Dataset.FieldDefs.Count - 1 do
        If Dataset.FindField(Dataset.FieldDefs[N].Name) = nil then
          Dataset.FieldDefs.Items[N].CreateField(Dataset);

      If Dataset.FindField(fName) <> nil then Exit;
      Case FldType of
        ftString: Fld := TStringField.Create(Dataset);
        ftInteger: Fld := TIntegerField.Create(Dataset);
        ftCurrency: Fld := TCurrencyField.Create(Dataset);
        ftDateTime: Fld := TDateTimeField.Create(Dataset);
        ftFloat: Fld := TFloatField.Create(Dataset);
      end;
      Fld.Name := Dataset.Name+FldName;
      Fld.FieldName := FldName;
      Fld.DisplayLabel := FldName;
      Fld.Calculated := True;
      Fld.DataSet := Dataset;
end;

John