MeadInKent
| Feedback | Things to do with an Access Query | Run Time Parameters |

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:

IIF() and CHOOSE() Logical functions returning alternative results

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

2 Bookings enhanced - Select query (design) ox
  source
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.

2 Bookings enhanced - Select query (results)
  BranchCode MinBkgRef KM_Journey
SE450 2046 168
SE450 2046 78
SE450 2046 93

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  
  btn btn  

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
  source  
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]
 

 
UK more...   USA more ...

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


Bookings Selection1: Select query ox
BOOKING_REF USERGROUP_NAME DATE_OUT  InvMonth
2436 CAPED CRUSADERS CLUB 16 Jul 05 7
2437 CHELSFIELD GIRL GUIDES 19 Jul 05 7
2438 ST MARTINS CHURCH CHOIR 28 Jul 05 7
 

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)

Examples of user defined fields using IIF Description of purpose
Discount: IIf([km_return]-[km_start]>200,0.75,1) If the distance travelled exceeds 200Km, a discounted rate of 75% will be returned, otherwise  the normal rate (100%) will be charged.
SummerPremium: IIf(Month([date_return])=8,1.25,1) This uses the Month function to determine the month number of the end of booking date. If it is 8 (i.e. August), a premium of 125% is applied.
Settled: IIf(IsNull([date_invpaid]),Date()-[invoice_date] & " days OD",[date_invpaid]) This uses the IsNull function to determine whether a field is empty. If there is no paid date value it will show the number of days overdue, otherwise it will show the date the invoice was paid.
DateString: [date_out] & IIf([date_return]>[date_out]," to " & [date_return],"") The start date is shown and only if the end date is greater than the start date will that also be displayed (e.g. 01/04/2005 to 08/04/2005)

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.

Examples of user defined fields using CHOOSE Description of purpose
Season: Choose(Month([date_out]),"Winter","Winter", "Winter","Spring","Spring","Summer","Summer", "Summer","Summer","Autumn","Autumn","Winter") This uses the Month function to determine the month number of the booking date and then returns a season name.
MySort: Choose([Sort by 1 Date out 2 Date paid 3 Group name],
[date_out],[date_invpaid],[usergroup_name])
This takes a value from a user parameter prompt (type=integer) to select a field by which the list will be sorted.


file: acciifchoose.htm Page last modified Mar14 MeadInKent.co.uk 2014