MS Access Bonus Stuff

Important Legal NOTICE!

Click Here To Read Important Legal NOTICE!

Special Keys
- F11 or Alt F1 = Displays database screen
- CTRL and F11 = switch between custom menu and default menus
- CTRL and + = Create a new record
- CTRL and – = Delete Current record
- CTRL and ; (Semicolon) = paste today’s date in a field
- CTRL and : (Colon) = paste current time in a field
- CTRL ‘ = copying content from above
- CTRL PgUp /PgDn = Go left or right on screen
- Shift and Tab = move left one column/field
- CTRL and Home = First column first row
- CTRL and End = Last column last row
- CTRL and ALT and Spacebar = replace current field with default
- Shift and Enter = Save Record
- Ctrl and Enter = New line in a memo field
- F5 = Go to Record number box at bottom
- F2 = Select all of current field
- ESC = Undo changes in current record

Terminology
- Database - collection of data and objects, you can only work with ONE database at a time
- Table - group of related/similar records (data)
- Field - certain type of data, category, columns
- Record - group of related fields, row
- Value - data in a field
- Relational Database - One record relates to many other records (so you don’t have to retype data)
- Query - question for extracting information
- Form - interface between users and database
- Reports - let you display and print information
- Macros - easy to use automated tasks
- Modules - custom programming using VBA
- SQL - Structured Query Language
- ODBC - Open database connectivity

7 Steps To A Better Database
1. Blue Print It (PLAN)
2. Create your Tables & Fields (& field properties)
3. Enter in some sample data
4. Create Relationships & Look-Ups
5. Create Forms
6. Create Queries & Reports
7. Create Macros & Menus

Fields

Type	Description	Code
Text	Combination of text & numbers / No calculations max. 255 Characters	T
Memo	Combination of long text & numbers 64,000 char.	M
Number	Numbers used in calculations	N
Date/Time	Date and Time	D
Currency	Monetary values used in calculations	C
AutoNum	A unique sequential number	A
Yes/No	Can only Yes or No / True or False / On or Off	Y
OLE Object	An object like a Picture	O
HyperLink	Link to a file, object or Web Page	K
Lookup	Choose a value from another table	L
PrimaryKey	Primary Key	P

Field Names
- Less than 69 AlphaNumeric Characters
- You Can Not use . ! ] `
- Can not start with a Space
- Should not use Spaces @ # $ %^&*( ) - + = < > ?/ \
- IF you change the field name you must also change it in all related calculations, forms, queries, and reports
- When you delete a field, you delete all data in it (you can undo if you have not saved changes!)

Input Mask
Phone Number = (999)” “000-0000
0 = 0-9 numbers, + and - not allowed
9 = numbers 0-9 or space, + and - not allowed, not required
# = numbers 0-9 or space, + and - allowed, not required
L = Letters a-z, entry required
? = Letters a-z, entry optional
A = Letter or number, entry required
a = Letter or number, entry optional
& = Any character or space, entry required
C = Any character or space, entry optional
< = Converts all letters to lower case
> = Converts all letters to upper case
! = Caused input to fill right to left
\ = the character that follows is a literal character

Mathematical order of operations
6 + 8 * 2 = ?what?
Answer is 28 or 22
B E D M A S
B = ( ) Brackets
E = 28 Exponents
D = / Division
M = * Multiplication
A = + Addition
S = - Subtraction

Data Validation
Between #1/1/2000 and #6/23/2003#
Pound sign # on either side of the date
Or Between #1/1/2000# and date()
ie. From 1/1/2000 and today
Or
InStr(“Male”,”Femaale”,”Unknown”) = must be one of these three

Tables

In datasheet table view you can hide columns so you can print only certain information by
Format menu Hide Column then to show them select surrounding columns and use Format menu Unhide Column
Primary Key should be made as short as possible otherwise it will slow you down
Create an index on a field if you find or sort by that field
To many indexes slows down your database
Table analyzer looks at the data in your table to see if it needs to be split off into a relationship
If the data or file in a linked object moves you will get an error at database start up and not be able to use the linked data

Queries and Criteria
To find all cats you could type cat or = cat or ”cat” or = ”cat” to find cats orNot cat to find else then cats
? means a single character
* means any number of characters
# means a single number
<>; means not equal to
<= means less than or equal to
>= means greater than or equal to
Can not say Is <> Null must say Not Is Null
OR expression ie. [LastName] = “Shea” or . [LastName] = “Smith]
If you place test in square brackets that is not a field Access will ask you the question before it runs the query
ie. [What Company would you like?]

Concatenate (put two strings together) you would use the ampersand &
ie. C2&” “&D2&” “&E2
If you use this in a query then you can use it in forms and reports with out having to recreate it all the time
At the beginning of the expression it will have Expr1:
Change the Expr1 to a name for the field

Functions
IIF(YourQuestion , DoIfTrue , DoIf False) ie. IIF([Income]>100000,”Rich”,”Still Trying”)
IN(Value1,Value2,Value3) as many values as you want ie. IN(“Male”,”Female”,”Unknown”) values separated by commas
Now() returns today’s date and time
RND() returns a random number
Ucase([field]) returns Upper case of a field
Lcase([field]) returns Lower case of a field
Round([NumberField],DecimalPlaces) ie. Round([Tax],2) this rounds your tax to two decimal Places
AVG([field]) returns the average of a list of records on a field in a report
Sum([field]) returns the Sum of a list of records on a field in a report
Right([field],Places) Returns the left most characters based on the number of places
Left([field],Places) Returns the left most characters based on the number of places
Len([field]) Returns the number of characters in a field

Forms

When you drag your own fields on to a form you can use the shift key to select contiguous fields or CTRL for random fields
Double click the table name to select all fields in that table to drag them all in
Select * in the table field select widow to show all fields
View menu >> Tab Order OR right click any control as select Tab Order
Right click an object choose Special Effects make it look 3D
Use an unbound picture so it uses less computer resources

Easter Egg
Access 2000 Magic Eight Ball
1. Either create a new database, or use an existing one.
2. Create a new Macro.
3. Without entering any commands, save the macro with the name: Magic Eight Ball (case insensitive)
4. Close the macro.
5. Now, drag the macro you just created up to the toolbar. It will create an Icon that looks like a magic eight ball.
6. Ask yourself a question and click on it!

Mosaic Puzzle Credits
In any database create tables and name one A, the next CC,
the next E, and the last one SS. Go to the about box from
the Help drop down), and right double click the key logo
while holding down the ctrl and shift keys - have fun!!

Crazy Car Chase!!!!
1) Open up Microsoft Access 2000
2) Open up a new database, saving it as crazy cars
3) Create a new table in Datasheet view (you start out with just 22 rows)
4) Position your cursor on the first row, first column
5) Press the space bar, then down arrow key, keep going on to the end of the table
6) You will eventually get to row 23, keep on creating new rows this way until you get to 57
7) Go to row 57 and tab along to field 3
8) Type in cars4me (no spaces)
9) Go to field (column) 3 and type cars4me
10) Highlight the word
11) Hold control shift and Alt
12) Open up help/about
8) Still holding Ctl shift Alt left click on the key on the left making sure it is the bit overlapping the access icon
9) Sit back and enjoy!
10) Use 'm' to go left. 'Z' to go right and left and the arrow keys to go forward and back.

Backup Access database without quiting (2003)
Before Office 2003, backing up was tedious. You had to close the database, use a backup and restore utility to copy it to a safe place.
Access 2003's backup database feature does it with no need to close the database and leave Access.
- select File menu
- then Back Up Database
- notice that the current date is appended to the original filename.

www.kayodeok.btinternet.co.uk Resources For MS Access
www.databasecorner.com

Back To Main Page Important Legal NOTICE!

Click Here To Read Important Legal NOTICE!

Updated October 10, 2007
Copyright� 2007 by Dana Shea
Copyright� All rights Reserved. No part of the contents of this site may be reproduced
or transmitted in any form or by any means without written permission.

Any questions or problem links please email