Format query field before Batch Move

[lespaul]

I have a query that is used to search for Prior jury service (if you served within the last three years, you are exempt from duty). So I run a query that returns all the people from the last three years in order to verify for other agencies that the person is exempt. Since most of the data doesn't change on a day to day basis (most of the changes are to FUTURE jurors), I have created a local table of the 35000+ past jurors (this is updated with a Batch Move component in my data module). However, the date of birth field in my LIVE database is stored as MMDDYYYY (02011967). I would like to display this as MM/DD/YYYY (02/01/1967) to make it a little easier to read. Can anyone suggest a way to change the dataset BEFORE the batch move so that the information stored in the local table is in "correct" date format? If not, then a way to display it properly in the grid. I tried both ways but had no luck. All suggestions appreciated!

Thanks!


Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[towerbase]

Why not use the OnGetText event for the field in question? You could then display the date in the grid in any format that you choose.

The format for the date of birth field in your live database is a bit eccentric. Why not use a proper date field?

Andrew

 

[lespaul]

because the AS400 doesn't have a date field (trust me, it's an ISSUE!)

I tried to use GetText earlier and didn't have any luck. I'll try again (I think I was missing something because I couldn't find the GetText - I had to add the fields!)

Thanks!
Leslie

 

[richardchaven]

TField.OnGetText is a good place to create custom formats for users.

You don't actually have to add the fields at design-time if you don't want (e.g. if the schema might change--especially with a query).

This code allows you to add a calculated field or a lookup field from the BeforeOpen event. You can modify it so you create TFields just for the columns you want instead of all of them. Once you have a TField, you can set its event handler to a (private) procedure that has the correct arguments and it is just like you did it at design-time.

function OpenField(ADataSet : TDataSet; ACalcFieldName : string; ACalcFieldClass : TFieldClass) : TField;
var
   Counter : Integer;
begin
   with ADataSet do
   begin
       if (DefaultFields) or (FieldCount = 0) then
       begin
           FieldDefs.Update;
           for Counter := 0 to FieldDefs.Count - 1 do
               FieldDefs[Counter].CreateField(ADataSet);
       end;

       Result := FindField(ACalcFieldName);
       if Result = nil then
       begin
           Result := ACalcFieldClass.Create(ADataSet);
           with Result do
           begin
               Calculated := True;
               FieldName := ACalcFieldName;
               DataSet := ADataSet;
               DisplayLabel := StringReplace(DisplayName, '_', ' ', [rfReplaceAll]);
           end;
       end;
   end;
end;

function OpenField(ADataSet : TDataSet; AFieldName : string; AFieldClass : TFieldClass; ALookupDataSet : TDataSet; const AKeyFields : string; const ALookupKeyFields : string = ''; const ALookupResultField : string = '') : TField;
var
   Counter : Integer;
begin
   with ADataSet do
   begin
       if DefaultFields or (FieldCount = 0) then
       begin
           try
               FieldDefs.Update;
           except
{$IFDEF DEBUG}
               if ADataSet is TQuery then
                   TQuery(ADataSet).SQL.SaveToFile(Session.PrivateDir + '\BadOPENFIELD.TXT');
{$ENDIF}
               raise;
           end;
           for Counter := 0 to FieldDefs.Count - 1 do
               FieldDefs[Counter].CreateField(ADataSet);
       end;

       Result := FindField(AFieldName);
       if Result = nil then
       begin
           Result := AFieldClass.Create(ADataSet);
           with Result do
           begin
               Lookup := True;
               FieldName := AFieldName;
               DataSet := ADataSet;
               DisplayLabel := StringReplace(DisplayLabel, '_', ' ');
               LookupDataSet := ALookupDataSet;
               KeyFields := AKeyFields;
               if ALookupKeyFields = '' then
                   LookupKeyFields := AKeyFields
               else
                   LookupKeyFields := ALookupKeyFields;
               if ALookupResultField = '' then
                   LookupResultField := AFieldName
               else
                   LookupResultField := ALookupResultField;
           end;
       end;
   end;
end;

Cheers