Question about soBlank and soNotBlank search operators

rbuser

Greetings,
I'm using Delphi 7 and Reportbuilder 7.02. I'm trying to add an
AutoSearchField, which will allow the end-user to include or ignore null
values in a date field. The soBlank and/or soNotBlank operators appear to
be ideal for this, but I can't seem to make the code work correctly in the
BeforeAutoSearchDialogCreate event. I didn't see an example in the
rbuilder\demos subdirectory for this particular parameter.

The help file states that no search value is required for this type of
search, but omitting it gives a syntax error, and passing a blank results in
a runtime type mismatch error. Changing the data type to dtBoolean doesn't
work, and using dtUnknown results in a compile time error that 'dtUnknown'
is an Undeclared identifier.

If you could point me in the right direction on this, I'd be most grateful.
An example would be ideal, but any hints would be appreciated. Thanks!

Mitchell Schaff
mschaff@nemontel.net

nicocizik

Mitchell,

You need to manually set the table filter or sql criteria of your query when
setting your autosearch criteria. In other words, after you define the
search properties (ie. Report.CreateAutoSearchField), you need to change
the table filter or query SQL in the Report.GetAutoSearchValues event. See
the AutoSearch Table filter example located in the \RBuilder\Demos\5.
AutoSearch\2. AutoSearch Field to Table Filter\... directory for an example
of this.

--

rbuser

Nico,
I think I understand. What I was actually trying to do was to incorporate
the technology in the "AutoSearchFieldsToSQLWhereClause" example project,
which cycles through all the AutoSearchfields, and automatically generates
the appropriate SQL code, then inserts it into the query for my report.

I can handle the soNotBlank conversion to SQL manually, because the
AutoSearchField.SQLString method doesn't seem to know how to correctly
create the SQL code for an soNotBlank AutoSearchField. It generates the
correct SQL code to allow null values, but then inserts the date string from
the date expression parameter into the SQL string, which breaks the SQL.
The resulting string is something like "DateField is not null 05/05/2003",
where the 05/05/2003 comes from a dummy value that I'm passing to
CreateAutoSearchFields. I can manually strip that last date expression out,
and the SQL code works correctly. But the whole SQLString method doesn't
work the same way for this type of search option as it does for an soLike
search option, for example.

Also, I'm still curious about the use of the dtUnknown data type. That type
is listed in the ReportBuilder help file, but Delphi doesn't recognize it.
Am I omitting a needed file in my "uses" clause, or is this data type not
actually defined in ReportBuilder (a documentation error, perhaps?). The
other dtxxx types compile fine, but not this one.

Also, I have another question about AutoSearchFields. In my report
selection criteria, I'd like to provide the user with the option of
selecting the report contents based on a date range. Piece of cake, using
the soBetween option, and generating the SQL using the SQLString method.
The twist is that I'd also like the user to have the option of including or
excluding Null values for this date field. In essence, I want the user to
be able to say: "Give me all the records with (a) a date between 4/1/03 and
5/1/03 OR (b) a blank date." The examples all show AND logic, with respect
to the AutoSearchFields. I'm interested in providing OR functionality. Is
there a technique built in to ReportBuilder, which could help with this
problem?

Thanks for your original response, and to any responses to my latest
questions. I am most grateful for your help.

Mitchell Schaff
mschaff@nemontel.net

nicocizik

Mitchell,

1. Try adding ppTypes to the 'uses' clause to enable the dtUnknown
datatype.

2. Check out the daSQL.pas file in the TppCriteriaType class for the
definitions of the operators to use to get an "OR" in your Autosearch
criteria by code. These are the same operators used when you right click on
your search criteria in the DADE Query designer. The dacrOR operator is
what you will want to use.

--

rbuser

Nico,
Thank you, once again, for your reply - I appreciate the feedback. I
checked my report unit, and ppTypes is already in place. Browsing through
the ppTypes.pas unit, I see the other dtxxx types, but no dtUnknown type. A
search for 'unknown' comes up empty. Again, this is with the Pro version
7.02.

I briefly looked into the daSQL.pas file, although a search for
tppcriteria came up blank. In the particular report that I'm creating, I
haven't enabled the Query designer function. Instead, to simplify the
process for the end user, I created two autosearch fields. The first allows
the end-user to specify a date range, and the intent of the second was to
allow the end-user to include or exclude the dates which were null. It
appears that I may have to manually generate the sql code to do that, since
the sqlstring method of the autosearchfield is not handling that correctly.

If you have any additional suggestions, I'd appreciate hearing them.
Regardless of that, thanks for the information thus far.

Mitchell Schaff
mschaff@nemontel.net

nicocizik

Hi Mitchell,

1. Sorry, this is a mistake in the help file. dtUnknown is not a supported
type in Reportbuilder. Thanks for pointing that out.

2. You will want to look for the TdaCriteria class in the daSQL.pas file.

3. It looks as though you need to create a third autosearch field using the
dacrOR operator inbetweent the two autosearch fields you already have.
Check out the example below for the code you will need to use to insert the
OR operator.

http://www.digital-metaphors.com/tips/ExtractSQLObject.zip

--