Is it possible to format date constants in criteria fields automatically?
One of the common issues I and my users are having is our brains and data entry on the dates fields. We’re used to typing in 2/27/2012 and the criteria field would know it is a date field and reformat it to ‘02/27/2012’. Is there a way to add this to the product or a work around so the users don’t have to type the apostrophes?
I had this issue and wrote a routine to quote fields.
In the GridCellChanging method, I put the following code;
<code>
procedure TfmAQBuilder.QueryBuilderGridCellChanging(
Sender: TacQueryBuilder; AUnionSubQuery: TacUnionSubQuery;
ASelectList: TacSelectList; ASelectItem: TacSelectItem;
AGrid: TacQueryBuilderSelectListControl; ACol, ARow: Integer;
AOldValue: WideString; var ANewValue: WideString; var AAllow: Boolean);
const QuotableFieldTypes = [ftString, ftBlob, ftMemo, ftGraphic, ftFmtMemo, ftFixedChar,
ftWideString, ftOraClob, ftDate, ftDateTime, ftTime, ftUnknown];
// Numeric Aggregates are set for SQL Server
NumericAggregates : Array [0..3] of string = ('Count', 'Checksum_Agg', 'StDev', 'Var');
var
FieldName : string;
FieldNo : Integer;
I : Integer;
begin
if (chkParseCriteria.Checked) and
((ACol >= AGrid.FirstConditionColumnNum) and (ACol <= AGrid.LastConditionColumnNum)) and
(AOldValue <> ANewValue) and (ANewValue <> '') then
begin
// use case insensitive match - truncate aggregate to allow "Count", "Count_Big", etc
if PosInStringArray(UpperCase(ASelectItem.AggregateString), NumericAggregates, [pisaTruncate]) > -1 then
begin
// Aggregate funtion returning a number not the original fieldtype
ANewValue := ParseAQBSQLText(ANewValue, False);
end
else
begin
// Get the name of the underlying field
if Pos('.', ASelectItem.ExpressionString) > 0 then
begin
FieldName := CopyRightS(ASelectItem.ExpressionString, Pos('.', ASelectItem.ExpressionString) + 1);
end
else
begin
FieldName := ASelectItem.ExpressionString;
end;
// find the field number in the used objects
FieldNo := -1;
for I := 0 to Sender.QueryStatistics.UsedDatabaseObjectColumns.Count - 1 do
begin
if UpperCase(FieldName) = UpperCase(Sender.QueryStatistics.UsedDatabaseObjectColumns[I].MetadataField.NameStr) then
begin
FieldNo := I;
Break;
end;
end; // for
if FieldNo < 0 then
begin
ANewValue := ParseAQBSQLText(ANewValue, False);
end
else
begin
ANewValue := ParseAQBSQLText(ANewValue, (Sender.QueryStatistics.UsedDatabaseObjectColumns[FieldNo].FieldType in QuotableFieldTypes));
end;
end;
end;
end;
<end code>
Then the library routine I call is:
<code>
function ParseAQBSQLText(Const InputString: string; UseQuotes: boolean): string;
// Parses SQL Text into AQB compatible format, quoting if required
var
BadPos : Cardinal;
CutPoint : Integer;
I : Integer;
OutputList : TStringList;
OutputString : string;
Reps : Cardinal;
WorkingList : TStringList;
WorkingString : string;
begin
// Check for non-parsing conditions
if (Trim(InputString) = '') or // Empty string
(Pos('''', InputString) > 0) or // Already quoted
(UpperCase(Trim(InputString)) = 'NULL') or // Contains NULL
(UpperCase(Trim(InputString)) = 'IS NULL') or // Contains IS NULL
(UpperCase(Trim(InputString)) = 'NOT NULL') or // Contains NOT NULL
(UpperCase(Trim(InputString)) = 'IS NOT NULL') then // Contains Is NOT NULL
begin
result := InputString; // Don't Change it
end
else if ((Pos(' AND ', UpperCase(InputString)) = 0) and
(Pos(' OR ', UpperCase(InputString)) = 0)) and
(Pos(Copy(Trim(InputString), 1, 1), '>=<(+-') = 0) then
begin
if UseQuotes then
begin
result := QuotedStr(InputString); // Just quote it
end
else
begin
result := InputString; // Don't Change it
end;
end
else
begin
// Transform a free text string into an AQB compatible condition string
OutputList := TStringList.Create;
try
WorkingList := TStringList.Create;
try
OutputString := '';
WorkingString := InputString;
// Strip and retain spaces
while Copy(WorkingString, 1, 1) = ' ' do
begin
OutputString := OutputString + ' ';
WorkingString := CopyRightS(WorkingString, 2);
end; // while
OutputList.Add(OutputString);
// Make sure brackets are isolated
Reps := 1;
WorkingString := ReplaceStringAllS(WorkingString, '(', ' ( ', Reps);
Reps := 1;
WorkingString := ReplaceStringAllS(WorkingString, ')', ' ) ', Reps);
WorkingString := Trim(WorkingString);
// Break up the rest
ExtractTokensS(WorkingString, ' ', #0, False, WorkingList);
for I := 0 to WorkingList.Count - 1 do // Iterate
begin
OutputList.Add(WorkingList[I]);
end; // for
finally
// free resources
WorkingList.Free;
end; // try/finally
if OutputList.Count = 2 then
begin
// Special Case - single term
if (Pos('%', OutputList[1]) > 0) or
(Pos('_', OutputList[1]) > 0) then
begin
OutputList[1] := 'Like ' + QuotedStr(OutputList[1]);
end
else if UpperCase(OutputList[1]) = 'NULL' then
begin
OutputList[1] := 'Is ' + OutputList[1];
end
else
begin
if UseQuotes then
begin
OutputList[1] := '= ' + QuotedStr(OutputList[1]);
end;
end;
end
else if OutputList.Count = 3 then
begin
// Comparison operator + term
if UseQuotes then
begin
OutputList[2] := QuotedStr(OutputList[2]);
end;
end
else
begin
// Now process each item in turn
for I := 1 to OutputList.Count - 1 do // Iterate
begin
if ContainsOnlyS(OutputList[I], '()', BadPos) then
begin
// Ignore brackets
end
else if ContainsOnlyS(OutputList[I], '=<>', BadPos) or
(UpperCase(OutputList[I]) = 'NOT') then
begin
// Operator
if NOT((OutputList[I - 1] = '(') and
(OutputList[I + 2] = ')')) then
begin
// Add Brackets around expression
OutputList[I] := '( ' + OutputList[I];
OutputList[I + 1] := OutputList[I + 1] + ' )';
end;
end
else if (UpperCase(OutputList[I]) = 'OR') then
begin
// Handle "Or"
OutputList[I] := UpperCase(OutputList[I]);
end
else if (UpperCase(OutputList[I]) = 'AND') then
begin
// Handle "And"
OutputList[I] := UpperCase(OutputList[I]);
end
else
begin
// Just a term
if CopyRightAbsS(OutputList[I], 1) = ')' then
begin
// Already bracketted; Quote the first part only
CutPoint := Pos(' ', OutputList[I]);
OutputList[I] := QuotedStr(CopyLeftS(OutputList[I], CutPoint - 1)) + CopyRightS(OutputList[I], CutPoint);
end
else
begin
// Handle existing external brackets
if ContainsOnlyS(OutputList[I - 1], '=<>', BadPos) then
begin
// Previous term was operator
if UseQuotes then
begin
OutputList[I] := QuotedStr(OutputList[I]);
end;
end
else
begin
// Not an operator - simple term
if NOT((OutputList[I - 1] = '(') and
((OutputList.Count > (I + 1)) and (OutputList[I + 1] = ')'))) then
begin
if (Pos('%', OutputList[I]) > 0) or
(Pos('_', OutputList[I]) > 0) then
begin
OutputList[I] := '( Like ' + QuotedStr(OutputList[I]) + ' )';
end
else if UpperCase(OutputList[I]) = 'NULL' then
begin
OutputList[I] := '( Is ' + OutputList[I] + ' )'
end
else
begin
if UseQuotes then
begin
OutputList[I] := '( = ' + QuotedStr(OutputList[I]) + ' )';
end
else
begin
OutputList[I] := '( = ' + OutputList[I] + ' )'
end;
end;
end
else
begin
if (Pos('%', OutputList[I]) > 0) or
(Pos('_', OutputList[I]) > 0) then
begin
OutputList[I] := 'Like ' + QuotedStr(OutputList[I]);
end
else if UpperCase(OutputList[I]) = 'NULL' then
begin
OutputList[I] := 'Is ' + OutputList[I];
end
else
begin
if UseQuotes then
begin
OutputList[I] := '= ' + QuotedStr(OutputList[I]);
end
else
begin
OutputList[I] := '= ' + OutputList[I];
end;
end;
end;
end;
end;
end;
end; // for
end;
OutputString := '';
for I := 0 to OutputList.Count - 1 do // Iterate
begin
if I > 1 then // Don't pad leading spaces
begin
OutputString := OutputString + ' ';
end;
OutputString := OutputString + OutputList[I];
end; // for
finally
// free resources
OutputList.Free;
end; // try/finally
result := OutputString;
end; // Parse text
end;
<end code>
Hope that may be of some use.
... Joe
Member of the UK Developers Group