Different types of formats in MS Access are as follows:

  1. Text and Memo format

Diferent formats for text and memo are as follows:

Symbol Explanation
@ It indicates a required character or space
& It indicates an optional character or space
< It converts characters to lowercase
> It converts characters to uppercase
@;”XYZ” It displays the text inside double quotes if the user enters no value

 

Some examples of formats for text and memo are as follows:

Format Datasheet entry display
@@@-@@@@ 1234567 123-4567
@@@-@@@& 123456 123-456
< HELLO hello
> hello HELLO
@/! Hello Hello!
@;”No Data Entered” (blank) No Data Entered

 

  1. Number Format

Different format for numbers are as follows:

Format Explanation
0 0 is a placeholder that displays a digit or 0 if there is none.
# # is a placeholder that displays a digit or nothing if there is none.
% % multiplies the number by 100 and added a percent sign.

 

Different examples of formats for numbers are as follows:

Format Datasheet Entry Display
# # #, # # 0.00 123456.78 123,456.78
$# # #, # #0.00 0 $0.00
# # #.00% .123 12.3%

 

  1. Currency Format

The currency formatting consists of four parts separated by semicolons. These parts are format for positive numbers, format for negative numbers, format for zero values and format for Null values.

Format Explanation
$# #0.00;($# #0.00)[Red];$0.00;”none” Positive values will be normal currency format, negative numbers will be red in parentheses, zero is entered for zero values, and “none” will be written for Null values.

 

  1. Date Forma

The easiest way to apply a format is to select from drop-down list as follows:

General dateLong date

Medium date

Short date

Long Time

Medium Time

Short Time

06/19/94 5:34:23pmJune 19, 1994

19-Jun-94

06/19/94

5:34:23PM

5:34 PM

17:34

The user cans also formats the data according to his particular requirements. Different date formats are as follows:

Format Explanation
d It displays 1 or 2 characters for day. Its value can be from 1 to 31.
dd It displays 2 characters for day. Its value can be from 01 to 31 such as 01.
m or mm It displays month as a number. Its value can be from 1 to 12.
mmm It displays month using three characters such as Jan, Feb etc.
mmmm It displays full nam of the month such as January, March etc.
/- It displays separator character.
h It displays hours.
n It displays minutes.
s It displayed seconds.

 

Some examples of data formats are as follows:

Format Display
dddd “,” mmmm d “,” yyyy Monday, January 1, 2001
ddd “,” mmm “.” d “,”yy Mon, Jan. 1, ‘01
“Today is” dddd Today is Monday
H:n:s: AM/PM 12L00:00 AM
  1. Yes/No Format

The Yes/No fields are displayed as check boxes by default on the datasheet. It can be changed to textbox by clicking Lookup tab and changing Display Control to a textbox. The formatting is designated in three sections separated by semicolons. The first section contains nothing but semicolon must be included. The second section specifies formatting for Yes values and third for No values as Follows:

Format Explanation
;”Yes”[green];”no”[red] Prints “Yes” in green or “no” in red