Search/Query

Purpose:

Limit the display of records to those that match specific search criteria. This action is similar to dbpFind, but dbpQuery allows you to search multiple fields at the same time with more complex search criteria. After calling dbpQuery, only records matching the search criteria will be visible. You can examine the special variable [ID.Table.$RecCount] to determine the number of records returned by the query. Navigation actions, such as dbpFirst, dbpPrev, dbpNext and dbpLast, will only display records that fall within the query's search parameters. To cancel the query and display all records, use the dbpShowAll action.

Category:

Search/Query

Syntax:

dbpQuery "database id" "table" "filter"

database id

The name assigned to the database.

table

The name of the table to query.

filter

The filter defines the parameters of your query. The filter generally consists of three elements - a field name, an operator and search string. The field name is the name of the field you want to search. Search string is the text or data you want to find. The operator can be one of the following:

=	The search string matches the field contents exactly. For example: City = "Pittsburgh"
LIKE	The search string is similar to the field contents. The LIKE operator allows you to include wildcards to find partial matches. For example, the following will find all customers having a first name that starts with the letter J: FirstName LIKE "J%" The % character is a wildcard. The wildcard can be placed anywhere in the string and you can use as many wildcards as you like. Here are some examples: LastName LIKE "%duff%" will find "Duffy", "Macduff", "Duffel", etc. Phone LIKE "416%" will find telephone numbers beginning with 416. Another wildcard is the underscore character (_) which can be used to represent any single character. For example: LastName LIKE "sm_th" will find "Smith", "Smyth", "Smath", "Smeth", etc.
<	The contents of the field is less than the search string. This is most often used with numeric fields. For example: Salary < 50000
>	The contents of the field is greater than the search string. This is most often used with numeric fields. For example: Salary > 50000
<=	The contents of the field is less than or equal to the search string. This is most often used with numeric fields. For example: Age <= 65
>=	The contents of the field is greater than or equal to the search string. This is most often used with numeric fields. For example: Age >= 18
<>	The search string does not match the contents of the field. This is most often used with numeric fields. For example: Height <> 6.1
BETWEEN	The contents of the field falls between two values. This is most often used with numeric fields. For example: Age BETWEEN 18 AND 34 Note: For string type fields, data must surrounded by double quotes. For example: Age BETWEEN "18" AND "34"
IN	The contents of the field matches a specific set of values. For example: Age IN (18,19,28,29,38,39) Note: For string type fields, data must surrounded by double quotes. For example: Age IN ("18","19","28","29","38","39")

For example, the following filter will find all records where the Salary field equals $50,000:

"Salary = 50000"

If the field being searched is a String, Char or Memo type then the search string must be surrounded by quotes which, in VisualNEO for Windows, are specified using the special code: [#34]. For example:

"City = [#34]Pittsburgh[#34]"

You can also construct more complex queries by combining more than one filter. For example, to find all records where the City field equals either Pittsburgh OR St Louis:

"City = [#34]Pittsburgh[#34] OR City=[#34]St. Louis[#34]"

To find records where the City field equals Pittsburgh and the LastName field equals Jones:

"City = [#34]Pittsburgh[#34] AND LastName = [#34]Jones[#34]"

The < and > operators are Primarily useful when searching fields that contain numbers. For example, to Search our inventory database for items costing less than $10, we might use a filter like this:

"Parts < 10.00"

At times you may want to find records that don't match certain criteria. For this you can add the special operator "NOT". For example:

"NOT Country = [#34]USA[#34]"

There are certain situations where you might want to find fields that are empty. To do this you can use the special operator "IS" and the keyword "NULL". For example:

"Country IS NULL"

Or to find non empty fields:

"Country IS NOT NULL"

Note: Advanced users familiar with SQL can perform more advanced types of queries using the dbpExecSQL action.

Example:

dbpQuery "AddrBook" "Contacts" "State = [#34]CA[#34]"

You can use the special variable [ID.Table.$RecCount] to determine if the query was successful. For example:

dbpQuery "AddrBook" "Contacts" "State = [#34]CA[#34]"

If "[AddrBook.Contacts.$RecCount]" "=" "0"

AlertBox "Error" "No records matching the search criteria were found."

dbpShowAll "AddrBook" "Contacts"

EndIf

SQL Equivalent:

SELECT * FROM table WHERE filter

Purpose:	Discontinue current search query and display all records. See dbpQuery.
Category:	Search/Query
Syntax:	dbpShowAll "database id" "table" database id The name assigned to the database. table The name of the table.
Example:	dbpShowAll "AddrBook" "Contacts"
SQL Equivalent:	SELECT * FROM table

Purpose:

Search one or more fields for a specific string or value and display the first matching record. Use dbpFindNext to display additional matching records. See dbpQuery for more complex searches.

Category:

Search/Query

Syntax:

dbpFind "database id" "table" "fields" "search string" "options"

database id

The name assigned to the database.

table

The name of the table.

fields

A single field or list of fields to search. Multiple fields will be searched in the order that they appear. Separate multiple fields with semicolons (;).

search string

The text or value you want to find.

options

This is a compound parameter and can contain any combination of the following items:

ExactMatch=Yes/No	Yes = search string and the contents of the field must match exactly. No = a partial match is acceptable.
CaseSensitive=Yes/No	Yes = upper and lower case letters in the search string must be identical to the field contents. No = ignore case.

Separate multiple items in a compound variable with semicolons (;). This parameter may be left blank if you do not require either of these options.

Example:

dbpFind "AddrBook" "Contacts" "FirstName;LastName;Company" "[SearchStr]" "ExactMatch=No;CaseSensitive=No"

SQL Equivalent:

N/A

Purpose:	Search for the next record that matched the previous dbpFind criteria. dbpFind must be called at least once before calling dbpFindNext.
Category:	Search/Query
Syntax:	dbpFindNext "database id" "table" database id The name assigned to the database. table The name of the table.
Example:	dbpFindNext "AddrBook" "Contacts"
SQL Equivalent:	N/A