The top half of the query window concerns the filter There are four criteria you can mix and match to display only certain records on a report or browse. Each of these four is a filter option. Filter Options are at the heart of Simple Accounting’s reporting capabilities, so studying this section in detail is very important.

Basically, the filter works by letting you enter one or more simple math expressions (don’t get frightened!) to indicate the records you want to display on the report, screen, or graph. For example, you might want to see a list of all customers with Year To Date activity greater than $10,000 which we might write on paper as:

Year To Date Sales >= 10000.00

So we would click on Criteria One and select Year To Date Sales from the drop down list, select ‘>=‘ as our Operator, then type 10000 in the Value field.

And if we only want to see those customers with Year To Date activity greater than $10,000 and who are in the AUTO industry, we can add another expression to further target our list of valid records:

Year To Date Sales > 10000.00

And

Industry = AUTO

Now look at the example. There are a series of rows with four columns each: the first column is the Field Name, the second is a Comparison Operator, the third column is the Field Value, and the fourth column is the And/Or Operator. Each row taken as a whole is called a Filter Option. In the example above, ‘YTD’ is a field name, ‘>’ is a Comparison Operator, ‘1000.00’ is a Field Value, and the ‘&’ sign is the And/Or Operator. You can apply filtering to any or all of the Field Names displayed in the window by filling in the desired expression for one or more Field Names.

The Comparison Operator is used in combination with the Field Value to determine which records will be selected for viewing, reporting, or graphing. Only records which meet the criteria for both the Field Value and the Comparison will be displayed. Options for the Comparison Operator are as follows:

= Records approximately equal to the entered Field Value

Name = SMITH All names beginning with SMITH, such as SMITH, SMITHWICK, SMITHERS, etc.

$ Records whose field contains the text of the entered Field Value

NAME $ WILL All names containing WILL, such as WILLIAM, GOODWILL, SWILLING, etc.

< Records less than or equal to the entered Field Value

AMOUNT < 10000.00 All amounts <= 10000.00

NAME < C All names starting with A, B, or C

 

> Records greater than or equal to the entered Field Value

AMOUNT > 10000.00 All amounts > 10000.00

NAME > C All names starting with C, D, E,…

 

= Records exactly equal to the entered Field Value

AMOUNT Exactly Equal To 10000.00 All amounts = 10000.00

NAME Exactly Equal To SMITH All names which are spelled S-M-I-T-H

 

! Records not equal to the entered Field Value

AMOUNT Not Equal To 10000.00 All amounts not equal to 10000.00

NAME Not Equal To C All names not starting with the letter ‘C’

When a Field Value and its Comparison Operator are left blank, Simple Accounting ignores that particular Field when building a list of valid records. So if you leave all fields at their default (blank or =), all records in the data file will be selected.

Date Macros

If the field you have selected is a date field, then you have several other Comparison Operators available which function as macros; that is, they automate date entry to save you keystrokes. They are:

Current Date>= Today’s date

Current Date<= Any date less than or equal to today’s date

Current Date>= Any date greater than or equal to today’s date

Month= Any date in which the month is equal to the value entered in the Value field

Month>= Any date in which the month is greater than or equal to the value entered in the Value field

Month<= Any date in which the month is less than or equal to the value entered in the Value field

Year= Any date in the current year

Year<= Any date in which the year is equal to or less than the current year

Year>= Any date in which the year is equal to or greater than the current year

Current Month Records in the current month for the Field (date fields only)

DATE Current Month / / All records entered in the current month

 

Current Year Records in the current year for the Field (date fields only)

DATE Current Year / / All records entered in the current year

 

Current Date Records in the current date for the Field (date fields only)

DATE Current Date / / All records entered today

 

For example, if you want to see all records invoiced during the month of February you would enter as one of your filter criteria:

Invoice Date Month= 2

The M tells Simple Accounting to select all records in which the Month of the Invoice Date field equals the month in the value date you entered (02)

As another example, if you want to see all records invoiced during the current year no matter what that may be, you would enter as one of your filter criteria:

Invoice Date Y=

The Y= tells Simple Accounting to select all records in which the Year of the Invoice Date field equals the current year. So for this field we do not even need to enter anything in the value field (it will be ignored even if you do.)

OK, let’s look at one more example. If you want to see all records invoiced on today’s date no matter what that day might be, you would enter as one of your filter criteria:

Invoice Date T

The T= tells Simple Accounting to select all records in which the Invoice Date field equals Today. Again, for this field we do not need enter anything in the value field.

Now you may be asking yourself why you wouldn’t just enter your range of dates with the other Comparison Operators (<, >, =, etc.). The reason is that once you save a query with a date macro, you can re-use it without having to re-key the date ranges. For example, if you don’t use a date macro, every time you want to see a list of this month’s sales orders you need to key in the range of dates. But if you use the date macro MC, and save the query, you can call up the query every time you need it with no re-keying.

Not to be too obvious, but you can combine date macro filter options freely with other filter options in the same query.

Joining Criteria

If you select more than one Field Name for filtering, Simple Accounting by default assumes that you wish to And both Field Names when building a list of valid records. In other words, if Field Name #1 meets the condition and Field Name #2 meets its criteria, then the record is valid and should be included in the displayed records. If a record meets the criteria for one of the filters, but not the other, then it is not included in the display. You can override this default behavior by using the Join Operator to determine how the results of each data field will be combined with those of other fields:

And Records which match this data field and records whose value matches other selected data fields. This is the default if you leave this field blank.

Or Records which match this data field or records whose value matches other selected data fields.

When printing a list of customers, you may wish to list only those customers who are not entered as ‘AUTO’ in CATEGORY #1. To do this, enter ‘AUTO’ in the CATEGORY #1 data field, and ‘<>’ (not equal) as the operator, leaving the other filter fields blank. Your customer list would then print out only the customers 'not in CATEGORY AUTO'.

You may want a report of sales comparing the performance of two sales people: John and Mary. To do this, enter ‘JOHN’ for the first EMPLOYEE data field, and ‘=’ (equal) as the first operator and ‘or’ as the second operator. Now arrow down to the second EMPLOYEE data field, type MARY, and ‘=’ (equal) as the first operator. Since there are no other fields we wish to filter, leave the second operator blank. This will yield a report including all sales credited to 'John or Mary'.

Don’t be afraid to experiment with complicated filters. Learning to customize your reports will increase the effectiveness of this program greatly. We’ll encounter the Filter Window and Sort Window used together at many points in this manual, so don’t worry if all is not crystal clear right now. The main things to keep in mind are:

• The Filter determines which records will be included, but not the order in which they will be displayed. The Sort determines the order in which records display.
• If you leave a data field blank, that field will be ignored when records are filtered. Leaving all data fields blank cancels out the filter, causing all records to be included.
• You may press bypass the filter entirely, including all records. (Be careful, though, as you may get more information than you bargained for!)
• The Join Operator connects the selected data field with the next selected data field in the filter expression. The filter defaults to ‘And’ if this operator is left blank.
• As with Sort Window Options, additional Filter Window Options can be added to the program for a nominal fee, contact us for details.