Microsoft Access Tips
If you like these tips, you'll love the free, opt-in, monthly publication, The
Office Newsletter. View a recent issue and subscribe
.
Query Criteria Tips
You may already be familiar with two wildcards supported by Access: ? and
*. The question mark represents any single alphanumeric character, while the
asterisk (sometimes called a splat) represents a character string of any
length.
For example: Like "B?T" locates bit, bat, bet, bot or
but. Like "B*T" will also locate boot, beruit,
bait, and ballot.
A fantastic tool for select queries is the square brackets, which will find any
characters listed inside the brackets. You'll generally use this operator along
with other wildcards. For example: Like "[E-J]*" locates records
where the entry starts with E, F, G, H, I, or J.
Use the exclamation point ! (sometimes called a bang symbol) to create a
"not like" expression. For example: Like "[!E-J]*" locates
all records that start with anything except the letters E, F, G, H, I, or J.
Attach Your Own Notes To Objects
The Tag property for a form, a report, a section, or a control will accept up
to 2,048 characters. And the contents of this property has no effect at all on
the appearance or behavior of the object. Find the Tag property on the Other
tab of the object's property sheet, and consider it the object's little
notepad, ready to accept your documentation.
Customized Tip Text
Help your users become better at data entry; provide them with screen tips
you've customized!
Enter helpful text in the ControlTip Text property. The ControlTip Text
property can be found on the Other tab of the Property sheet for any control.
The text will pop up in a small box when the user parks the mouse over the
control for a second or so.
Customize A Default Format For Controls
Set a default format for your controls! Just select the control on the
Toolbox and then adjust the format using the Formatting toolbar.
Every control you place on that form or report will then be formatted that way.
You can also set formatting so any new form will inherit your custom format:
Create a new form.
- Select a control in the Toolbox and then make selections using the Formatting toolbar.
- Save the form with the name Normal.
- From now on, all controls on every new form will be formatted as in the Normal form.
Microsoft Access Database Cluttered With Duplicates?
Make sure you have "Queries" selected in the Object Bar on the left and click
on the "New" button at the top and select the "Find Duplicates Query Wizard".
This will walk you through selecting the table or query where the duplicates
are located, then selecting all of the fields that might have duplicate
information.
CAUTION: This will only find duplicates; it won't delete them, and
if you manually delete the records returned by this query, you'll delete all
copies including the original. Consider yourself warned.
Insert a Word Doc Into a Record
Do you have letters to send out to contacts in your database? While a report
will function, the formatting capabilities of Word can't be matched. Here's how
you can 'save' a Word document in an Access field:
In one of your tables, create a field with a data type of OLE Object. In an OLE
Object field, you can insert a Word document, an Excel worksheet, or a
PowerPoint presentation. To actually insert the file, right-click the field
where you want to place it (in either Datasheet or Form view) and choose Insert
Object.
If the object does not exist yet, select Create New in the Insert Object dialog
box and choose the appropriate item in the Object Type list box. If the file
already exists, select Create From File and then browse to the source document.
Select the Link check box only if you want the document to be bound to the
source document (so that if it's edited in Word, those edits will also exist in
Access). Click OK. If you place this field on a form or report, in effect
you'll be editing or printing the Word, Excel, or PowerPoint file.
Drag a Microsoft Access Table Into Word
Begin with Word running, but minimized to the taskbar. Highlight the table
object in the database window and drag the highlighted table down onto the Word
button and hold there until Word is restored (without letting up on the mouse
button). Once Word is restored, drag the table up into the Word document. See
the little square attached to your mouse pointer? That's your table. Let go of
the mouse button and the table will appear.
Filter to Compare a Date With Today
In a table, click Records, Filter, Advanced Filter/Sort. Select that field (for
example: dtmInvoiceDate) for your filter and in the criteria row enter:
<Date()
This filter will return records where the dtmInvoiceDate is before
today's date.
You can then manipulate this. If, for instance, invoices are due 15 days after
the invoice date, the criteria would look like this: <Date()-15. This filter
will return records where the dtmInvoiceDate is 15 days before today's date.
Organize Your Database With Groups
Groups let you organize your database by putting shortcuts to related objects
in one place. For example, you can create a group that contains a table,
queries based on the table, and forms used to enter data in the table. You
create a group by right-clicking on the area under the Groups bar (in the
Database Window) and choosing New Group from the shortcut menu. You can then
drag objects from the database window to add shortcuts to the group objects. An
object may be added to as many groups as you wish. The Groups feature is new to
Access 2000.
Change the Start Value of an Autonumber Field
Create your table with an AutoNumber type field, but don't enter any records.
Then create another table with only a single Long Integer Number type field.
This field must have same name as the AutoNumber field in the first table.
Enter one record in the second table that is a number one less than the
required start of the AutoNumber for the first table.
Now create an append query to append the record in the second table to the
first table and run the query. You can now delete the second table and begin
entering your data into the first table.
Conditional Formatting a Control in a Form or a Report in Microsoft Access 2000 or 2002
Perhaps you want to format a Unit Price text box to display red text on a
yellow background if the value is over $100. Open the form or report in Design
View. Select the control and, on the Menu bar, click Format, Conditional
Formatting. This will produce the Conditional Formatting dialog box. This
feature is new to Access 2000.
Use Your Own Picture as the Background of a Form
Open the form in Design View and bring up the property sheet for the form. (One
way to do this is to double-click on the gray box in the upper-left corner of
the form window, at the intersection of the vertical and horizontal rulers.)
Click the format tab of the property sheet. Click in the Picture box. Click the
Build button (it has three dots on it). In the Insert Picture dialog box that
appears, navigate to the graphics that you want. Select it and click OK. Notice
on the property sheet there are also properties that allow you to specify the
alignment of the picture, and whether or not it is tiled.
Send an Access 2000 Report to Word
To save a report as a Word file (a .rtf with most of its formatting included),
either open the report in Print Preview or select the report name in the
database window, and click Tools, Office Links, Publish It With MS Word.
Rearranging the Tab Order on a Form
The sequence of controls the user moves through when pressing the Tab key is
assigned when the form is created, so if you rearrange the controls, the tab
order may be out of sequence. This almost guarantees data entry errors. To
change the tab order, choose View, Tab Order, or right-click and choose Tab
Order, to open the Tab Order dialog box. You will usually only be concerned
with the Detail section. Clicking the Auto Order button will usually fix it,
but if it doesn't: click the button to the left of the control name to select
it, and then drag it up or down into the correct position.
Query to Calculate Years of Service Based on Hire Date
Use a combination of the Now() function, the Year() function, and a table
field. Now() returns the current date and time. Year() returns the year from a
date. By nesting these two functions, you can calculate the current year and
the year hired and then subtract the year hired from the current year. If your
Date Hired field is named dtmHireDate, your function will look like this:
Expr1: Year(Now())-Year([dtmHireDate])
Just place the function in the field cell of a query grid. Remember, you can
always change 'Expr1' to a field name of your choosing. Another great use for
this function is to calculate age from date of birth.
Running a Make Table Query
Sometimes you need to create a new table from a subset of data in an existing
table. Perhaps you have a large table of customer records that you wish to keep
intact, but you need a separate table of customers who have placed orders in
the last two years.
- Create a query that contains the records you want to put in
a new table. Have the query open in Design View.
- Click the arrow next to the Query Type button and change the query to a Make Table query.
- In the Table Name bax, enter the name of the table you wish to create. You can
put choose to put the new table in the current database, or a different database. Click OK.
- Select the data you want to include in the new table, and enter the appropriate
criteria to get the records you want.
- To preview, switch to Datasheet View. Make any necessary changes in Design View.
- Click the Run button to create the new table. In the warning box, click Yes.
Close the query without saving it (unless you anticipate needing to make a new
table with this query again).
What Our Customers Say:
Great class! I really enjoyed it; I gained the knowledge I came here for and had fun doing it.