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
It is not possible in the Query Columns Grid, but we are in the process of creating a tree-like criteria editor in which the user will input date and time values using an appropriate editing controls.