Advanced Queries

You can also add extra fields into a query to provide or search for extra information using built in functions. This is what we refer to as a "calculated field" on the third assignment. To do this, you must first save a query and go to design view.

First, you should select which fields you want the query to include/show. Then, right click on one of the blank fields and select the "build" option.

1. The name the field will be. If left empty, it will be automatically named expr1. To make the name, choose your name and end it with a full colon.


2. Then put the function you want to use. Put in the function name, and then the parameters for the function in parenthesis. Remember that if there is a field name, it must be in square brackets

Lastly, hit ok, make sure that the field you created has the "show" box checked off, and click "run" in the top left of the design menu. This will bring you to the datasheet view, where you will see the new column you created and the result of your function.

Combining/Nesting IIF and MID

When you are using functions in your database, you may need to combine two of them at once, which is called nesting functions. For example, on the assignment you may want to nest the MID function in an IIF function.

1. The IIF function is a true or false test. You check a condition, state what happens if it's true, and then what happens when its false.
   IIF(condition, value_if_true, value_if_false)
2. The MID function takes a chunk of a field, called a substring, and presents it by itself. You might use this to check a set of letters or numbers for a pattern within a field, such as checking if a digit is a valid number for a postal code
   MID([Field Name], start_point, length_of_substring)