DXKeeper: Filtering the Log Page Display with SQL

DXKeeper Online Help Contents

To create a more sophisticated search in SQL (Structured Query Language), click the Adv button to the immediate left of the Filter panel; this will display the DXKeeper's Advanced Log Sorts and Filter window. Using the SQL Query Filters panel, you can compose and activate up to eight different SQL Queries; these are automatically saved between DXKeeper sessions. Notice that this panel also gives you the ability to construct more sophisticated UTC filters, specify BAND and MODE filters, and compose up to four advanced Sorts. Like the SQL Queries, the Sorts are also saved between sessions.

The  SQL Query Filters panel contains four query textboxes that display either SQL Queries 1 to 4, or SQL Queries 5 to 8; click the ~ button in the panel's upper right corner switch between the two sets of four. To create a Query, enter the appropriate expression in one of the panel's four query textboxes; to use that query as a log filter, click the Filter button to immediate right of the query text box. If you have enough screen space, arranging things so you can see both the Advanced Log Sorts and Filter window and the main window makes it easy to compose queries and immediately see their results.

The first SQL Query can be directly invoked from the Main window's Filter panel by clicking the SQL1 button.

The database schema for logs contains one record for each QSO, and each record contains an identical set of fields.  Use each field's specified ADIF field name when constructing a query.

At the very top of the Advanced Log Sorts and Filter window, you'll find a pull-down list containing all valid field names. Having selected a field name in this list, double-clicking in one of the four query textboxes in the SQL Query Filters panel will append the field name to the query.

A simple SQL query that shows only your QSOs with VK9NS would be 


We could have simply used the main window's Call filter to accomplish that query, but

(CALL='VK9NS') AND (QSO_Begin > #1997-06-01 12:00#)

shows how to incorporate a constraint on the QSO's begin time, in this case showing only QSOs occuring after noon UTC on June 1, 1997. Notice the use of the ISO date format, which is
YYYY-MM-DD HH:MM:SS . In SQL, date constants must be enclosed between # symbols.

(CALL='VK9NS') AND (QSO_Begin between #1997-06-01 12:00# and #1999-12-1#)

illustrates the use of the "between" operator to find QSOs within a specified date/time range.


shows all QSOs with the Smith family, illustrating the use of the OR operator.

SQL provides the LIKE operator and wildcard characters to enable broader searches by specifying a pattern, for example


which shows all QSOs with callsigns whose first three characters are VK9. The Asterisk wildcard character matches 0 or more characters. The Question Mark wildcard character matches exactly one character. Thus


shows all QSOs VK9X, but not those with VK9NS or VK9NL.


uses the NOT operator to show all Norfolk QSOs not involving Jim.

Besides the Asterisk and Question Mark, the LIKE operator provides wildcard characters that let you specify a single digit, or a range of characters, as illustrated in the table below:

To match... Example Samples that match Samples that don't match
one or more characters VU4*W VU4CW, VU4WWW, VU41W VU2CW, VU4DY
one or more characters *YV1DIG* YV1DIG, YV0/YV1DIG, YV0/YV1DIG/QRP YV0/YV1DX
one character OX1VHF/? OX1VHF/P, OX1VHF/5,OX1VHF/M OX1VHF, OX1VHF/MM
one digit A6#AJ A61AJ, A64JA A6JA, C61AJ
a range of characters A[A-L]6DX AA6DX, AF6DX  AM6DX, A6DX, AA6DY
outside a range of characters K[!G-H]4DX KC4DX, KK4DX, K$4DX KG4DX, KC4DY
outside the range of digits K5[!0-9] K5K, K5% K50
a pattern composed of several sub-patterns A[A-L]#[A-Z]* AA6YQ, AL7X  AM4DX, KH6/AL7X, AA6
characters that are wildcard characters [*]Footnote *Footnote Footnote, -Footnote

Note that you can combine multiple wildcards to specify more complex patterns.

In summary, you can use the following comparison and logical operators to create filters:

Operator Meaning Example
= equal to PROP_MODE='F2'
< less than QSO_BEGIN < #2003-12-31 12:00#
<= less than or equal to QSO_END <= #2003-12-31 12:00#
> greater than QSO_BEGIN > #2003-12-31 12:00#
>= greater than or equal to QSO_END >= #2003-12-31 12:00#
<> not equal to MODE <> 'SSB'
LIKE used to match a pattern QTH LIKE '*Pittsburgh*'
BETWEEN...AND used to specify a range of values QSO_BEGIN BETWEEN  #2003-12-31 12:00# and  #2004-01-01 12:00#
IN used to specify a set of values MODE IN ('PSK31','PSK63','MFK8','MFSK16')
Operator Meaning Example
AND both conditions must be true (PROP_MODE='F2') AND (MODE <> 'SSB')
OR either condition can be true (QTH LIKE '*Pittsburgh*') OR (QTH LIKE '*Philadelphia*')
NOT logical inversion (STATE='PA') AND NOT (QTH LIKE '*Philadelphia*')

If the character string
<filtertextbox> is found in an SQL Query being executed, it is replaced by the contents of the Filter textbox. Thus the query

QTH like '*<filtertextbox>*'

with the Filter textbox set to 


results in execution of the query

QTH like '*Philadelphia*'

If you then change the contents of the Filter textbox to


and invoke the query,

QTH like '*Pittsburgh*'

will be executed.

A online reference for SQL as supported by the Microsoft Jet engine, which is incorporated in both DXKeeper and Microsoft Access, is available at http://www.devguru.com/Technologies/jetsql/quickref/jet_sql_intro.html .

DXKeeper Online Help Contents