Logical Functions and user defined fields in an Access query

It is simple to define your own fields in a query and this allows you to adapt the data taken from your underlying tables. These new fields can incorporate a large number of built in functions which let you manipulate values. Many of the functions have the same name and arguments as in Excel, but there are some peculiar differences and care must be taken.

The functions covered in this section are:

Creating a new user defined field in a query

To add a user defined field, simply write your chosen name for the field (e.g. BranchCode) followed by a colon and then the definition of the field. The field definition may be

• a permanent value (col 1 below)
• a parameter provided by the user at run-time (col 2 below)
• a calculation using functions or mathematical operators (i.e. + - * /) to modify a value (col 3 below)

2	Bookings enhanced - Select query (design)		ox
Field:	BranchCode: "SE450"	MinBkgRef: [What is the first booking reference?]	KM_Journey: [km_return]-[km_start]
Table:
Sort:
Show:	Y	Y	Y
Criteria:

Note that field names used within the definition of new fields (including parameters entered by the user at run-time) are enclosed in square brackets.

A new user defined field can subsequently be used in the same way as a field taken from a table or query record-source (although its values cannot be edited at run time). It is available for use in any form, report or another query using this query as a record-source.

Define Query Parameters
	Parameter	Data type
	Month number	Integer
	Date to	Date/Time
	Organisation (optional)	Text

Run Time Query Parameters

Run time parameters are values which the user will be prompted to provide when the query is run. A small dialog box will appear with a prompt message such as 'What is the first booking reference?'. This prompt then becomes the field name which can be used (enclosed in square brackets) within a query. These parameters should be defined using the ribbon option Query Tools Design | Show/Hide | Parameters although Access will assume that any unrecognised field name is a (text data type) run time parameter, even if it hasn't been defined. The advantage of properly defining parameters is that it lets you specify the data type.

Bookings Selection1 - Select query (design)				ox
Field:	BOOKING_REF	USERGROUP_NAME	DATE_OUT	InvMonth: Month([INVOICE_DATE])
Table:	Bookings	Bookings	Bookings
Sort:	Ascending
Show:	Y	Y	Y	Y
Criteria:		Like [Organisation (Optional)] & '*'	<=[Date to]	[Month number]

	more...			more ...

The three parameter variables have all been used as criteria to select the required records. Note that the organisation criteria uses wildcards.

IIF() and CHOOSE() functions

The main logical function in Access is called IIF() - i.e. immediate IF. It is probably more commonly known in other programs as the IF function . Apart from the peculiarity of the extra 'i', it is the same as the function in Excel. It allows you to return one or other of two values, depending on whether a condition is true and it takes the form: =IIF(statement to check, result if true, result if false)

If you require more than two options (true or false) you can either combine more than one IIF() function or alternatively use the CHOOSE() function. CHOOSE() returns an item from a list of alternatives, dependent on an initial numeric value (the option number) that specifies which of the values is returned. It takes the form: =CHOOSE(option number, Value1, Value2, Value3 ... Value29). The values can be field names, numbers or text.