?
Solved

Calculated Fields in a Query

Posted on 2003-11-26
10
Medium Priority
?
426 Views
Last Modified: 2010-04-05
Hi

I have a series of queries where I populate the SQL at runtime so I do not have lots of Query components on my form.  

I need to add calculated fields at runtime, but I cannot work out how to do it.

I cannot just add to my SQL something like "Qty*UnitPrice AS Amount" - I need to be able to call functions from OnCalcFields.

John
0
Comment
Question by:jdthedj
9 Comments
 
LVL 2

Expert Comment

by:TheLeader
ID: 9828657
did you use Count function  !
ex:

Count (FieldName)
it will returns you the right result
0
 
LVL 3

Author Comment

by:jdthedj
ID: 9829281
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
0
 

Expert Comment

by:lamtl354
ID: 9829431
i duno what is about?
could you post some code of yours here?
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
LVL 3

Author Comment

by:jdthedj
ID: 9829550
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.
0
 
LVL 2

Expert Comment

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

could you be more specific ?
0
 
LVL 3

Author Comment

by:jdthedj
ID: 9830042
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
0
 
LVL 8

Expert Comment

by:RickJ
ID: 9836689
I have not tried this but it may work

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

Hope this helps.
0
 
LVL 3

Author Comment

by:jdthedj
ID: 9839561
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
0
 

Accepted Solution

by:
SpazMODic earned 0 total points
ID: 9853170
PAQed, with points refunded (125)

SpazMODic
EE Moderator
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question