Functions - Category

MS Access: Functions - Category

Below is a list of the most commonly used functions in Access. These functions can generally be used in either VBA code or SQL/Queries.

String Functions

Asc Chr Concat with & Format (with Strings) Instr InstrRev LCase

Left Len LTrim Mid Replace Right RTrim

Space Str StrConv Trim UCase

Advanced Functions

Case Choose CurrentUser First

iif IF-THEN-ELSE IsDate IsNull

IsNumeric Last Nz Switch

Domain Aggregate Functions

DAvg DCount DFirst

DLast DLookup DMax

DMin DSum

Numeric/Mathematical Functions

Abs Atn Avg Cos Count Exp

Format (with Numbers) Fix Int Max Min Rnd (random number)

Round Sgn Sum Val

Date Functions

Date DateAdd DateDiff DatePart DateSerial DateValue Day

Format (with Dates) Hour Minute Month MonthName Now Second

Time TimeSerial TimeValue Weekday WeekdayName Year

Data Type Conversion Functions

CBool CByte CCur CDate

CDbl CDec CInt CLng

CSng CStr CVar

Functions used by Group By Feature in queries

Avg Count

Max Min

Sum

File/Directory Functions

ChDir ChDrive CurDir

Dir FileDateTime FileLen

GetAttr MkDir SetAttr

Miscellaneous Functions

• Test a string for an alphanumeric value
• Convert currency into words
• Creating a custom round function
• Retrieve the user name from Windows while in an Access database

Functions - Alphabetical

S Access: Functions - Alphabetical

Below is a list of the most commonly used functions in Microsoft Access. These functions can generally be used in either VBA code or SQL/Queries.

Alphabetical Listing of Functions

Abs Asc Atn Avg Case CBool CByte CCur CDate CDbl CDec ChDir ChDrive Choose Chr CInt CLng Concat with & Cos Count CSng CStr CurDir CurrentUser CVar Date DateAdd DateDiff DatePart DateSerial DateValue DAvg

Day DCount DFirst Dir DLast DLookup DMax DMin DSum Exp FileDateTime FileLen First Format (with Dates) Format (with Numbers) Format (with Strings) Fix GetAttr Hour iif IF-THEN-ELSE Instr InstrRev Int IsDate IsNull IsNumeric Last LCase Left Len LTrim

Max Mid Min Minute MkDir Month MonthName Now Nz Replace Right Rnd (random number) Round RTrim Second SetAttr Sgn Space Str StrConv Sum Switch Time TimeSerial TimeValue Trim UCase Val Weekday WeekdayName Year

Miscellaneous Functions

Test a string for an alphanumeric value Convert currency into words Creating a custom round function Retrieve the user name from Windows while in an Access database

Naming Variables (Programming Standards)

MS Access: Naming Variables (Programming Standards)

We try to prefix all variables with a letter indicating the scope of the variable. This makes it easier to figure out where the variable has been declared.

Here is a quick summary of the naming conventions that we use:

Scope	Example	Explanation
Global	GClient_ID	All global variables should be prefixed with "G".
Local	LClient_ID	All local variables should be prefixed with "L".
Parameter	pClient_ID	All parameters should be prefixed with "p".

Global Variables

What is a Global variable?

A Global variable is one that can be accessed from anywhere in your program. We try to prefix all Global variables with "G".

For example, you could create a global variable called GClient_ID with the following command:

Global GClient_ID As Integer

Global variables are generally declared at the top (ie: Declarations section) of a module.

Local Variables

What is a Local variable?

A Local variable is one that exists only within the current function or subroutine. We try to prefix all Local variables with "L".

For example, you could create a local variable called LTotalDays with the following command:

Dim LTotalDays As Integer

Local variables are generally declared at the top (ie: Declarations section) of a function or subroutine.

Below is an example of a function:

Function Example1() As Integer

   Dim LTotalDays As Integer
   Dim LSaturdays As Integer
   Dim LSundays As Integer
   
   LTotalDays = DateDiff("d", StartDate, EndDate)
   LSaturdays = DateDiff("ww", StartDate, EndDate, 7)
   LSundays = DateDiff("ww", StartDate, EndDate, 1)
   
   'Elapsed days excluding Saturdays and Sundays
   Example1 = LTotalDays - LSaturdays - LSundays
   
End Function

In this example, we've created three local variables - LTotalDays, LSaturdays, and LSundays.

Parameters

What is a Parameter?

A Parameter is a variable that is used to pass information to or from a function or subroutine. We try to prefix all parameters with "p".

For example, we could create parameters in a function as follows:

Function Example2(pStartDate As Date, pEndDate As Date) As Integer

   Dim LTotalDays As Integer
   Dim LSaturdays As Integer
   Dim LSundays As Integer
   
   LTotalDays = DateDiff("d", pStartDate, pEndDate)
   LSaturdays = DateDiff("ww", pStartDate, pEndDate, 7)
   LSundays = DateDiff("ww", pStartDate, pEndDate, 1)
   
   'Elapsed days excluding Saturdays and Sundays
   Example2 = LTotalDays - LSaturdays - LSundays
   
End Function

In this example, we've created a function with two parameters - pStartDate and pEndDate.

You could also create parameters in a subroutine as follows:

Sub Example3(pStartDate As Date, pEndDate As Date)

   Dim LTotalDays As Integer
   
   LTotalDays = DateDiff("d", pStartDate, pEndDate)
   
   'Elapsed days
   MsgBox "There are " & LTotalDays & " elapsed days."
   
End Sub

In this example, we've created a subroutine with two parameters - pStartDate and pEndDate.

Setting up Tables and Naming Fields (Programming Standards)

MS Access: Setting up Tables and Naming Fields (Programming Standards)

Here are a few of the programming standards that we try to follow when setting up tables and defining fields within those tables.

Setting up Tables

Guideline #1: Create tables with meaningful names. For example, if you want to create a table that stored information about your suppliers, then name the table Suppliers.

Guideline #2: Even though Access allows spaces in your table names, you may want to consider using the underscore character ("_") instead of a space. This will make it easier for you in the long run if you decide to port your tables to a different database. Most other databases do not allow spaces in table names.

For example, if you wanted to create a table that stored your order details, instead of calling the table Order Details, you might want to consider naming the table Order_Details.

Guideline #3: Use only alphanumeric characters when naming your tables. Do not use characters such as colons, semi-colons, brackets, etc.

Guideline #4: Try to create a primary key on your tables. A primary key is what uniquely defines each record in a table. If you do not have a combination of fields in your table that uniquely defines a record, then consider creating an AutoNumber field that acts as your primary key.

In the example below, we've created an AutoNumber field called Supplier_ID that acts as a primary key in our Suppliers table.

Naming Fields

The programming standards that we use for naming fields are very similar to those for naming tables.

Guideline #1: Create field names that are meaningful. Do not name your field names - Field1, Field2, Field3, etc., but choose names that identify what kind of information will be stored in the fields.

Guideline #2: Even though Access allows spaces in your field names, you may want to consider using the underscore character ("_") instead of a space. This will make it easier for you in the long run if you decide to port your tables to a different database. Most other databases do not allow spaces in field names.

For example, if you wanted to set up a field that stored the company name, instead of calling the field Company Name, you might want to consider naming the field Company_Name.

Guideline #3: Use only alphanumeric characters when naming your fields. Do not use characters such as colons, sem-colons, brackets, etc.

Shortcut Keys for Entering Data in a Datasheet/Form

MS Access: Shortcut Keys for Entering Data in a Datasheet/Form

The following is a list of shortcuts or hot keys for entering data in a Datasheet/Form:

Key Sequence	Description
CTRL+PLUS SIGN (+)	Add a new record.
CTRL+MINUS SIGN (-)	Delete the current record.
CTRL+SEMICOLON (;)	Insert the current date.
CTRL+COLON (:)	Insert the current time.
CTRL+ALT+SPACEBAR	Insert the default value for the field.
CTRL+ENTER	Insert a new line.
SHIFT+ENTER	Save changes to the current record.
SPACEBAR	Toggle between the values in a check box or radio button.

Shortcut Keys for Working with Text/Data

MS Access: Shortcut Keys for Working with Text/Data

The following is a list of shortcuts or hot keys for working with text/data in Access:

General

Key Sequence	Description
F7	Check spelling.
CTRL+C	Copy the selection to the Clipboard.
CTRL+V	Paste the contents of the Clipboard to where the insertion point is.
CTRL+X	Cut the selection and copy it to the Clipboard.
CTRL+Z	Undo typing.
CTRL+DELETE	Delete all characters to the right of the insertion point.
CTRL+SEMICOLON (;)	Insert the current date.
CTRL+COLON (:)	Insert the current time.
CTRL+ALT+SPACEBAR	Insert the default value for the field.
ESC	Undo changes in the current field. Press ESC a second time to undo changes to the current record.
SHIFT+F2	Open the Zoom box to enter large amounts of data that can not be properly displayed in a small control.

Finding and replacing text

Key Sequence	Description
CTRL+F	Open the Find tab on the Find and Replace dialog box.
CTRL+H	Open the Replace tab on the Find and Replace dialog box.
SHIFT+F4	Find the next occurrence of the value entered in the Find and Replace dialog box when the dialog box is closed.

Selecting text in a field

Key Sequence	Description
SHIFT+RIGHT ARROW	Extends selection one character to the right.
SHIFT+LEFT ARROW	Extends selection one character to the left.
CTRL+SHIFT+RIGHT ARROW	Extends selection one word to the right.
CTRL+SHIFT+LEFT ARROW	Extends selection one word to the left.

Moving the insertion point in a field

Key Sequence	Description
RIGHT ARROW	Move one character to the right.
LEFT ARROW	Move one character to the left.
CTRL+RIGHT ARROW	Move one word to the right.
CTRL+LEFT ARROW	Move one word to the left.
END	Move to the end of the field, in a single-line field.
HOME	Move to the beginning of the field, in a single-line field.
CTRL+END	Move to the end of the field, in a multi-line field.
CTRL+HOME	Move to the beginning of the field, in a multi-line field.

General Shortcut Keys

MS Access: General Shortcut Keys

The following is a list of general shortcuts or hot keys in Access:

Key Sequence	Description
F1	Display the Microsoft Access Help. This may be context-sensitive help depending on what you are positioned on.
F11	Display the Database window.
F12	Open the Save As dialog box.
CTRL+N	Open a new database.
CTRL+O	Open an existing database.
CTRL+P	Print the current or selected object.
CTRL+S	Save the current database object.
CTRL+W	Close the active window.
ALT+SPACEBAR	Display the Control menu.
ALT+F11	Toggle between the Visual Basic editor and the Access Database window.
SHIFT+F10	Display the shortcut menu (ie: popup menu).

MS Access: Date/Time

Tables

• Define a custom format for a Date/Time field in a table in Access 2007 | Access 2003/XP/2000/97

Queries

• Query to retrieve max date in Access 2007 | Access 2003/XP/2000/97

Forms/Subforms

• Popup calendar in a form/subform in Access 2007 | Access 2003/XP/2000/97
• Define a custom format for a Date/Time field on a form in Access 2007 | Access 2003/XP/2000/97
• Set up a text box to display a date value when another field has a value in Access 2007 | Access 2003/XP/2000/97

Reports

• Using WeekdayName function in a report in Access 2003/XP/2000/97

Formatting

• Set up a text box to flash red/black when a date is past due in Access 2007 | Access 2003/XP/2000/97

Miscellaneous

• Calculate and format an elapsed time value in Access 2003/XP/2000/97
• Convert a date to a numeric value in Access 2003/XP/2000/97
• Calculate the number of workdays between two dates in Access 2003/XP/2000/97
• Set up a text box to display the difference between two dates in Access 2007 | Access 2003/XP/2000/97

Switchboard

MS Access: Switchboard

A switchboard is a form that allows you to navigate around your Access database. The switchboard is made of buttons that you click. You can program these buttons to open forms, reports, queries, etc.

Microsoft provides a switchboard wizard that allows you to define the look and feel of your switchboard.

Basics

• Create a switchboard in Access 2007 | Access 2003/XP/2000/97
• Add a new item to the switchboard in Access 2007 | Access 2003/XP/2000/97
• Automatically start up switchboard when database is opened in Access 2007 | Access 2003/XP/2000/97
• Create more than 8 switchboard items in Access 2003/XP/2000/97
• Open the switchboard maximized (full screen) in Access 2003/XP/2000/97
• Create a switchboard item that closes down Access 2007 | Access 2003/XP/2000/97
• Create a switchboard item that opens a report criteria Form in Access 2007 | Access 2003/XP/2000/97
• Delete/remove the switchboard in Access 2007 | Access 2003/XP/2000/97
• Add a Preview Reports page to the switchboard in Access 2007 | Access 2003/XP/2000/97