Autosearch date format

rbuser

I have created an End-user interfase using ADO+Informix, but I found that I
can't supply dates for autosearch fields, ADO driver wants yyyy/mm/dd format
but if I use this format I get nothing from the Query builder, and if I use
mm/dd/yyyy I get the SQL sentence generated but get a sintax error on ADO
informix driver.

Any suggestion?

Thanks in advance

Eduardo Martinez

rbuser

> I have created an End-user interfase using ADO+Informix, but I found that
I
format
use


Maybe this code helps. It registers a special autosearch field which
automatically is displayed in teh end user dialog if the datatype is
TDateTime. Have a look where the control is created, there youare free to
set formats as you like it. Comments are in german - hope you don?t mind :-)
but the code is straight forward.

hth
Bernd

unit AutoSearchDialog;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
StdCtrls, ExtCtrls, ppForms, ppASCtrl, ppDB, ppTypes, ppUtils, ppASDlg,
ComCtrls, ppASField;

type
// Die Klasse TAdvanceAutoSearchDialog dient um beim Anzeigen des
Suchdialoges, den richtigen abzufangen und
// anzuzeigen.
TAdvanceAutoSearchDialog=class(TppAutoSearchDialog)
protected
// Die Methode GetPanelClassForField ?berpr?ft, ob es sich beim
darzustellenden Suchfeld um einen von ReportBuilder
// oder implementierten Suchdialog handelt. Falls es sich nicht um den
von ReportBuilder handelt, wird die
// entsprechende Klasse aufgerufen.
procedure GetPanelClassForField(aField: TppAutoSearchField; var
aPanelClass: TppAutoSearchPanelClass); override;
end;

// Die Klasse TDateSearchPanel dient um einen Datumssuchdialog anzuzeigen.
TDateSearchPanel=class(TppSimpleSearchPanel)
private
dtSearchPicker: TDateTimePicker;
protected
procedure ShowAllValuesClickEvent(Sender: TObject); override;
public
// Die Methode Create erzeugt und initialisiert alle Objekte, die f?r
diese Klasse notwendig sind.
constructor Create(aOwner: TComponent); override;
// Die Methode Destroy gibt wieder alle Objekte frei, die in dieser
Klasse initialisiert worden sind.
destructor Destroy; override;
// Die Methode Init initialisiert DateTimePicker und setzt den richtigen
Wert ein.
procedure Init; override;
// Die Methode Valid setzt das ausgew?hlte Datum ein, falls der Checkbox
"Bedienung ausblenden" ausgeschalten bleibt.
function Valid: Boolean; override;
end;

implementation

procedure TAdvanceAutoSearchDialog.GetPanelClassForField(
aField: TppAutoSearchField; var aPanelClass: TppAutoSearchPanelClass);
begin
if aField.DataType=dtDateTime then
aPanelClass := TDateSearchPanel;
end;

constructor TDateSearchPanel.Create(aOwner: TComponent);
begin
inherited Create(aOwner);
dtSearchPicker:= nil;
end;

destructor TDateSearchPanel.Destroy;
begin
FreeAndNil(dtSearchPicker);
inherited;
end;

procedure TDateSearchPanel.Init;
var
stValues: TStrings;
begin
inherited;
EditControl.Visible := False;

dtSearchPicker:= TDateTimePicker.Create(Self);
dtSearchPicker.Parent:= Self;
dtSearchPicker.DateTime:= Now;
dtSearchPicker.Left:= EditControl.Left;
dtSearchPicker.Top:= EditControl.Top;
dtSearchPicker.Width:= EditControl.Width;

stValues:= TStringList.Create;
ppParseString(Field.SearchExpression, stValues);
if (stValues.Count > 0) then
dtSearchPicker.Date := ppStrToDateTime(stValues[0]);
FreeAndNil(stValues);
end;

procedure TDateSearchPanel.ShowAllValuesClickEvent(Sender: TObject);
begin
inherited;
dtSearchPicker.Enabled:= not(ShowAllValues.Checked);
if (dtSearchPicker.Enabled) then
dtSearchPicker.SetFocus;
end;

function TDateSearchPanel.Valid: Boolean;
begin
Result := True;
Field.ShowAllValues := ShowAllValues.Checked;
if not(Field.ShowAllValues) then
Field.SearchExpression := DateToStr(dtSearchPicker.Date);
end;

initialization
ppRegisterForm(TppCustomAutoSearchDialog, TAdvanceAutoSearchDialog);

finalization
ppUnRegisterForm(TppCustomAutoSearchDialog);

end.

rbuser

Thanks a lot for your help, I'll try the code you sent (I don't know a word
in German but it's ok may be you don't know too much of spanish either) but
I think I didn't explained my self very well

Let's say that I open the design of a reporte and open the Query Builder,
then I pick a table (invoices for example), pick some fields (invoice #,
client #, address, amount, tax and date) and then I want to filter records
for a given date range. So in the Search tab you pick the Date field and set
the operator to "between" and put to date's range divided with a comma, like
12/01/2003,12/15/2003 The SQL that is generated in SQL tab would look like:

select invoice, client , address, amount, tax, date from invoices where date
between '12/01/2003' and '12/15/2003'

But this generates a "Sintax error" with my ADO conection to Informix.

If I edit directly and put the next SQL sentence:

select invoice, client , address, amount, tax, date from invoices where date
between '2003/12/01' and '2003/12/15'

then the query executes correctly but I can't continue using Query Builder.

If in the Query Builder's Search tab I write 2003/12/01,2003/12/15 then I
get the next sentence generated:

select invoice, client , address, amount, tax, date from invoices where date
between and

wich obviously generates an error since there are no dates.

I want to know if there is some way to change the date's format that is
generated by the Query Builder or if it is possible to change tha format ADO
uses to do the query.

Thanks again

Eduardo Martinez

"Bernd Maierhofer (dato)" escribi? en el mensaje

rbuser

Problem solved by setting Informix's environment variable DBDATE to "MDY4"

Thanks everyone, best regards and best wishies this holidays

Eduardo Martinez