Allen Browne's Database and Training

for small business and not-for-profit organizations

Welcome to Allen Browne's tips for Microsoft Access

Brief tutorials and help for novices and database developers. Examples and solutions to implement. Bugs and traps to avoid. Sample code and utilities to download. All free.

This is literally an FAQ. It has grown in response to the most frequently asked questions in Access newsgroups, for more than 13 years. Each page explains the most efficient solution that works completely within Access, as simply as possible.

For a summary of the latest version, see Converting to Access 2007.

Tips for Casual Users

If you don't know how to write code, these may help.

Expressions

Getting a value from a table: DLookup() All versions
Nulls: Do I need them? Access 95 and later
Common errors with Null Access 95 and later
Calculating elapsed time All versions
Quotation marks within quotes All versions
Why can't I append some records? Trouble-shooting imports All versions
Rounding in Access - Round up, round down, rounding time values Access 95 and later

Tips for Serious Users

Solutions for developers.

Forms

Filter a Form on a Field in a Subform Access 95 and later
Locking bound controls on a form and subforms Access 97 and later
Duplicate the record in form and subform Access 95 and later
Flicker with tab controls Access 2003 (Addressed by SP3 for Office 2003.)
Assign default values from the last record - Carry data over Access 95 and later
Default Forms, Reports, and Databases All versions
Managing Multiple Instances of a Form Access 95 and later Sample database
Rolling dates by pressing "+" or "-" Access 2 and later
Return to the same record next time form is opened Access 95 and later
Unbound text box: limiting entry length Access 95 and later
Properties at Runtime: Forms Access 95 and later

Combo Boxes

Using a Combo Box to Find Records Access 97 and later
Combos with Tens of Thousands of Records - Postcodes example Access 95 and later
Adding values to lookup tables Access 95 and later

Reports

Use a multi-select list box to filter a report Access 95 and later
Print a Quantity of a Label All versions
Has the record been printed? Access 2000 and later Sample database
List Box of Available Reports Access 95 and later
Format Check boxes on reports: size, color, unbox, conditionally format. All versions
Sorting report records at runtime Andy Baron Access 95 and later
Print a page with 3 evenly spaced mailing slips All versions
Reports: Page Totals Access 2 and later
Reports: Blank Line every Fifth Record Access 2 and later
Reports: Snaking Column Headers Allen and LeAnne Jergensen All versions

Queries

Lookup in a range of values in a query Tom Ellison All versions
Action queries: suppressing dialogs, while knowing results Access 97 and later
Why is my query read-only? All versions
Truncation of Memo fields All versions
Crosstab query techniques - row totals, zeros, parameters, column headings All versions
Subquery basics - Intro to subqueries. Covers Year to date, Top N per group, Previous row, Unmatched delete, ... All versions
Surviving subqueries - Trouble-shooting common problems with subqueries. All versions
CPU utilization under Windows Vista All versions
Ranking and numbering records - All versions

Relational Design

Split your MDB file into data and application All versions
Attached tables: reconnect on start up Peter Vukovic Access 2 and later
Self Joins: tables that look themselves up (Pedigrees example) All versions
DAO: Data Access Objects Access 2 and later
Field type names (JET, DDL, DAO and ADOX) Access 2000 - 2003
Set AutoNumbers to start from ... Access 95 and later
Custom Properties: Copyright, Version, Counter Paul Murray Access 2
Cascade-to-Null Relations Access 2000 and later Sample database
Why I stopped using Yes/No fields All versions Sample database (for Access 2000 and later)

Tips for Programmers

Coding examples for VBA (Visual Basic for Applications.)

Solutions (VBA)

Error Handling in VBA Access 95 and later
ELookup() - an extended replacement for DLookup() Access 95 and later
ECount() - an extended DCount(). Count distinct values. Access 95 and later
EAvg() - an extended DAvg(). Average TOP or most recent values. Access 2000 and later
Archive: Move records to another table - copy + delete in a transaction Access 95 and later
List files recursively - List files in a folder and subfolders Access 2002 and later
Enabling/Disabling controls, based on User Security berlinda b eras All versions
Multiple return values from a Function - User-defined types Access 95 and later
Copy SQL statement from query to VBA Access 95 and later

Functions (VBA)

MinOfList(), MaxOfList(): Get the min/max of a list of values Access 97 and later
Soundex(): Fuzzy matches - find names that sound alike Access 95 and later
Age(): Calculate a person's age, from date of birth data Access 95 and later
Text2Clipboard(), Clipboard2Text() Copy to and from the Windows Clipboard Access 95 and later
TableInfo(), FieldTypeName(): List the names, types, descriptions of fields in a table Access 95 and later
DirListBox(): Fill a list box with the files in a directory All versions
PlaySound(): Play WAV files in Access events All versions
ELookup() - an extended replacement for DLookup() Access 95 and later
ParseWord(): Parses the first, last, or n-th word/item from a field/list Access 2000 and later
FileExists(), FolderExists(): Determine if a file or folder exists Access 95 and later
ClearList(), SelectAll(): Select or clear all items in a multi-select list box Access 95 and later
DeleteAllRelationships(): Delete all relations in a database. Useful for repairs Access 95 and later
CountLines(): How many lines of code in the current database? Access 2000 and later
InsertAtCursor() - Insert characters at the cursor position Access 95 and later
GoHyperlink() - Handle warnings, special characters, and errors opening hyperlinks Access 2000 and later

Pitfalls and Traps

International Date Formats in Access Access 95 and later
Calculated fields misinterpreted All versions
Common errors with Null Access 95 and later
Traps: Working with Recordsets - 10 common mistakes Access 95 and later
Traps: Working with Variables Access 95 and later
Traps: Logic Errors Access 95 and later
Currency format lost when regional settings change Access 2 and later

Examples by Library

Developer reference section: create, delete, modify, and list the database objects (tables, fields, indexes, relations, queries, databases) and set their properties. Heaps of code, with only limited explanations.

DAO code - 25 examples Access 97 and later
ADO code - 9 examples Access 2000 and later
ADOX code - 21 examples Access 2000 and later
DDL query - 8 examples Access 2000 and later

See also the Index of VBA Functions - list of the code from all pages on this site.

Applications / Utilities

Specific solutions and downloadable tools.

Applications

Find as you type - Filter forms with each keystroke. Sample database. Access 97 and later
Search form - Handle many optional criteria. Sample database Access 2000 and later
Audit Trail - Log changes at the record level Access 95 and later
Inventory Control - Quantity On Hand Access 95 and later
People in households and companies - Modeling human relationships Access 2000 and later Sample database
Clashing Events/Appointments Access 95 and later
Field-level permissions - How to lock controls on a per-field basis Access 2000 and later
Credit Card validation - Verify a credit card number is entered correctly. Sample database. Access 2000 and later
Enter text in calculated controls - Access 2000 and later Sample database
Recurring events - how to handle recurring events that may never end. Access 2000 and later Sample database

Utilities

Splash screen with version info - Sample database Access 97 - 2007
Popup calendar - Sample database with an Access form that works as a calendar Access 97 and later
Printer Selection Utility - Users assign printers to reports Access 2002 and later (including MDE/runtime)
Relationship Report with extended field information Access 2000 and later
Database Issue Checker - Examine an Access database for structural issues Access 2000 and later
Problem names and reserved words - Reference list of 2000+ words from the utility above
Where is a field used? - Search tables, queries, forms, and reports for a field name Access 2000 and later
Renaming fields and tracing dependencies a utility by Chas Dillon Access 2000 and later
Postal/Zip codes - Listings for USA and Australia All versions
Export data to web pages - Utility to output HTML pages from Access tables/queries. Access 2000 and later
Where are macros used in forms or reports? Access 2000 and later

Corruption / Repair

Recovery sequence - Standard steps to recover a database. Access 2000 and later
Recovering from Corruption - Solutions for specific symptoms Access 95 and later
Preventing Corruption - Techniques to avoid corruption Access 2 and later
Solving problems with Library References Access 97 and later
Fixing AutoNumbers when Access assigns negatives or duplicates Access 2000 and later

Flaws in Access

Service packs fix known bugs. To get the latest service pack for your version of Office, visit http://support.microsoft.com/sp or Office 2007 Service Pack 1.

New bugs

Bugs introduced by SP3 for Office 2003 Access 2003
Database may be deleted on compact Access 2007
Converting to Access 2007: What's broken Access 2007

The flaws listed below remain unfixed for at least three versions of Access. You must know how to work around them, or they will bite you. Flaws marked "Sample" are demonstrated in AccessFlaws.zip (Access 2000, 125KB).

If you use queries, be aware!

These can all fail:

PARAMETERS clause
SELECT clause
DISTINCT predicate
FROM clause
WHERE clause
GROUP BY clause
ORDER BY clause

Some of these describe cases where Access fails or crashes.
Worse still, some give wrong answers, with no indication that the results are incorrect.

Failures caused by Name Auto-Correct Access 2000 and later
Incorrect Sorting (Decimal fields) Access 2000 on (partially fixed in 2007) Sample
Records missed by SELECT query Access 2000 and later Sample
Comparison gives wrong result All versions
DISTINCT query handles Nulls inconsistently All versions
Parameter of type Text is evaluated wrongly All versions
Outer join queries fail on Yes/No fields All versions Demo
Trailing spaces give inconsistent query results All versions
Concatenated fields yield garbage in recordset All versions Demo
Grouping by Memo field yields garbage Access 2000 and later Demo
Outer join expressions retrieved wrongly All versions

Interface bugs

Cases where Access displays or handles data wrongly in forms and reports.

Losing data when you close a form All versions Sample
Incorrect filtering of forms and reports Access 95 - 2003 (fixed in 2007) 4 bugs, Samples
Incorrect display of data Access 95 and later Sample
Writing the wrong record - Bookmark bug Access 2 - 2003
Conditional formatting flaws Access 2000 - 2003 Sample

Other bugs

Miscellaneous issues:

Errors using multiple versions of Access under Vista
Problem properties Access 2000 and later

Upgrading

Converting Access versions

Issues you may encounter in changing version:

Converting to Access 2007 - good and bad, configuration and compatibility, runtime and links
Prevent Access 2007 users modifying existing databases Any MDB prior to Access 2007
Converting from Access 97 to 2000 and later
Converting from Access 1 or 2 to Access 95 or 97

Microsoft also has an Access 2.0 Converter for Access 2003.

Converting from the old xBase

If you have a background in the old dBase/Foxbase, you may appreciate:

Contact us

All tips and samples are offered without liability: use at your own risk.

Permission

You may freely use anything (code, forms, algorithms, ...) from these articles and sample databases for any purpose (personal, educational, commercial, resale, ...). All we ask is that you acknowledge this website in your code, with comments such as:

'Source: http://allenbrowne.com 'Adapted from: http://allenbrowne.com

Support

We are not able to give free support for all the free tips, utilities, and solutions in these pages. We will need to charge you if you email asking for support or help in implementing these ideas or completing your database.

For free help, post a question to the Usenet newsgroups. Depending on your configuration, you might reach them with these links:
    comp.databases.ms-access
    microsoft.public.access.*
Alternatively, type those group names as the location in this link:
    http://groups.google.com
or try the Access Database section of the:
    Microsoft Communities
Clear, concise questions usually receive at least one good answer within 24 hours.

Occasionally, Access users offer to send something in gratitude. We prefer you give your donation to a charity that provides education and health for children (such as Compassion.) Then drop us an email so we can share the pleasure of your gift.

To email Allen Browne: