• 512-991-7197
  • This email address is being protected from spambots. You need JavaScript enabled to view it.
  • Always Open

Access

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive

When storing personal data, it is normal practice to record a person's date of birth rather than their age. This is for the simple reason that the date of birth never changes, but the age will be different next year. If you need to show someone's age you should ask Access to calculate it for you.

Calculating someone's age from their birthday is not quite as simple as it might at first seem! Of course, you take the current year and subtract it from the year in which they were born. This will give you an accurate age in years - providing they have had their birthday this year. So, to be absolutely certain you need to decide if the day and month of their birthday has passed yet.

If it has you subtract their birth year from the current year. If not, then you subtract their birth year from the current year, and then subtract 1 from the answer. A simple calculation we do in our heads without even thinking about it, but quite a task to explain to Access. To get Access to do it this way requires a number of conditional statements, resulting in a very complicated formula! (Conditional statements are easier in Excel that in Access. Take a look at the example in the tutorial Working Out a Person's Age in Excel).

Fortunately, there are a couple of easier ways. Providing you understand the results they provide and use them accordingly, they should suit most purposes. Both examples below take birth dates from a field called DoB and calculate an age, which is displayed in a new field called Age.


To Calculate Approximate Age

Use this expression...   Age: Year(Now( ))-Year([DoB])

acctut05a

acctut05b

Age: creates a new field called Age
Year(Now( )) calculates the year number from the current year
Year([DoB]) calculates the year number from the date it finds in the [DoB] field

The expression subtracts the person's birth year from the current year to give an approximate age, but does not take account of whether or not thy have had their birthday. It will tell you how old they will be this year.


To Calculate Accurate Age

Use this expression...   Age: (Date()-([DoB])/365.25

 It makes use of the fact that Access uses serial numbers to store dates

acctut05c

acctut05d

   Date( ) calculates today's date

The expression subtracts the person's birth date serial number (which it finds in the field [DoB]) from today's serial number, leaving the number of days in between. It then divides that number by 365.25 (being the average number of days in a year) to give their age in years.

In this example the field has been formatted to show two decimal places.


Applying the Technique

You can use these expressions to calculate someone's age on a particular date, and refine the query by adding criteria to display only the records for people whose ages fall within a particular range

acctut05e

acctut05f This example creates a new field called Age and calculates the person's age on a particular date, which it finds in a field called Chosen Date. It then uses the expression <35 in the criteria row to display only the results that calculate to less than 35.

The result is a list of people who were under 35 years of age on the date in question.

If you want to calculate people's ages on a particular date, providing that it is the same date for everyone, you don't need to retrieve that date from a field. Simply include it in the expression…

Age: (#01/11/65#-[DoB])/365.25 would create a new field called Age and calculate how old the people were on 1st November 1965. Note: If the person's birth date is later than the date used in the expression (i.e. they had not been born by that date) their age would show as a minus number!


Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive

A useful feature of the query is that it can be saved and used again and again, whenever we want to ask the same question. The result we see (the recordset) always reflects the most up-to-date information in the database because what you save is the question, not the answer. You just ask the question again by running the query.

Sometimes we want to ask a question time and time again, but the details (the query's criteria) may vary. It would be handy to have a way to run a query and make changes to its criteria without having to design a completely new one each time. Access has a tool to solve that problem, the Parameter Query. In fact, the parameter query can be any sort of query. You just employ the methods described here to specify the criteria.

When you run a parameter query Access presents you with a dialog box prompting you for the parameter value, which it enters into the appropriate criteria cell. You can have as many parameters as you like in a single query. Here's how it's done.

Entering a Parameter

Instead of typing a value or expression into the criteria cell, type some text enclosed in square brackets ([ ]). The text you type will appear as a prompt on a dialog box, so you might want it to be in the form of a question to the user. In this example (Fig. 1) the user will be prompted to type the name of the Office when they run the query. The text that the user types will be used as the criteria for that particular field. The dialog box looks like this…

acctut01a

Fig. 1 When you enter a parameter into a query it asks for the criteria when the query is run.

If the user were to type London then this query would display all the records with the entry London in the Office field. The query simply substitutes the text the user enters into the parameter dialog for the parameter you entered into that field's criteria row. Parameters can be used for text, numbers or dates and can be used alone or combined with normally entered criteria.

Using Multiple Parameters

You can enter a parameter almost anywhere you would place a piece of text, number or date in a regular criteria expression. For example, supposing you wanted the query to prompt the user for two dates to define a date range. Instead of typing the actual beginning and end dates into the criteria cell, type a prompt in square brackets. The user will see two separate dialog boxes, each asking for a date (Fig. 2). After receiving the dates the query proceeds, inserting the dates into the appropriate places in the criteria expression.

In this example the query would display all the records which contained dates in the range 1 July 2005 to 31 December 2005 in the HireDate field...

acctut01b

Fig. 2 Access displays a separate dialog box for each parameter used.

You can use as many parameters as you want, in as many fields as necessary. The dialog boxes appear in the same order as they do on the QBE grid.

Combining Parameters with Wildcards

A useful feature of the query is its ability to accept wildcards (i.e. an asterisk "*" representing any string of characters; one or more question marks "?", each representing a single character). Wildcards allow you a degree of flexibility when specifying criteria. When you don't know exactly what you are looking for you can use wildcards to give the query a "clue". This method can also be applied to parameter queries, but you need to do a bit more than just add an asterisk or question mark. The correct syntax is as follows…

For a single wildcard: 

Like [type prompt here] & "*"

For two wildcards: 

Like "*" & [type prompt here] & "*"

When using a single wildcard it can be placed before or after the prompt. You can use asterisks or question marks, or a combination of both.

Using a Single Wildcard

In this example a single wildcard has been used, an asterisk (Fig. 3).

acctut01c

Fig. 3 A parameter employing a single wildcard.

The parameter...

Like [Which Last Name] & "*"

... creates a prompt in which the user can enter the first letter or string of letters of the names they want to see. The user has entered the text "gr" causing the query to select records with entries in the LastName field of any length starting with the letters "gr".

Using Two Wildcards

In this example (Fig. 4) two wildcards have been used, both asterisks.

acctut01d

Fig. 4 A parameter employing two wildcards.

The parameter...

Like "*" & [Which Last Name] & "*"

...creates a prompt in which the user can enter a letter or string of letter that should occur anywhere in the names they want to see. The user has entered the text "en", causing the query to select records with entries in the LastNamefield of any length containing with the letters "en" together.

Being Creative with Parameters

You can enter a parameter almost anywhere you would normally enter a specific piece of data in your query criteria. Sometimes the syntax (how you write it out) can be a bit tricky, but persevere until you get the result you need.

Here are a few examples...

Finding records for a specific year (or month) from a collection of dates

Supposing you have a field called BirthDate containing a range of dates covering several years. Use the following criteria...

Year([BirthDate])=[Enter a year]

...will create a prompt in which the user can type a year number (e.g. 2005) to see all the records for people born in that year. I you would rather see records for specific months use...

Month([BirthDate])=[Enter a month from 1-12])

Note that the prompt tells the user to enter a number for the month. Access doesn't understand month names.

Finding records for a specific month and year from a collection of dates

If you want to be more specific and call for a particular month and year, the criteria...

Month([BirthDate])=[Enter a month from 1-12] And Year([BirthDate])=[Enter a year] 

...will present the user with two dialog boxes, the first asking for a month and the second asking for a year.

Creating a list of records with dates in the last so many days

You may want to view all the invoices generated in a recent period, such as the last 30 days. The criteria...

 >Date()-[The last how many days?]

...means "today minus how many days". The user enters a number (e.g. 30) to see a list of dates since that many days before today. The Date() part creates the current date so this query is always up-to-date.

What about variable calculations?

You can even include parameters as part of the definition of a new calculated field. (If you want to learn about calculating in Access check out the tutorial Calculating in Access Queries)

For example, you have a list of invoices in which there is a field called TotalGoods and you need to calculate the discount (or tax or whatever!), but this changes from time to time. You need to create a new calculated field to work out the new figures. Instead of...

Discount: [TotalGoods]*25/100

...which would always calculate a discount of 25% (note: unlike Excel, Access doesn't understand the % sign as a mathematical operator). You could substitute the fixed figure with a parameter...

Discount: [TotalGoods]*[What discount rate - percent]/100

...which would prompt the user to enter a figure representing the required discount.

NOTE: When you run a query that uses a combination of parameters and calculations or functions for the first time, Access will often re-arrange what you have written in the QBE (Query By Example) grid of the query design window. This is so that it can compose the query's SQL correctly. The QBE grid is merely a graphic representation of the SQL language that actually powers the query (check it out in the query's SQL view). You can, of course, compose your criteria in this fashion yourself if you wish. The examples I have given here are simply an easier way for you to enter the parameter criteria.

Asking the Questions in the Right Order

When you create a query using more than one parameter, the user sees the prompts in the order that the fields are arranged in the design view of the query, reading from left to right. You normally arrange the fields in the way in which you want to see the results displayed. But what if you want the prompts to appear in a different order? Get to know the Query Parameters Window.

Using the Query Parameters Window

To control the order in which the prompts appear when running a parameter query containing more than one parameter, you can specify the desired order in the Query Parameters window. Here's how...

  1. In the query design view choose right-click on the upper part of the query design screen and choose Parameters... from the context menu to open the Query Parameters window.

  2. In the Parameter column, type the prompt for each parameter exactly as it was typed in the QBE grid.

  3. In the Data Type column specify the kind of data (as defined in the table properties). Pick a type from the list. The default type is Short Text.

  4. List the parameters in the order in which you want the dialog boxes to appear when the user runs the query.

Click OK to accept your entries and close the window.

Here is an example of a query containing two parameters (Fig. 5).

acctut01e

Fig. 5 Defining the order in which the parameter prompts appear.

If you didn't specify otherwise, the prompts would appear in the order that the parameters are arranged in the QBE grid reading from left to right. The user would be asked for an Office first and then a Department. If, however, you make use of the Query Parameters window you can choose the order of the prompts. In this example (Fig. 5) the parameters have been arranged in a different order so that the user is asked for a Department first and then an Office.

There is no need to make entries in the Query Parameters window if you are happy with the way the query runs, unless you are creating a Crosstab Query containing parameters, in which case you must enter the details of the parameters to make the query run correctly.

What if the User Doesn't Enter Anything?

There is always the possibility that the user will dismiss the parameter dialog without making an entry, either because they don't know what they can type or because they want to see everything. 

What if the user Doesn't Know What to Type?

It's possible that your users won't know all the entries they can make. I'm often asked how to make things easier for them by offering a list from which they can simply choose an item. Take a look at my tutorial on Customizing Access Parameter Queries to find out how to do this. It shows you how to build your own custom parameter dialog boxes powered by VBA code. You don't need any prior knowledge of programming. The tutorial explains everything step-by-step and it's a great introduction to programming your databases with VBA.

Dos and Don'ts for Parameter Queries

Prompts must not match field names

When you are designing a parameter query, make sure that the prompt is not exactly the same as one of the field names. You might be tempted to enter the parameter [LastName] to prompt the user to enter a name into the dialog box for the LastName field. This won't do! Access uses field names in square brackets for calculations in queries so your entry will not be recognised as a parameter and will not appear as a prompt. If you really want to use the name of the field as a prompt, a simple solution is to turn it into a question by adding a question mark e.g. [LastName?].

Don't use illegal characters

You can type just about anything for the prompt, but you mustn't use the period (.) exclamation mark (!) square brackets ([]) or the ampersand (&). Everything else is OK.

Don't write too much!

You are only allowed one line of text in the prompt dialog box, which amounts to about 40 to 50 characters depending on what you say. Anything extra just gets cut off. Check your work!


Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive

An often-overlooked feature of the ordinary select query is its ability to calculate totals. Whilst the crosstab query is useful for analysing complex data, it needs several fields to work with. Supposing the data you wish to analyse is contained in just two fields? The answer is to create a select query and make use of the totals option.

Totals can do more than just add up too! Here's how it works…

We have a table containing a mass of sales data. The records are arranged in no particular order, and there are several fields containing information about each individual sale…

acctut04a

If we wanted, we could do a sophisticated analysis of this data with a crosstab query, but to begin with all we want to know is "What are the total sales for each person?" All the data we need is contained in just two fields, Salesperson and Sales, and a crosstab query needs at least three fields to work with. No problem! We're going to use Totals.

Adding Totals to a Query

The first step is to create a query selecting the fields we are interested in. Running the query at this stage simply presents with a list of data. In this case Salespeople and numbers representing individual Sales

acctut04b  acctut04c

But we need to analyse this data…

acctut04dIn the design view of the query we activate the totals option. To do this either click the Totals button on the toolbar, or choose View > Totals from the menu, or right-click anywhere in the QBE grid and choose Totals from the shortcut menu.

acctut04e 

acctut04f

Activating the Totals option creates another row in the QBE grid, the Total: row.

The default setting for the Total option for each field is Group By, but to make sense out of this data we must change one of these and select a type of calculation. In this example we want to calculate the sum of Sales for each Salesperson

Clicking in the Total cell of the Sales column, then clicking the down-arrow that appears, reveals a list of choices. Sum is the one we need here, but you can see that several useful calculations can be chosen, mathematical and statistical.

Click the appropriate option to select it and run the query.

acctut04g

The result of the query shows a total sales figure for each salesperson. The Salesperson field is automatically sorted into alphabetical order so there's no need to choose a sort option in query design. If you wish you can override the A-Z sort order and choose Descending if you want the results sorted the opposite way.

^ top


Refining the Query

You can still add criteria to the query definition if you wish. Here, criteria have been added to the Salesperson field to select records for specific salespeople…

acctut04h  acctut04i


Grouping by Several Fields

You can group my as many fields as you wish, providing the chosen fields contain suitable data.

acctut04j

acctut04k Here, the data has been grouped additionally by region. Again the data is sorted automatically…

 


Using Additional Criteria

It may be that you want to specify certain criteria referring to another field, but you don't want to group by that field or calculate its data. To do this, select the Where option in the Totals row. This lets you add criteria to the criteria row of that field to further refine your query. When you do this Access unchecks the Show box for that field. If you want to see the new field in the result of your query, you must add to the QBE grid a second time (with Grouped By selected in its Totals row). Construct the query like this…

acctut04l

The Region field has been added to the QBE grid twice, first using the Where option to specify criteria, and again using Group By to let the result be shown.

acctut04m Here's the result. The data has been grouped by Salesperson and Sales subtotalled (summed) for the North region only.

Note: Because what you see when you run your query using Totals is a summary of your data, it doesn't behave like a normal dynaset. Changes made to the data here are not reflected in the original data.


Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive

When constructing a query or a filter, you need to tell Access what to look for in each field. You do this by defining criteria - typing something (an "expression") into the Criteria cell of the query or filter grid. If you do not define any criteria for a particular field, Access assumes that you are applying no constraints and will display everything it has. This means that you only have to define criteria for those fields you are interested in.

Here are some examples of the more common types of criteria. Often Access will complete the expression so that you need only type the text you want to match. However, sometimes Access has a choice so you should always check that what Access has written is the same as you intended. If what you type doesn't make sense to Access, you will see an error message.

The list of examples below is not exhaustive. Try using combinations of different expressions and see what you get. Also, don't immediately assume that you have made a mistake if you get no records when you run the query or filter. It means that Access can't find anything to match your criteria. That may be because you've asked for something impossible, but it could equally mean that your criteria were perfectly OK but there simply aren't any matching records.

Matching Text

When you enter text into the criteria cell your text should be enclosed in quotes ("") to distinguish it from other expressions and operators that you may need to add.

acctut06a"Text"
To match a word or phrase
 simply type the text you want to match. The query will find all the records that match the text exactly. Access will add the quote marks at each end. It is only necessary to enter the quotes yourself if you type text that might confuse the query. For example you may want to type a phrase that contains the words "and" or "or". Access would normally interpret these words as instructions. You can manually insert the quote marks at each end of the phrase to make sure the criterion means what you intend it to. This example will display all the records that contain the entry London in the Town field.

acctut06b"Text" Or "Text"
To match one of two or more words or phrases, type the text you want to match separated by the word "or". The query will find all the records that match any of the words or phrases. Enter quote marks yourself if you think the text might confuse the query. This example will display all the records that contain either London or Paris in the Town field.
 

 

acctut06c"Text"
"Text"

To match one of several words or phrases, you can type each word or phrase in a new row moving down the column. This gives the same result as using "or" but has the advantage that your criteria might be easier to read. This example will display all the records that contain the entry LondonParis or Amsterdam in the Town field. Note: If this method is combined with criteria for other fields those criteria must be repeated for each row.

acctut06dIn ("Text", "Text", "Text"…)
To match a word or phrase from a list, type the list items separated by commas, and enclose the list in round brackets (parentheses). Access will add the expression "In" and place quote marks where needed - you can do this manually if you wish. This example will display all the records that contain UK or USA or France in the Countryfield.

 

acctut06eNot "Text"
To exclude a word or phrase, use the expression "Not" followed by the word of phrase you want to exclude (enclosed in quotes). This example will display records that contain anything other than London in the Town field.

 

acctut06fNot In ("Text", "Text", "Text"…)
To exclude a list of words or phrases from the search use the same method as for matching from a list but add the expression "Not" at the beginning. This example will display all records that contain anything other than UK or USA or France in the Country field.

 


Using Wildcards

wildcard is a special character that can stand for either a single character or a string of text. Wildcards are useful when you want the query to look for a range of different possible values, and also when you are not certain exactly what you are looking for but can give the query some clues to work with.

The two wildcards we commonly use are the asterisk or star (*) and the question mark (?).The asterisk (*) represents any string of text from nothing up to an entire paragraph or more. The question mark (?) represents a single character only (although you could use, for example, two question marks to represent two unknown characters).

For example:

  • Yor* would find YorkYorkshire and Yorktown but not New York.
  • Mar? would find Mark but not MarioMartin or Omar
  • F*d would find Fred and Ferdinand but not Frederick.

acctut06gLike "Text*"
To match text starting with a particular letter or string type the letter or string of text followed by an asterisk. Access will add the expression "Like" and place quotes around your typing. This example will display all records that have an entry starting with S in the Company field.

 

acctut06hLike "*Text"
To match text ending with a particular letter or string type an asterisk followed by a letter or string of text. This example will display all records that have an entry ending with Plc in the Company field.

 

 

acctut06iLike "[Letter-Letter]*"
To match text starting with letters within a certain range you must type the entire expression as shown (this one is too complicated for Access to work out what you want. This example will display all the records with entries starting with the letters A - D in the Company field.

You can often get the same results by using mathematical operators such as greater than (>) and less than (<). These are normally used for specifying numbers and dates but can also be used for text.

For example: 

  • <"N" would find all entries beginning with a letter lower than the letter N in the alphabet. In other words, all entries starting with the letters A - M.
  • >"F" And <"H" would find all entries beginning with the letters F and G.

Working with Numbers

When working with numbers we normally use the mathematical operators to define the range of numbers from which we want to select.

For example, where X represents a number: 

  • <X finds values less than X. 
  • >X finds vales greater than X 
  • >=X finds values greater than or equal to X 
  • <>X finds vales not equal to X

It is important that your field type is correctly defined as a Number field for numerical queries to work properly. Here are some examples…

acctut06jX
To match a number simply type the number that you want the query to find. This example will display the record(s) with the entry 385 in the CustomerNumber field.

 
 

acctut06k<X
To find values less than a certain number type a less than sign (<) followed by the number. This example will display all records with an entry less than 1000 in the CustomerNumberfield.

 

 

acctut06lBetween X And Y
To find values in a range of numbers type the expression shown where X and Y represent the numbers at opposite ends of the range. This example will display all records with entries falling within the range 500-700 in the CustomerNumber field.

 


Working with Dates

Dates behave the same way as numbers, so you can use some of the same techniques when constructing your date query or filter. Remember, for dates to be treated properly by Access it is important that your field type has been correctly defined as a Date/Time field. It doesn't matter how you enter the date, as long as you use a recognised format. The date will be displayed in the resulting dynaset in whatever format you chose when you created the table.

When you enter a date in the criteria cell you can use any standard date format, but each date must be enclosed by hash marks (#).

For example: 

  • <#1/1/98# finds dates earlier than 1 January 1998 
  • =#27-Sep-50# finds dates equal to 27 September 1950 
  • Between #5/7/98# And #10/7/98# finds dates no earlier than 5 July 1998 and no later than 10 July 1998

Here are some more examples…

acctut06m=#Date#
To match a particular date type the date enclosed by hash marks (#). This example will display all the records with entries for 27 September 1998 in the Invoice Date field.

 
 

acctut06n=Date()
To match today's date type the expression shown. Date() means "today". This example will display all the records with entries for the current date in the Invoice Date field.

 
 

acctut06oYear([Fieldname])=Year(Now())
To match the current year type the expression shown, entering the name of the current field in square brackets where indicated. This example will display all the records with entries for the current year in the Invoice Date field.

 

acctut06pYear([Fieldname])=Year
To match a particular year type the expression shown, entering the name of the current field in square brackets where indicated and the required year in place of Year. This example will display all the records with a date in 1998 in the Invoice Date field.

 

acctut06q<Date()-30
To match a particular calculated date range you will need to use a combination of expressions. This expression employs a calculation that subtracts 30 from the current date and also includes the less than operator. This example will display all the records with a date more than 30 days old in the Invoice Date field.

 


Excluding Things

Sometimes you want to specifically exclude criteria from your search. This is done with the expression Not. This expression can be used on its own or in combination with other expressions.

For example: 

  • Not "text" finds all records except those matching the specified text. 
  • Not Like "X*" finds all records except those starting with the specified letter (or string of text).

Here are some more examples:

acctut06e 1Not "Text"
To exclude specific records from the search use the expression Not followed by the text which matches those records you want left out. The text needs to be between quotes as shown here - Access will normally do that for you. This example will find all records for contacts in towns other than London.

 

acctut06rNot Like "Text*"
You can use wildcards with the Not expression, which then becomes Not Like followed by your wildcard criteria. Here is just one example. This example will find all records for contacts in towns starting will letters other than L.
 

acctut06sAnd Not "Text"
The Not expression can be used in combination with other expressions, when it becomes And Not followed by the text you want to exclude from your search. This example will find all records for contacts in towns starting with the letter L but will exclude those in London.

 


Finding Empty Fields

A query can be used to find records where specific fields are empty. To do this you use the expression Is Null. Conversely, to find records for which specific fields are not empty you use the expression Is Not Null. The expression Null simply means "nothing".

If you have made use of the "allow zero length" field property you can search for zero length entries. Sometimes you want to distinguish between, for example, records for which you don't happen to have the particular piece of information for a certain field and those for which you know there definitely isn't any information available. Is the Fax Number field empty because you don't know the person's fax number or is it because they don't have a fax? Either way you can't type a fax number into the field. It has to be left empty. Well, not exactly…

You can make a "zero length entry" (providing this feature has been enabled in the properties of the field - in the table's design view). To do this when entering data type two double-quote marks together without a space between, like this… "". When you leave the field the quote marks disappear and the field looks just like any other empty field - except Access knows it contains a zero length entry. You can search for zero length entries with a query. It is important to remember that if you make use of zero length entries, Is Null will not find them. It regards them as a piece of text and therefore a field containing a zero length entry is not empty, it just doesn't contain any data. Confused? Read it again then try it out - it does make sense eventually!

Here are some examples:

acctut06tIs Null
To find empty fields use the Is Null expression. This looks for fields that contain no data. This example will find all records for contacts whose fax number has not been recorded.

 
 

acctut06uIs Not Null
To find fields that are not empty use the Is Not Null expression. This looks for fields that contain data. If there is something in the field the record will be shown. Note that Is Not Null will find fields containing zero length entries. (If you want to leave them out try excluding them with the And Notexpression.) This example finds all records for contacts whose fax number has been recorded.

 

acctut06v""
To find zero length entries use "" expression. This looks for zero length entries in the specified field. This example would find, depending on why you had made use of the zero length entry feature, all records for contacts who did not have a fax.


Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive

Simple Date Calculations

When you enter a date into an Access table, Access recognises it as a date, and checks it against the calendar to make sure it is a possible date. You'll get an error message if you try to enter an impossible date such 31st September or 29th February in a non leap year. Then it stores the date as a number known as the date serial. PCs use the 1900 System to store dates. 1st January 1900 was day 1, 2nd January 1900 was day 2 etc. You don't need to know the serial number of your dates, but you can make use of it in mathematical calculations.

Here are a few examples…

To add or subtract days from a date...

acctut03a Create a new field with an expression that adds (+) or subtracts (-) the required number from the field containing the original date. For example…

Due Date: [Invoice Date]+60

acctut03b Due Date: creates a new field called Due Date

[Invoice Date]+60 takes the date it finds in the Invoice Date field and adds 60 to its serial number.

The result is automatically displayed as a date.

To calculate the number of days between two dates

Create a new field with an expression that subtracts the field containing the earlier date from the field containing the later date. For example…

Stay: [Departure Date]-[Arrival Date]

acctut03c

acctut03d Stay: creates a new field called Stay

[Departure Date]-[Arrival Date] subtracts the date found in the field Arrival Date from the date found in the field Departure Date.

The result is automatically displayed as a number.*


Note: If the result fails to display as a date, or displays a date in the wrong format, switch to design view and click in the new field column.

acctut03eOn the menu choose View > Properties (or right-click the field and choose Properties from the shortcut menu). Click in the Format section on the General tab of the Field Properties dialog box. Click the down-arrow and choose an appropriate format from the list. Close the dialog box and run the query again to see your dates displayed correctly. 


Using Date Functions

There are four basic date functions which extract part of a date so that it may be displayed on its own, in a new field (further refined with criteria id required), or with additional information as part of the criteria of the field in which the date itself occurs. These functions are…

  • Year([Fieldname]) returns the year from a date e.g. 20/8/99 would return 1999
  • Month([Fieldname]) returns the month from a date e.g. 20/8/99 would return 8
  • Day([Fieldname]) returns the day of the month from a date e.g. 20/8/99 would return 20
  • Weekday([Fieldname]) returns the day of the week form a date e.g. 20/8/99 would return 6 representing Friday. The weekdays numbering from 1 to 7 starting with Sunday.

Use these functions in a new field if you want to display the extracted data in addition to the original date. Remember to type the name of the new field first followed by a colon (:). When you do this you can further refine the query by entering criteria in the new field's criteria row…

acctut03f Year( )

This function is used to extract the year from particular date.

In this example, the function is used simply to display the year in a new field…

acctut03g

Year : Year([Date]) creates a new field called Year containing data calculated from the field [Date].

If no criteria are defined then all the records are displayed. The usual criteria for defining numbers can be used to display specific years or ranges. For example…

acctut03h 

acctut03i

1996 displays records for dates in the year 1996 only.

1996 Or 1997 displays records for dates in 1996 or 1997.

>1997 displays records for dates from 1998 onwards.

Between 1996 and 1999 displays records for dates in the years 1996, 1997, 1998 and 1999.

If you don't need to see the extracted data separately, you can enter the function as part of the criteria of the original date field...

acctut03j

acctut03k

The Year( ) function does not have to be used in a separate field. It can form part of the criteria definition. For example…

Year([Date])=1997 displays records for dates in 1997

Year([Date])>1995 displays records for dates from 1995 onwards.

Remember that you still have to include the name of the field (in this case the field is called [Date]) within the function, even though the criteria are typed in that field's column.

The same applies to the Month( )Day( ) and Weekday( ) functions. For example…

acctut03l

acctut03m

Month([Date])=9 displays records for dates in September

Month([Date]) Between 4 and 8 displays records for dates in April, May, June, July and August

Day([Date])=15 displays dates which are the 15th day of the month.

Weekday([Date])=4 displays dates which are a Wednesday.

 


Advanced Date Functions

Access contains a number of more sophisticated date functions for when you can't get the result you need using a simple calculation or one of the basic date functions. Here's an example...

DateAdd(interval, number, [Fieldname])

Use this function to add (or subtract) a specific amount of time to a date. The interval part of the function refers to the type of time unit you want to add and requires you to enter a code… 

  • yyyy for year 

  • q for quarter (i.e. 3 months) 

  • m for month 

  • d for day 

  • ww for week

The number part of the function refers to how many of those units you want to add. You can use a minus number if you want to subtract from the date. Finally you need to supply the name of the field containing the original date.

Adding or subtracting days or weeks can be performed with simple mathematical calculations as demonstrated above. The DateAdd function makes it easy to add years, months or quarters to a date. For example…

acctut03n

acctut03o Date Due: DateAdd("m",2,[Date])

This expression creates a new field called Date Due into which it enters dates from the Date field to which it adds 2 calendar months.

This query could be further refined to display records whose calculated dates fall in a specific range by entering criteria in the new field's criteria row. For example, if these were due dates for invoices where the payment terms were one calendar month, entering <#15/8/99# in the criteria would display only those invoices for which payment was due before 15th August 1999.


Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive

Everyone likes parameter queries! The database developer doesn't have to anticipate the user's every requirement, and the user can vary their enquiries without having to get involved with query design. But there's one question I'm always asked by my Access students. Can you customize the parameter input dialog?

The basic input dialog box that appears when you run a parameter query simply asks the user for some text input. Here is a standard parameter input dialog (Fig. 1).

acctut08a

Fig. 1 A standard parameter dialog box.

It serves its purpose, but there is room for improvement. The title "Enter Parameter Value" is guaranteed to frighten the life out of a new user! Perhaps it could say something a little more friendly. Let's assume they've decided to continue and type something. What are they allowed to type? Perhaps they can't remember the names of all the offices or they might make a typo. Couldn't we add a combo box with a list of choices? The query might require several separate dialogs for different fields. Wouldn't it be useful if they could be combined into a single friendly dialog box.

So, can you customize the parameter input dialog? No, you can't. But you can do something much better. You can build a completely new one. In fact with this method you don't even build a parameter query. You build a regular query that takes its criteria from a special form. You have three things to do...

  1. Build a dialog box with as many combo boxes as you need.

  2. Design a query to read its criteria from the information on the dialog box.

  3. Create a macro or visual basic procedure to tell them both what to do.

This tutorial explains all of these steps in detail so, if you are already an experienced user, you might want to skip straight to the bits that interest you.

Finally, there is a section illustrating some useful variations on this technique such as allowing for Null entries (when the user leaves the combo box empty).

I'm using a sample database containing details of the employees of a company that has offices in a number of different cities. Each office has several different departments. I'm going to build a query that enables the user so view an employee list selected by Office and Department.

Step 1: Build the Dialog Box

You are going to build a dialog box to replace the one that the user would normally see when running a parameter query. The dialog box is just a form built using the Access form design tools. It will contain two combo boxes, one for Office and one for Department, and a couple of command buttons to make things happen.

1.1 Create a New Form in Design View

Click the Create tab on the Access ribbon then click the Form Design button to create a new form in Design View. A form design tab opens containing a blank form. You don't need to specify a table or query for the form's data. This is going to be an "unbound" form, one that isn't linked to a data source. Your design window is probably maximized at this point but later you will tell Access to display the form as a dialog box.

Start by dragging the edges of the form to make a rectangle about 7 cm wide by 4 cm tall. It should look something like this (Fig. 2).

acctut08b

Fig. 2 Drag the form's background to the desired size.

Now you are ready to put some objects on the form. Access will have added a collection of Form Design Tools tabs to the ribbon. The tools necessary for this task are all located on the Design tab.

1.2 Prepare the List Data

I'm going to put two combo boxes on the form, one listing the Offices and another listing the Departments. But first, I need something that Access can use to build the lists. If you have ever used the wizard to create a combo box you will know that you have several choices. You can create a table, listing each item you want to appear on the list; you can build a query that makes the list from a data source such as one of the tables already in the database; or you can simply type your list on to the combo's properties sheet. Each method has its own advantages. A query, for example could be self-updating. A typed list would be easiest but fiddly to change later. I've chosen to create a couple of tables. Their data won't change often, but when it does it will be easy to update them. I've called the tables tblDepartmentList (Fig. 3) and tblOfficeList and each has just one field, Department and Office respectively.

acctut08c

Fig. 3 A table is used to populate the combo box list.

TIP: Why the strange names for the tables? Access developers follow certain conventions when naming database objects. This becomes important when you get involved with VBA code where spaces in object names aren't allowed (and it's a pain typing all those underscores), and where prefixes such as "tbl" help to identify what sort of object is being referred to (e.g. tbl = table, qry = query, frm = form).

Once you have created your tables, you are ready to add the combo boxes to the form.

1.3 Add the Combo Boxes

First, switch off the Use Control Wizards option on the Design tab of the ribbon (Fig. 4). This will prevent the wizard from running when you select the Combo Box tool. You are going to do it the "hard" way! If you forget to disable the wizard, don't panic, just click the Cancel button on the first window of the wizard when it appears. 

acctut08d

Fig. 4 Switch off the Control Wizards option.

To add a combo box click the Combo Box button (Fig. 5) on the toolbox then click on the form about 1 cm down and 3 cm in from the left.

acctut08e

Fig. 5 The Combo Box tool.

Access creates an unbound combo box on the form, and a text label to go with it (Fig. 6).

acctut08f  acctut08g

Fig. 6 Use the Combo Box tool to position and add a new combo box.

Click the label to select it. You can press the [Delete] key on your keyboard to delete the label, or click the label a second time to insert your cursor and edit the text to something meaningful. You can resize or move the control (an object on a form such as a combo box is called a "control") or its label by dragging the dots that appear around the edge of the object when it is selected. Watch the cursor to see what will happen when you drag it (Fig. 7).

acctut08h

Fig. 7 The cursor indicates its function when dragging.

TIP: Here's an easy way to re-size a label to fit its text. Just double-click one of the small dots and the label will snap to the correct size.

TIP: It can be really fiddly moving and resizing those labels and controls with the mouse. I prefer to use the keyboard... Select the object then, on the keyboard, use [Arrow] to move or [Shift]+[Arrow] to re-size. Hold the [Control] key down at the same time to fine tune the movements. The up, down, left and right arrow keys all do different things so experiment!

Now you have an "unbound" combo box on your form. The term unbound indicates that it is not connected to any field. It will just display a value that we can make use of later. In this example I need two of these so I'll repeat the process (Fig. 8).

acctut08i

Fig. 8 A second combo box is added to the form.

TIP: If you have an object like a control or command button on your form and you want another exactly the same, select the object then Copy and Paste (don't bother to click anywhere in between) and you've got an exact copy of the original. A quick way to do this is to select the control you want to duplicate then hold down the [Control] key and press C then V. An exact copy of the control will appear directly below it.

1.4 Set the Combo Box Properties

You may be tempted to switch to Form View and check out your combo boxes, but they won't work yet. We have to give them their instructions by setting their properties. Select the combo box whose properties you want to set and click the Properties button (Fig. 9) on the Design tab of the ribbon (or right-click the combo box and choose Properties from the context menu).

acctut08j

Fig. 9 The Properties button.

This opens the Properties pane at the right-hand side of the Form Design window.

The first thing to do is give the combo box a meaningful name (an object's "name" is a different thing from its "label"). Click the "Other" tab on the properties pane. You'll see the name that Access gave it, something like Combo1, but it makes sense to change it to something meaningful so you don't get confused when you have to refer to it in your VBA code, or in a macro or query. I've called mine cboOffice (note the propellerhead naming convention again). 

Next we have to tell the combo box where to get its list. Switch to the "Data" tab of the Properties pane. If you used a table or query to make your list (I used tables) leave Row Source Type set to Table/Query. Then click in the box next to Row Source, click the down-arrow and choose your table or query from the list (Fig. 10).

If you chose to type out your list instead, change the Row Source Type to Value List then, in Row Source, type the items you want to see in your list, separated by semicolons (;). Like this...

"Birmingham"; "London"; "Manchester"; "New York"; "Paris"

The are several more properties that you can change if you wish. I've chosen to set the Limit To List property to Yes - this prevents the user from typing something that isn't on the list. I've also left the Auto Expand setting as Yes - this means that the user can type the first one or two letters of an entry for Access to fill out the full name automatically. Auto Expand is either really useful or profoundly irritating depending on what's in your list, so you can turn it off if you want.

acctut08k

Fig. 10 Set the properties of the combo box.

I haven't bothered to set a Default Value, because I want the user to have the option of leaving the entry blank. I could have set a Default Value of "London" for example (remember to put quote marks around text).

TIP: To make things easy for your users the items in your combo box list should be in alphabetical order. If your list is based directly on a table the order can be disrupted if new items are added. For this reason I usually use a query as the row source, even when working with a simple one-column table, because this allows me to sort the list items so that they always appear in the correct order. There is no need to create a separate stored query for this. Instead of selecting a table as the Row Source click in the Row Source textbox then click the Build button [...] to open the query builder where you can create an embedded query that sorts the table data for you. When you close the query builder it generates a SQL statement for the Row Source property.

Other helpful properties are Status Bar Text and Control Tip Text which you'll find on the "Other" tab. They both offer help for the user and both can accept up to 255 characters of text (make sure your status bar is long enough for what you type!). Status Bar Text appears on the status bar at the bottom of the Access window when the user enters the combo box. Control Tip Text appears in a pop-up box next to the mouse pointer when the user points at the combo box. If you need specific help on any of the properties just click on the appropriate property and press the [F1] key on your keyboard. 

Set the properties for each of the combo boxes on your form.

1.5 Draw the Command Buttons

I'm using two command buttons. One for [OK] that will run the query and close the form when the user clicks it, and one for [Cancel] that will just close the form if the user changes their mind.

Click the Command Button button on the Design tab of the ribbon, then click on the form where you want your button to appear. You may want to resize the button. Do it the same way as for the combo boxes. Create a second button (copy/paste is quick and easy) and arrange them to suit yourself (Fig. 11).

acctut08m  acctut08n

Fig. 11 Use the Command Button tool to add two command buttons to the form.

1.6 Set the Command Button Properties

Give your buttons sensible captions like OK and Cancel. Use the properties window to set their properties as follows...

OK Button: Caption: OK, Name: cmdOK, Default: Yes
Cancel Button: Caption: Cancel, Name: cmdCancel, Cancel: Yes

Setting the Default property to Yes for the OK button makes it the form's default button. As long as the user hasn't selected another button on the form (by tabbing to it for example), pressing the [Enter] key on the keyboard will have the effect of pressing the button. You may not want to set this property if you think the user might press enter by mistake (users do that sort of thing!).

Similarly, setting the Cancel property Yes for the Cancel button means that button will be pressed if the user presses the [Esc] key on the keyboard.

We're going to use the OK button to run the query (so pressing [Enter] after selecting from the combos makes sense), and the Cancel button will close the form without running the query (users expect things to go away if they press the [Esc] key). These properties simply assign actions to the key presses - we still have to create procedures to tell the buttons what to do.

At this point you might like to decorate the background of the form to suit the colour scheme of your database (Fig. 12). To change the background colour, click on the Detail area of the form and change its BackColor property.

acctut08o

Fig. 12 Make the form’s background a suitable colour.

One property we haven't set yet is the On Click event property. I'll return to that later. First we have to tidy up the form and make it look like a proper dialog box...

1.7 Turning the Form into a Dialog Box

To make our form look more professional we need to change some of its properties so that it looks like a proper dialog box. An Access form has several features that a dialog box does not need, like record selectors and navigation buttons. Double-click the Form Selector button (Fig. 13), it's the small rectangle located where the two rulers meet, to select the form itself and display the form properties window. When the form is selected a black dot appears in the centre of the Form Selector.

acctut08p

Fig. 13 Select the form itself.

Go to the Format tab of the Form Properties pane and set the properties as follows:

Caption: [Choose a suitable title], Scrollbars: Neither, Record Selectors: No, Navigation Buttons: No, Dividing Lines: No, Auto Center: Yes [Optional], Border Style: Dialog, Control Box: No [Optional].

Setting the Border Style to Dialog automatically removes the Min/Max buttons from the form. You can also choose to set the Close Button property to No (which disables it rather than removing it), or to set the Control Box property to No(which removes both the Control Box* and the Close Button). I have chosen the latter option in this example but sometimes I prefer not to do this. You must give your user some sort of 'escape route'. They might have arrived at this dialog box by mistake and just want to get rid of it. Here, our custom Close button will let them do that. Users will often close a form or dialog using its own close button (the one in the upper right corner marked with a cross [X]). You may not want them to do this if, for example, the dialog is shown mid-way through a procedure. Unexpectedly closing a dialog might crash your code! So when I want complete control over the user's actions I provide my own close button that I can program events the way I want.

*NOTE: What! You never heard of the Control Box? It's the tiny icon in the upper left corner of almost every window. Click on it with your mouse to get a menu of window options.

Go to the Other tab of the Form Properties pane and set the following property to make sure that the form appears as a dialog and not as a tabbed form or window:

Pop Up: Yes

Take a look at your form dialog box in From View and if necessary resize the detail area so that it is a suitable size for what it shows. You may want to re-arrange the contents so that everything looks good. Here is our (almost) finished dialog box (Fig. 14):

acctut08q

Fig. 14 The form’s design is complete.

It's time to close and save the form as we are about to turn our attention to the query. I have called this form frmEmployeeLocator. You can, of course, call it anything you like.

The next step is to design the query that will make use of the choices that the user makes from the form's combo boxes...

^ top

Step 2: Design the Query

2.1 Create the Query

Select the Create tab of the Access ribbon and click the Query Design button to create a new query in Design View. Build your query as you would normally, choosing the table whose data you want to query and adding the fields you want to see. Don't add any criteria yet. In my example, I'm going to set the criteria for the Office field of my Staff table to the value that the user chose on the cboOffice combo box on my new custom dialog box. I'll do the same for the Department field.

2.2 Set the Field Criteria

We need to tell the query to use as its criteria the values currently showing in the appropriate combo box of the custom dialog box. If you know what to type you can enter the instructions directly into the criteria cell. I find it easier to use the Build tool. Right-click in the first criteria cell of your chosen field (here I'm using the Office field) and choose Build from the context menu (Fig. 15).

acctut08r

Fig. 15 Select the Build tool.

This opens the Expression Builder window. In the lower part of the window there are three columns. In the left column click the [+] next to the name of your database to expand its contents. Then expand Forms and All Forms until you can see the name of the dialog box you just created (mine was named frmEmployeeLocator) and click it.

This displays in the centre column a list of all the objects (controls, labels etc.) on this form. Find the name of the combo box that contains the list appropriate to the field whose criteria you are setting (mine is cboOffice) and double-click it. Its full designation appears in the upper section of the Expression Builder (Fig. 16).

acctut08s

Fig. 16 Select the new dialog in the Expression Buider.

Click the [OK] button to close the Expression Builder and return to the query design window, where you will see that the correct information has been placed into the criteria cell. Repeat this for each of the combo boxes (taking care to apply the correct combo box criteria to each field). The result will look something like this (Fig. 17).

acctut08t

Fig. 17 The query contains a reference to each combo box.

We haven't finished yet, but you might like to try out the query at this point...

2.3 Test the Query

Leave the query window open but go to the Navigation Pane and open your custom dialog box. Make selections from the combo boxes but don't close the dialog box. Now switch back to the query design window and run the query by pressing the Run button on the Query Design tab of the ribbon. If everything has been done correctly, your query will proceed using the criteria you selected in the dialog box and you will see the appropriate result (Fig. 18).

acctut08u

Fig. 18 Test the new dialog box.

If you need to modify the query in any way do it now, then close and save it, giving it a suitable name (mine is called qryEmployeeLocator). We are now ready to create the instructions that will link the query with the dialog box...

Step 3: Create the Code

The final task is to create the instructions that operate when the user clicks the OK or Cancel buttons on the dialog box. You could do this using an Access Macro but I have chosen to use VBA code because this gives me much greater control. It is also an easy introduction to writing VBA code if you haven't done it before. Open your custom dialog box in Design View if it is already open right-click on it and choose Design View from the menu.

3.1 Programming the Cancel Button

The easy one first! The Cancel button needs to be programmed to close the form without running the query when it is clicked by the user. Right-click the Cancel button and choose Properties from the context menu to open the Properties Window. Switch to the Event tab. Here you will see a list of events associated with the Cancel button. We are going to attach a VBA procedure to one of these.

acctut08v

Fig. 19 Create an Event Procedure for the Cancel button.

Click in the box next to On Click, then click the Build button (the one with the [...] symbol) (Fig. 19). Choose Code Builder from the dialog box that appears then click [OK] to open the Visual Basic Editor. The first parts of the code are already in place (Listing 1):

Listing 1:

Private Sub cmdCancel_Click()

End Sub

Place your cursor in the empty line between the Sub and End Sub statements and press [Tab] to indent your code (this makes it easier to read). Type DoCmd followed by a dot. When you type the dot a list of possible entries appears...

acctut08w

Fig. 20 Start writing the code to close the dialog box.

Choose Close from the list (Fig. 20) and type a space. Typing the space both enters the text Close into your code and also enters the space. This prompts the next list of possible entries to open...

acctut08x

Fig. 21 Continue writing the code to close the dialog box.

Choose acForm (Fig. 21) and type a comma (,). Typing the comma enters the text acForm into your code and also enters the comma. Now you can see a panel indicating the various pieces of information (called arguments) that this statement needs (Fig.22). The current one is in bold, telling us that we should enter the form's name next. The remaining arguments are in italic, indicating that they are optional. Type, in quotes, the name that you gave your custom dialog box (mine is called frmStaffLocator).

acctut08y

Fig. 22 The Visual Basic Editor tells you what it requires.

Your finished code module looks like this (Listing 2):

Listing 2:

Private Sub CmdCancel_Click()
   
On Error Resume Next
   DoCmd.Close acForm, "frmStaffLocator"
End Sub

NOTE: You might have noticed that, although not on the screenshots, I have added a basic error handler "On Error Resume Next" to each of my completed code listings. Even the simplest of macros might fail if the unexpected happens, so it is good practice always to add an error handler of some kind. This won't prevent your code from crashing but it will at least prevent a catastrophe from happening if it does.

Close the code module window. The On Click property of the Cancel button now shows [Event Procedure] indicating that there is some code associated with that event. You can edit or view the code at any time simply by clicking the Build button.

TIP: If you want to remove the code associated with a particular event, just delete the entry [Event Procedure] from that event in the Properties Window.

Your code will be saved when you save the changes to the form so do that now. Switch the form to Form View and test your Cancel button. When you click the button the dialog box closes. Because you set the Cancel property of the Cancel button to Yes, pressing the [Esc] on your keyboard also closes the dialog box.

3.2 Programming the OK Button

The OK button needs to be programmed to run the query and then close the dialog box. The query already knows that it must get its criteria from the dialog box. I am getting the procedure to close the dialog box to deter the user from trying to run the query again whilst it is already open.

Use the same method as for the Cancel button to add an event procedure to the On Click event of the OK button. Your code should look something like this (Listing 3).

Listing 3:

Private Sub cmdOK_Click()
   O
n Error Resume Next
   DoCmd.OpenQuery "qryStaffLocator", acViewNormal, acEdit
   DoCmd.Close acForm, "frmStaffLocator"
End Sub

It is important that the code runs the query before it closes the dialog box. If the instructions were reversed the query would not be able to retrieve its criteria and the user would see an error message.

Note also that when opening the query you have a number of choices. You could, for example, choose to open the query Read Only (preventing the user from making changes to the data) or Add (allowing the user to add new entries but not edit or delete existing ones).

Save and test your custom dialog box. Remember that this kind of query has to be run from the dialog box. If you try to open the query by itself there will be an error (unless the dialog box is already open and showing choices). To do this simply place a command button on a form and add the appropriate code to its On Click event. For example (Listing 4):

Listing 4:

Private Sub cmdEmployeeLocator_Click()
   
On Error Resume Next
   DoCmd.OpenForm "frmEmployeeLocator"
End Sub

4. Variations on a Theme

In the tutorial I built a dialog box with multiple combo boxes. If you need only one you can save the user the effort of clicking buttons.

If the user leaves either combo box blank, the query will return no data. There are two ways to handle this.

1. You can insist that the user makes choices from all the combo boxes, by changing the code to check that both combo boxes contain a value (i.e. the user has chosen something).

2. You can modify the query to interpret an empty combo box as the user's wish to see all results for that particular field (i.e. leaving the Department combo box blank means you want to see records for all departments).

4.1 A Simple Single-Combo Dialog

This demonstrates the technique at its simplest and most elegant. It looks just like a parameter query dialog but it has a combo box (Fig. 23).

acctut08z

Fig. 23 A single-parameter dialog box.

If you are querying on a single variable field, then you need only one combo box. You don't need any buttons because you can run the query code when the user chooses an item from the list. You do this by attaching the event procedure to the After Update event of the combo box itself (Fig. 24).

acctut08aa

Fig. 24 Create an After Update event procedure.

The code looks the same (Listing 5), it's just in a different place...

Listing 5:

Private Sub cboOffice_AfterUpdate()
   DoCmd.OpenQuery "qryEmployeeLocator", acViewNormal, acEdit
   DoCmd.Close acForm, "frmEmployeeLocator"
End Sub

I have not allowed for the user wanting to close the dialog box without running the query, and if I was feeling benevolent I might change the form properties to reinstate the control box/close button.

If you decide that the user must make a choice (i.e. they are not allowed to leave one of the combo boxes blank) you need to be able to check their entries before running the query. To do this, modify the code that runs when the user clicks the OK button to look something like this (Listing 6):

Listing 6:

Private Sub cmdOK_Click()
   
On Error Resume Next
   If IsNull(cboOffice) Or IsNull(cboDepartment) Then
      MsgBox "You must choose both Office and Department."
      Exit Sub
   End If
   DoCmd.OpenQuery "qryEmployeeLocator", acViewNormal, acEdit
   DoCmd.Close acForm, "frmEmployeeLocator"
End Sub

Or for a more professional looking message box (Listing 7)...

Listing 7:

Private Sub cmdOK_Click()
   
On Error Resume Next
   If IsNull(cboOffice) Or IsNull(cboDepartment) Then
      MsgBox "You must choose both Office and Department." _
         & vbCrLf & "Please try again.", vbExclamation, _
         "More information required."
      Exit Sub
   End If
   DoCmd.OpenQuery "qryEmployeeLocator", acViewNormal, acEdit
   DoCmd.Close acForm, "frmEmployeeLocator"
End Sub

The code has been modified to include an IF statement that checks to see if either combo box is empty. If so, a message is displayed and the procedure terminated. If not, the query runs as normal.

TIP: When you modify existing code, Access sometimes displays an annoying habit of ignoring your changes! The solution is simple. In the code editing window select all your new code, Cut it (so that it disappears) then immediatelyPaste the code back again. It sounds crazy but it works!

4.3 Allowing Null Entries

Most people would assume that a blank combo box would mean they wanted to see everything. A query assumes the opposite so, if you leave any combo box empty the query returns no records. If you want to allow the user to leave any or all of the combo boxes empty you have to modify the query criteria. (See also the tutorial Parameter Queries: Handling Null Responses).

For each query field that you want to allow a null entry modify your criteria from:

[Forms]![frmStaffLocator]![cboOffice]

to:

[Forms]![frmStaffLocator]![cboOffice] Or Like [Forms]![frmStaffLocator]![cboOffice] Is Null

When you do this and run the query for the first time you will find that Access has changed the way your criteria were written from the text above to something more complex. The criteria grid now contains several rows of entries. Leave it alone! Access has just broken down your ... Or Like ... Is Null statement into its component parts, listing all possible combinations of null and not-null entries on separate lines. The query needs this but, thankfully, you can type it out the quick way!

TIP: Sometimes you have a lot to type into a query criteria cell. It's important that everything is correct so make life easier for yourself! Right-click the cell and choose Zoom to open a large editing window that lets you see what you are typing.

Final Thoughts

Running your queries from a form or switchboard offers a high degree of user-friendliness and allows you to help the user make their choices by providing them with lists of options.

This means that the query has to be run from the form rather than from the query itself so you will probably want to build a switchboard listing available queries for the user to run.

Remember that, if you choose to allow the user to leave a combo box blank, the query definition starts to get complex. Limit your dialog boxes to two or three combo boxes, unless you are prepared to wait a long time for Access to execute a highly complex query!


Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive

The reason for this is quite logical. Providing the data itself contains everything necessary to perform the calculation, to store the calculated result would be a waste of space. Also, should the data change, the result would have to be recalculated and edited to remain correct.

You can create calculations in queries, reports and forms (but not in tables). Instead of making a table containing fields for, for example, QuantityUnit Price, and Total, we build a table containing the data (Quantity and Unit Price) and a query showing these fields plus and additional calculated field which multiplies the two together. No calculations are stored, saving space in the database, but the results are instantly accessible by running the query which calculates them as they are needed.

This tutorial shows you how to perform simple calculations in a query. For more examples of calculations look at Working with Dates in Access Queries and Calculating Totals in Access Queries.

When you perform a calculation you create and name a new field that contains the calculation you need. To begin, open a new query in design view and specify whatever criteria you need (if any). Then…

Name the New Field

In the Field row of a new column type the name of your new field followed by a colon. For example: New Field:

Enter the Calculation

Type the name(s) of the field(s) to be calculated using the appropriate mathematical operators (+, -, *, / etc.). For example if you wanted to multiply two fields together: [Field 1]*[Field 2]

Here are some examples…

Calculating with Different Fields

acctut02aTotal Cost: [Quantity]*[Unit Price]

In this example the query creates a new field called Total Cost and displays in it the value in the Quantity field multiplied by the value in the Unit Price field.

Calculating with Fields and Constants

acctut02bDiscount Price: [Unit Price]*0.9

In this example the query creates a new field called Discount Price and displays in it the value in the Unit Price field multiplied by 0.9.

Note: This is the same as multiplying by 90% or subtracting 10% but the query does not recognise the % symbol and we have to devise an alternative expression.

Formatting the New Field

When you are performing calculations, you may wish to see your results formatted in a particular way - currency for example. Normally field formatting is specified in a the design of a table, but as the field is being created by the query there is another way…

In the query's design view, right-click anywhere in the new field's column and choose Properties… from the shortcut menu (or click somewhere in the new field and choose View > Properties from the menu). This opens the Field Properties dialog box...

acctut02c

Click the format box, and then click the down-arrow to display a choice of formats. Choose from the list, in this example Currency is selected. Finally, click [X] to close the dialog box. 

When you run the query your new field will appear, containing the calculated values and formatted as you specified.

Dos and Don'ts for Calculated Fields

  • In new field definition what goes to the left of the colon becomes the new field's name and what goes to the right becomes the calculation.
  • Remember to follow the usual rules for naming fields (no illegal characters) and choose a name that does not already exist amongst those in the table(s) being calculated.
  • Make sure you spell the names of the calculated fields correctly (a good reason for keeping field names short) and remember to enclose field names in square brackets.

Calculations are simple and straightforward. You just substitute field names for numbers. If you use a calculation in a Make-Table query the new table will include a new field containing the new calculated data.


Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive

Why Use a Calendar?

Getting dates entered accurately always manages to be a problem. Everyone seems to have their own way of writing a date. In the USA the standard format is month/day/year whereas most of Europe uses day/month/year. What about date separators... should you use a slash, a dash or a dot, or is it OK to use nothing at all? Or can you type something like 27-Sep-02? In fact, most of these options are acceptable date formats and can be catered for with Windows settings, and even field property settings in Access tables and forms.

Difficulties arise when you create an unbound text box to accept a date on a form that is designed to, for example, pass criteria to a query. Because there is no underlying form field to reject incorrect dates, you need some other way to make sure that the user has entered good data. Sometimes you just don't know what date to enter... "the 3rd Thursday in April next year".

acctut09a

What you really need is a calendar, and thankfully Microsoft has provided us with one that can be placed on a form. This tutorial shows you how to find the calendar tool and use it to include pop-up calendars on your Access forms, and explains how to add the simple VBA programming instructions necessary to make the calendar work.

How to Add a Calendar to an Access Form

There are three steps to creating a pop-up calendar. First you must prepare the form, then draw and customize the calendar to your requirements, and finally add the VBA code which will drive the process.

The calendar will be hidden until the user clicks the form's date field, when the calendar will appear. When a date is chosen the calendar will pass the it to the date field before being hidden again. If the user uses their [TAB] key to enter the date field, the calendar will not appear, and they will be able to type a date directly into the field if they wish.

Step 1: Prepare the Form

Date fields on forms are usually represented by simple Text Box controls. The first step is to convert your date field text box into a Combo Box. The reason for this is to prompt the user to click it! When a user sees the arrow button on a combo box they know that clicking it will reveal a list. In this case, instead of a list, they will see a calendar.

Select the date field text box on your form and go to Format > Change To > Combo Box...

acctut09b

This will convert your existing text box to a combo box. Alternatively you could draw a new combo box on your form with the Combo Box tool in the form design toolbox.

acctut09c    >>>    acctut09d

Step 2: Draw the Calendar

You won't find the a calendar tool in the form design toolbox but one is provided as an "optional extra". Click the More Controls button on the toolbox, and wait a moment while Access builds its list of available controls. Then look for Calendar Control 10.0 (for Access 2002, or 9.0 in Access 2000, or 8.0 in Access 97) and select it...

acctut09e    >>>    acctut09f

The mouse pointer will turn into the Calendar Control tool and you can click anywhere on your form to insert a calendar...

acctut09g

acctut09h

You will probably want to resize and position the calendar to suit the design of your form. Double-click the calendar to open its own properties window (alternatively, right-click the calendar and choose Properties in the usual way, then go to the Othertab and find Custom. Click the build button [...] to open the custom properties window). These properties are additional to the regular set of properties that you normally see for form controls. You can change font, colour scheme, and layout to suit your own requirements.

In this example, I want to make the pop-up calendar quite small, so I have opted not to see the Month/Year Title and also set the font size to 8. You can resize the calendar in the normal way by dragging one of the handles around its edge. I have also given the calendar a size 1 border using the Line/Border Width tool on the form design toolbar. Here's how my calendar will look in Form View...

acctut09i

Remember that the calendar is going to be hidden until the user asks for it by clicking on the date field combo box (you don't have to do it this way but I prefer to!). Make sure that your form is big enough to display the calendar when it is un-hidden. If you wish, you can place the calendar over existing controls. This does make building the form a bit difficult but it can help to economise on space.

NOTE: The Calendar Control is an ActiveX control (actually a file called mscal.ocx) supplied with Microsoft Office. It is normally installed with a standard installation of Microsoft Office, Excel or Access, but if you can't find it on the list you will need to get hold of a copy. If you are distributing your file, or planning to use it on more than one computer, you will also need to make sure that the host computer has the mscal.ocx file installed.

Finally, right-click on the calendar control and choose Properties to open its properties sheet and on the Format tab set the Visible property to No.

Step 3: Write the Code to Power the Calendar

Code to Display the Calendar

Two code procedures are required: one to un-hide the calendar when the user clicks the date field combo box; and another to re-hide the calendar when the user chooses a date. The first procedure will be refined to match the calendar's date with any date that is already shown in the date field (otherwise to display the current date). The second procedure must include an instruction to transfer the chosen date to the date field combo box.

Open the properties sheet for the date field combo box and on the Event tab click in the space next to On Mouse Down. Then click the build button [...] to open the Choose Builder dialog, choose Code Builder and click OK...

acctut09j    >>>    acctut09k

The code editor window opens with the first and last lines of the date field combo's mouse down event procedure already written, and your cursor is in the space between. Enter the following two lines, the first to unhide the calendar and the second to transfer the focus to it:

ocxCalendar.Visible = True
ocxCalendar.SetFocus

NOTE: I have named the date field combo box on my form cboStartDate and the calendar ocxCalendar. Substitute the names of your combo and calendar for these when you write the code.

Now enter the following lines, in which an If Statement checks the date field combo box to see if it already contains a date. If it does, then the calendar is instructed to display this date. If not, the procedure uses the VBA Date function to instruct the calendar to display the current date:

If Not IsNull(cboStartDate) Then
   ocxCalendar.Value = cboStartDate.Value
Else
   ocxCalendar.Value = Date
End If

The finished procedure should look like this:

acctut09l

Return to your form and test the code (now is a good time to save the form in case anything goes wrong - just click the save button on the toolbar). Switch the form to Form View and click the form field combo box. The calendar should appear displaying either today's date (if the combo box was empty) or the same date as shown in the combo box.

Code to Hide the Calendar and Transfer the Date

Return to the code editor window and open the left-side combo box at the top of the code window. This displays a list of objects on the form. Choose the name of your calendar (in this example ocxCalendar), then open the right-side combo box to display a list of procedures and choose Click. This prompts the editor to create the first and last lines of the calendar's click event procedure...

acctut09m    >>>    acctut09n

(NOTE: if the lines of any other procedure are created when you make the first choice you can just delete them.)

Enter the following lines of code:

cboStartDate.Value = ocxCalendar.Value
cboStartDate.SetFocus
ocxCalendar.Visible = False

The first line transfers the date chosen in the calendar to the date field combo box. The next line sets the focus back to the date field combo box. The last line hides the calendar again. The finished code should look like this:

acctut09o

Return to the form design window and save the form. Switch to Form View and test your calendar. If anything fails to work properly, go back to the code window and check your typing!

Your pop-up calendar is finished! Unless of course...

What About Multiple Calendars?

It's quite likely that you will have more than one date field on a form. In which case, do you need a separate calendar for each?... No!

Each date field combo box can call the same calendar control, but the calendar needs to know which combo called it so that it can return a date to the correct one. This needs only a minor change to the code we have already used.

Declare the Variable

First of all, declare a variable to hold the name of the combo box that called the calendar. I'll name my variable cboOriginator and declare it with the line...

Dim cboOriginator As ComboBox

...at the very top of the form's code window after the Option Compare Database and Option Explicit statements but before the first procedure, like this:

acctut09p

Declaring the variable like this makes it global so that it will hold a value after the procedure that gave it the value has finished, and can pass a value from one procedure to another.

Pass the Originator's Name to the Variable

There will be a series of almost identical MouseDown procedures, one for each date field combo box. Add a line to each one to pass the name of the originating combo box to the variable:

 Set cboOriginator = cboStartDate

NOTE: the use of the "Set" keyword when assigning a value to the variable. The set keyword is required when assigning a value to an object variable.

Other references to the combo box in the procedure can now be written as cboOriginator:

acctut09q

Pass the Date Back to the Originating Combo Box

The calendar's Click procedure is similarly modified to pass the date back to the combo box named in the cboOriginator variable.

acctut09r
A final line:

 Set cboOriginator = Nothing

...empties the variable, ready for the next call.


Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive

The idea of a parameter query is that it offers the user some choice when they run the query (see the tutorial on Using Parameter Queries). Instead of you having to anticipate the various combinations of criteria that you are going to need and creating a separate query for each, you use parameters to prompt for information when the query is run. Access does this by presenting the user with an input box into which they type what they want to see. When they click the [OK] button the query places what the user typed into the appropriate place in the query definition and runs the query.

But what if the user leaves the input box empty? You might expect that if the query receives no input it would return all the records, but that isn't what happens. It returns nothing at all - an empty recordset.

It's really easy to adjust your criteria so that the query will return all the records when the user ignores the prompt and doesn't type anything. They just click [OK] and if they want to see all the records. Here's how...

A Regular Parameter Query

Here's a regular parameter query with a prompt for the user to enter the name of the Office whose records they want to see in the query's result...

acctut07a

In this example the user typed Cardiff and got the following result...

acctut07b

If they had ignored the prompt and left the input box empty they would have seen no results at all.

Giving the Option to Return All Records

Suppose the user doesn't know what to type, or perhaps this time would like to see all the records? All we have to do is adjust the criteria to accept whatever the user types, or to return all the records if they type nothing (i.e. if the input is "null").

Here the criteria have been modified to accept a null entry...

acctut07c

...and here's what the user saw when they left the prompt box empty...

acctut07d

Ignoring the prompt returns all the records thanks to the modification to the criteria. To summarise...

A parameter that requires an input from the user, otherwise no records are returned is written...

[type prompt here]

A parameter that can accept an input from the user, but that will return all records if no input is made is written...

[type prompt here] Or Like [repeat prompt here] Is Null

As you can see, the prompt is entered twice although the user sees only one input box. It is important that the prompt is exactly the same in both cases, otherwise Access will treat them as separate parameters and the user will see two input boxes (although the query will probably still work!).

Variations on a Theme

If you prefer you can put the two parts of the parameter on separate lines in the query grid, like this...

acctut07e

Going down the column is equivalent to typing "Or" in the criteria. But I prefer to do it the other way. Doing it this way is fine if you aren't combining the parameter with criteria on other fields. If you are, then you have to be careful that your criteria read the way you intended. I find it's easier to put the whole thing on one line.

TIP: Sometimes you have a lot to type in the cell of the QBE grid. You can stretch the column to fit what you type - point to the tops of the columns where they meet. When your cursor changes from an arrow to a black cross with horizontal arrows you can drag to the desired width or double-click to snap to fit. An easier way is to right-click on the cell and choose Zoom... from the shortcut menu. A large text box opens into which you can type your entry. Close the text box to put your typing into the cell. This is great for doing those fiddly corrections to existing entries. It works in tables too!

Just like regular parameters, these ones can be combined into multiple parameters. For example, I could have had an additional prompt for Department which could also accept a null entry (click the thumbnail to see the full-size image)...

acctut07f small

Now the user can specify an Office or a Department or neither or both. Now don't tell me you don't think that's clever!


Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive

What Do Cascading Lists Do?

The purpose of cascading lists is to give the database designer greater control over data input, and to make things easier for the user. On their own, combo boxes make data input easier and more reliable. When two or more combo boxes work in conjunction with each other they can help to reduce the user's decision making by prompting them with only relevant data.

In this example there are two combo boxes: one displays a list of countries, the other displays a list of cities. If they worked independently the user could choose a country and a city but might get the combination wrong. Also, the list of cities in would have to show all the cities available. The combo boxes can be linked in a number of ways. The illustration below shows how linked combo boxes might work. The user chooses a country first then opens the city list. They see a list of cities relevant to the country they selected.

To see the demo, point at the numbered items below and watch the image change (if necessary scroll your browser window so that you can see the whole image).

1. The form has two regular combo boxes
2. The Country combo box displays a list of countries
3. Choosing "France" loads a list of French cities into the City combo box
4. Choosing "United Kingdom" loads a list of UK cities into the City combo box
5. Choosing "United States" loads a list of US cities into the City combo box
 

acctut10a

The following examples show several different ways of achieving this effect and are presented in an increasing order of complexity.

Example 1: Multiple Row Source Tables

This is the simplest method to code but it requires a table for the main combo box and several tables for the dependent combo box. This example has a table (tblCountries) that is assigned as the Row Source of the main combo box (cboCountry). There are also three more tables (tblFrancetblUnitedKingdom, and tblUnitedStates) which will provide in their turn the row source for the dependent combo box (cboCity). To begin with, no row source is specified for the dependent combo box.

acctut10f           acctut10g

The plan is to have the contents of the cboCity list change to reflect the user's choice from cboCountry. This will be achieved by programmatically defining the Row Source property of cboCity using the After Update event of cboCountry. Here's the code that does the job:

Private Sub cboCountry_AfterUpdate()
   On Error Resume Next
   Select Case cboCountry.Value
      Case "France"
         cboCity.RowSource = "tblFrance"
      Case "United Kingdom"
         cboCity.RowSource = "tblUnitedKingdom"
      Case "United States"
         cboCity.RowSource = "tblUnitedStates"
   End Select
End Sub

How it works...

The code for this method is very simple. The AfterUpdate event fires when the user makes a choice from the cboCountry combo box. The code uses a Case Statement to assign one of the city tables to to the Row Source property of the cboCity combo box according to the user's choice.

Example 2: A Single Row Source Table

This example uses a single table which provides data for the Row Source of both combo boxes. The table (tblAll) contains two fields, one for the name of the City and the other for the name of the Country to which it belongs...

acctut10h

The Row Source property of the cboCountry combo box takes the form of an SQL statement which represents a query of the tblAll table returning the unique values found in the Country field, sorted into ascending order:

SELECT DISTINCT tblAll.Country FROM tblAll ORDER BY tblAll.Country; 

As in the previous example, no row source is specified for the dependent combo box. The code will deal with that. The following procedure runs on the After Update event of the cboCountry combo box:

Private Sub cboCountry_AfterUpdate()
   On Error Resume Next
   cboCity.RowSource = "Select tblAll.City " & _
            "FROM tblAll " & _
            "WHERE tblAll.Country = '" & cboCountry.Value & "' " & _
            "ORDER BY tblAll.City;"
End Sub

How it works...

When the user makes a choice from the cboCountry combo box the AfterUpdate event fires. The attached code defines a Row Source to the cboCity combo box in the form of an SQL statement.

NOTE: When writing SQL in VBA code I usually write my SQL statements as shown above with each clause on a separate line. I think this makes them easier to read. If you do this remember to enclose each line in quotes, concatenate (i.e. join) each line with an ampersand (&) and insert the line continuation character space+underscore (  _) when you break the code line.

The SQL statement changes according to the user's choice as determined by the inclusion of cboCountry.Value in the WHERE clause. If, for example, the user had chosen France then the line containing the WHERE clause would read:

            "WHERE tblAll.Country = 'France' " & _ 

Example 3: Synchronising the Lists

This example uses the same code for the After Update event of the cboCountry combo box to assign the appropriate Row Source to the cboCity combo box as in the previous example. This time an additional procedure has been added to synchronise the cboCity Row Source with any existing value that might already be in the City field.

When a Row Source is assigned to the cboCity combo box, it remains assigned until it is reassigned by a change in the cboCountry combo box. So, when navigating through existing set of records, the list of cities may not be appropriate for the city shown in the City field.

For example, you enter a record for an address in the United Kingdom so cboCity displays a list of UK cities. You then move back through the records and stop on an address in France. You might wish to edit that address so you open the cboCity combo and instead of seeing a list of French cities you see a list of UK cities! This happened because that was the last Row Source assigned to the combo box and nothing has happened since to change it.

This example corrects that using the following code on the form's On Current event:

Private Sub Form_Current()
   On Error Resume Next
' Synchronise country combo with existing city
   cboCountry = DLookup("[Country]", "tblAll", arrowgreendown
            "[City]='" & cboCity.Value & "'")
' Synchronise city combo with existing city
   cboCity.RowSource = "Select tblAll.City " & _
            "FROM tblAll " & _
            "WHERE tblAll.Country = '" & cboCountry.Value & "' " & _
            "ORDER BY tblAll.City;"
End Sub

How it works...

The OnCurrent event fires when a form moves from one record to another (or when the form is refreshed or requeried).

The first line uses the DLookup function (this works like Excel's VLOOKUP function). This gets the value (i.e. the existing city name) from the City field, looks for it in tblAll table, and returns the corresponding Country name which it places in cboCountry combo box. (NOTE: If, as in my example there is a Country field in the form's underlying table then this data should be in place anyway - but I'm just making sure! In fact, you might want to omit or modify this line if you were allowing users to enter city names that were not on the list.)

The next line is the same as the one that runs on the AfterUpdate event of the cboCountry combo box but we need it here because the AfterUpdate event has not been fired.

Example 4: Mixing Controls - Using an Option Group

The method works just as well with different combinations of controls. Here I have replaced the cboCountry combo box with an Option Group called grpCountry with option buttons for the various countries.

The illustration below shows how it works. To see the demo, point at the numbered items below and watch the image change (if necessary scroll your browser window so that you can see the whole image).

1. If there is no city chosen, the Country group displays a "Null" value
2. Choosing "France" loads a list of French cities into the City combo box
3. Choosing "United Kingdom" loads a list of UK cities into the City combo box
4. Choosing "United States" loads a list of US cities into the City combo box
 

acctut10i

Writing the cascade code...

This form incorporates all the features of the previous example (e.g. synchronising the form with the data) but the code differs because I have used an Option Group (named grpCountry) instead of a combo box for the user to choose a country. Of course, an option group does not require a Row Source. Instead, all the options are on view as labelled option buttons (sometimes called "radio buttons"). Each Option Button has an Option Value property. This value must be a whole number but it can be whatever you want (within limits - its data type is Long) but it is logical to give them sequential numbers so mine are France(1), United Kingdom(2) and United States(3). When the user chooses one of the options by clicking it a black dot appears in the button and the Option Group has a value corresponding to the Option Value of the selected button. So, if the user selects the United States button the Option Group has a value of 3.

NOTE: When you draw a group of Option Buttons you should draw the Option Group frame first, then draw the Option Buttons inside. When you do this all the buttons inside the frame become part of the same group and function properly. Only one can be selected at a time, and the selected button passes its value to the group.

The code used to assign a Row Source to the cboCity combo box runs on the After Update event of the grpCountry Option Group (NOT the event of the individual button). The event fires when the user selects one of the buttons:

Private Sub grpCountry_AfterUpdate()
   On Error Resume Next
   Dim strCountry As String
   Select Case grpCountry.Value
      Case 1
         strCountry = "France"
      Case 2
         strCountry = "United Kingdom"
      Case 3
         strCountry = "United States"
   End Select
   cboCity.RowSource = "Select tblAll.City " & _
            "FROM tblAll " & _
            "WHERE tblAll.Country = '" & strCountry & "' " & _
            "ORDER BY tblAll.City;"
End Sub

How it works...

First of all, a Case Statement checks the value of the group and puts the corresponding country name a string variable called strCountry. Then an SQL statement, incorporating the variable and querying the tblAll table, is assigned as the row source of the cboCity combo box.

It works in much the same way as previously but the code gets the name of the country from an Option Group instead of a Combo Box.

Writing the synchronise code...

Similarly the forms On Current event is used to synchronise the Option Group with the City field, and again the code is a bit more complex:

Private Sub Form_Current()
   On Error Resume Next
   Dim strCountry As String
   If IsNull(cboCity.Value) Then
      grpCountry.Value = Null
   End If
' Synchronise country combo with existing city
   strCountry = DLookup("[Country]", "tblAll", arrowgreendown
            "[City]='" & cboCity.Value & "'")
   Select Case strCountry
      Case "France"
         grpCountry.Value = 1
      Case "United Kingdom"
         grpCountry.Value = 2
      Case "United States"
         grpCountry.Value = 3
   End Select
' Synchronise city combo with existing city
   cboCity.RowSource = "Select tblAll.City " & _
            "FROM tblAll " & _
            "WHERE tblAll.Country = '" & strCountry & "' " & _
            "ORDER BY tblAll.City;"
End Sub

How it works...

You might have noticed that in the first illustration none of the option buttons are selected, all are greyed out. Form designers often like to set one of the buttons of an option group as the "default" button so that it is already selected when the form opens or when the user moves to a new record. It's easy to do, just find the option group's Default Value property and set it to whatever value represents the button you want selected. But I didn't want to do that. Since I was going to synchronise the option group country with any existing city, I decided to give the group a Null value if no city name was present. This is achieved by the If Statement at the start of the code procedure.

Next comes the code to determine the correct country for the existing city (if present). Like the previous example it uses the DLookup function, but unlike the previous example it can't feed the country name straight into the option group. It has to convert the name to a number (the value of the appropriate option button) so it uses a Case Statement to do this. Finally, the same code as before synchronises the combo box list with the existing city.


© 2019 Austin Laptop [AustinLaptop.Com]. All Rights Reserved.