Examples of custom SQL strings

With a little practice, you can get the Find routine to do just about anything you want it to.

There are a few things to remember:

• This operation is very picky about punctuation and is case-sensitive, so double-check your query string before sending it. You don't want to hang up the program with a nonsense query. (Usually, if there's something wrong with the syntax, sending it has no action--no "hourglass" wait icon appears, and no results come up. It is possible to crash the program, though.) To see the name for a variable, just go to the main Tracy menu bar, select "Options", and look at the list in either "Edit and Find Displays/Select Fields" window or the "Results Display/Select Fields" window.

• If you need help formulating a query, you can use one of the queries auto-generated by the Find routine as a starting point. Run a regular "Find!" or "+Find!"query that is close to what you need, then choose "View SQL" and copy contents--then paste them into a "Custom SQL" window and modify them. Remember that the "Custom" window does NOT need to have the directions about which table to search and how to order the results---trim this part off! For example, use:

  CollectorName like "Monique Dubrule Reed" and County like "Grimes"
  and Country like "USA" and State like "TX" and Family like "POACEAE" 

  and NOT:

  Select Family, County, Accession, CollectionID, Genus, Species
  from "C:\Program Files\CSDL\Tracy\Collection\Collection.db"  Where 
  CollectorName like "Monique Dubrule Reed" and County like "Grimes" 
  and Country like "USA" and State like "TX" and Family like "POACEAE" 
  Order by Family, County, Accession, CollectionID, Genus, Species 

Samples:

extract (month from begindate)= "5" and County like "Grimes" 

To find everything from a county collected over a range of dates from a particular county by a particular collector:

begindate>= "1/1/90" and begindate<= "12/31/97" and County like 
"Grimes" and CollectorName like "Monique Dubrule Reed"

The Boolean not can be used to exclude certain results and or can be used to combine two searches.

For example, to make the previous search, but exclude collections by Monique Dubrule Reed:

begindate>= "1/1/90" and begindate<= "12/31/97" and County like 
"Grimes" and CollectorName not like "Monique Dubrule Reed"

To find everything collected in Brazos County in 1992-1994 by Monique Dubrule Reed (or Monique Dubrule or any combination of Monique and her chums), as well as those specimens by that collector from Leon County, regardless of date:

CollectorName like "Monique%" and begindate >="1/1/92" 
and begindate<="12/31/94" and County like "Brazos"
or
CollectorName like "Monique%" and County like "Leon" 

Other searches can be constructed in exactly the same way.

Back to the Tips page
Back to Tracy