To do a basic search use the Quick Search option which will look up the relevant word for you e.g. John and click Quick search in Customers Module, this searches all Customer Records for instances of this word.
When finished clear your search as it could interfere with using the more advanced search detailed below unless used in conjunction with it.
The Search option allows you to refine the lists available in any of the modules in Sage Accounts. For example, if you have a large number of customers you can use the Search option to reduce the number that appear when you open the Customers window.
The Search option restricts the records that appear to those that you specify, enabling you to quickly locate information. In all modules, except Financials, if a search is applied and you run a report, the report is limited to the records that appear on the main list. The Search option is available in all of the program modules, except the Bank module.
This article contains the following sections:
- To create a search – This section explains how to create a Search.
- Search Value Filters – When you create a search you may want to display a specific record, or you may want to display a range of records or use a complicated filter.
Note: If a search is applied within a module, then the module banner states how many records have been found that match the search criteria. To toggle the display, click the spy glass on the module banner.
To create a search
Note: In this example, we are creating a search on the Customers list, which excludes customers with a zero balance from the main Customers list and from the customer reports.
- Open the relevant module, for example, Customers, from the Links pane, click Customer List then click Search.
If you have not previously created a search in the selected module, the Search window appears without any details.
If you have previously created a search, the Search window appears displaying the last search.
- To clear a previously created search and create a new one, click Discard.
The Search window clears.
Tip: If you require further assistance creating your search, click Wizard then follow the steps in the Expression Wizard.
- Complete the Search window as follows:
- From the Join drop-down list, choose Where.
- From the Field drop-down list, choose the field you want to Search on, for example, Balance.
- From the Condition drop-down list, choose one of the following conditions:
- Is Equal to
- Is Not Equal to
- Is Less Than
- Is Greater than
- Is Less Than or Equal To
- Is Greater Than or Equal To
- Is Between – Not available in Financials or on all fields.
For example, to exclude customers with a zero balance, the Field column choose Balance then choose the Is Not Equal To condition, and leave the value set to 0.00.
Note: The conditions available vary according to the field selected.
- In the Value box, enter the value to be applied to the Condition.
Note: When using the Between condition, the two values must be split by a comma. For example, to display nominal codes between a certain range, choose the Nominal Code field, the Between Condition. Then in the Value box, enter the nominal code to start from, followed by a comma and the last nominal code, for example, 4000,4009.
For further information about refining the Value information, please refer to the section Search Value Filters later in this article.
- If required, to apply the search without adding a further line, please proceed to step 5.
If required, to add another line to the Search, after entering the Value, from your keyboard press the TAB key. On the second line, from the Join drop-down list, choose Or or And as required then from your keyboard press the TAB key. To complete the additional search line, repeat step 3.
Note: When you add a second line, you need to decide whether you want both, or either, condition to be met. If you use the AND command to link two expressions, both expressions must apply for a record to be found. To link expressions where either expression must be true for a record to be found, you should use the OR command.
- To apply the search, click Apply then to close the Search window, click Close.
The search is applied and the module window appears with the search applied. The number of records found matching the search appears on the module banner.
You have now applied the search to the selected module.
Search Value Filters
Wildcards are special characters that can be used to represent a line of text, or an individual character. They can be used in the Value box when you want to select a range.
The following wildcards can be used:
- ? – Represents a single undefined character, for example, WE?001 finds WEB001 and WET001 and so on.
- * – Represents any numbers of valid characters, including spaces, after a specific value, for example, NE* finds all text beginning with NE.
- $ – Represents a specific line of text anywhere in the data, for example, $ABC finds all text that contains ABC.
In certain circumstances, brackets may appear in the search requests you are creating. Sage Accounts automatically adds the brackets for you. The rules regarding the use of brackets are as follows:
- If your Join fields only include WHERE and AND, brackets do not appear.
- Brackets appear when OR is used to link expressions and the field chosen before and after the OR are the same.For example, where Account Reference is Equal to A Or Account Reference is Equal to BThe expression appears as Where ( ‘Account Reference’ = ‘A’ Or ‘Account Reference’ = ‘B’)
- If you are using both AND and OR in your expression, brackets appear if the OR is considered to be a separate filter.For example, if you wanted to search for all suppliers with a balance less than 500 who have settlement due days more than 20, and accounts where the settlement due days is equal to 30 you could set up the following Search request:Where (‘ Settlement Due Days ‘ > ‘ 20’ AND ‘Balance'< ‘500’) or ‘settlement due days’ = ’30’
The Search facility assumes that the first two statements are related, and therefore brackets them.