This function is used to issue a query against an open query database.
Click here for information on identifying an open database.
If Use previous results is not selected, the entire database is queried. If Use previous results is selected, you must specify a named variable containing the results of a previous query. When specified, the new query is applied over the previous results as opposed to the entire database. With this mechanism you can build complex queries by successive calls.
The results of the query are placed into the specified named variable. The results comprise a list of keys separated by a \~ sequence. The keys are suitable for accessing items in the database. You can enumerate the results of a query via a Repeat With statement. Note, if the database was opened Keyless, each result will take the absolute row index format: #row number. Note that the results set, when Keyless can be invalidated if a row is added or removed.
If any matches were made in response to the query, the action test state will be set to true. If no matches were found, the action test state will be set to false.
You can specify which column you want to access symbolically or numerically. A symbolic name must exactly match the name (case sensitive) of one of the database columns or all action processing will be terminated. When a query database is first opened the names of the columns are placed in a list in the Query Database -Names named variable. Numeric specifications are numbered left to right from zero. In the rare case that you have a column with a numeric name, simply leave a space in front of the number to force the symbolic lookup.
You choose a test type and data to apply the test against for each record taking part in the query. With one exception the database data is compared against the supplied data. For certain Text tests, various modifying settings are available:
- Case Insensitive
- When specified, the test will be case insensitive.
- Diacritic Insensitive
- When specified, the test will be diacritic insensitive. ie. accents will be ignored.
- Match Words
- Available on Starts With, Ends With and Contains tests. The comparison will only work if a match is made on word boundaries. For the purposes of this function a match whole word test fails if:
the first character in the from pattern is alphanumeric and the character preceding the match is alphanumeric.
or if:
the last character in the from pattern is alphanumeric and the character following the match is alphanumeric.- Reversed
- Only available on Contains tests. The supplied data is compared against the database data. ie. Does the supplied data contain the database data.
All text fields may contain any of the escape sequences described in Escape Sequences. When performing a regular expression test only track and named variables are escaped. All other escape sequences are passed to the regular expression parser.
If a specified regular expression is invalid a match will fail. In this case Text Not Matched by Regex will always succeed. You can validate and preview a regular expression with the Regular Expression Tester by using the appropriate buttons. Note that the regular expression functions are essentially contains tests. ie. if a tested database cell contains text which matches the regular expression it will succeed. If you want to match an entire cell, anchor the regular expression with ^ and $ metacharacters.
The statement is only executed once for all execution modes.