Access is a relational database management system from Microsoft. Access is widely used by small businesses, within departments of large corporations, and hobby programmers to create ad hoc customized systems for handling the creation and manipulation of data.

Apple Online Store

Its ease of use and powerful design tools give the non-professional programmer a lot of power for little effort. Access is included in the Microsoft Office Professional Edition (Academic Version). This area of the web-site is specific to Access code tips and their daily use.  Click on the bulleted items below to view Access code tips.

Calculate age of person

How do you calculate the age of a person given his/her birth date (DOB)? Assuming that the birth date field is called [DOB] and is of type date, you can use the following calculation:

Age: DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))

Back to Top^

DateValue: Turn a text string date into a number

When a field for dates is set up as text and later you want to calculate with that date, you need a function to turn the text into a number:

DateValue(date)

Examples and their results:

DateValue("Oct. 10, 2007") returns the value 10/10/2007
DateValue([DateGraduated]) returns the value 6/1/2007 when the field "DateGraduate" has the text value "June 1, 2007


 

Back to Top^

Filter Query Results from a Form

You should be aware that you can limit the results of a database select query by using particular criteria. What if you are not too sure about what criteria applies to the particular field? Wouldn't it be better to be able to choose this from a list rather than having the standard Microsoft Access input box appear? You can create a simple Access form, containing a combo box, and base the query criteria on the value chosen in the form as detailed below.

To begin with we will create a table (tblStudentGrades) in Microsoft Access containing some values to search on. The table is shown below:


The next stage is to create the Access query (qryStudent) that we will later apply criteria to. The query design, shown below, prior to any criteria being applied:

Once we have created the query above, we then need to create the Microsoft Access database form (frmSearchStudent) that we are going to use to apply the criteria to the query. This form will contain the drop-down combo box with a list of values to choose from that will run the query. The form design is shown below:

The combo box will be linked to the table tblStudentGrades.

 Within the form's design view, make sure the Properties for the combo box are visible by clicking the Properties button.

Click to activate the combo box. Under the Format Tab within the Properties box, name the combo box "cboSearch". Next, click the Tab "Event" to activate its properties.

On the "On Change" Event, click the expression builder button...

 to open the "Choose Builder" dialog box.

Choose "Code Builder" and you will be taken to the Access Visual Basic Editor. Within this editor, copy the following code within the procedure:

DoCmd.OpenQuery "qryProducts_And_Locations"
Me.cboSearch = ""

Your Visual Basic code should look similar to this:

Private Sub cboSearch_Change()
 DoCmd.OpenQuery "qryStudent"
 Me.cboSearch = ""
End Sub

The above code runs the query and re-sets the search drop down list to a blank selection. What we now need to do is apply the criteria...

[Forms]![frmSearchStudent]![cboSearch]

...to the query design that will ensure that the filter is applied based upon the value chosen in the form and this criteria is shown below:

So now, when we choose a value from the drop down list on the form...

...the query will run and return only the item chosen from the list:

You can download this Microsoft Access 2003 Database "filter_combo_box" here.

Back to Top^

Parameter Queries

In the Criteria cell for the field you want to use as a parameter, type an expression with a prompt enclosed in square brackets. For example, in a field that displays the current number of units in stock, enter the following:

<[Number of units of Stock:]

To use more than one parameter, in the Criteria cell for the field you want to use as a parameter, type an expression with prompts enclosed in square brackets. For example, in a field that displays dates, you can display the prompts "Type the beginning date:" and "Type the ending date:" to specify a range of values:

Between [Type the beginning date:] AND [Type the ending date:]

To use a wild card with parameters, in the Criteria cell for each field you want to use a parameter, type an expression with a prompt enclosed in square brackets. To prompt the user for one or more characters to search for, and then find records that begin with or contain the characters the user specifies, create a parameter query that uses the LIKE operator and the wildcard symbol (*). For example, the following statement searches for words that begin with a specified letter:

LIKE [Enter the first character to search by:] & "*"

The following statement searches for words that contain the specified character:

LIKE "*" & [Enter any character to search by:] & "*"

 

Back to Top^