MS ACCESS

GROUP 5
MEMBERS:
-
-
-
-
-
 MS Access
• MS Access is defined as a database computer
program by Microsoft used to organize and use
information for home or business.
• An example of MS Access is a program for keeping
detailed notes about all your business contacts.
 MS Access
• FUNCTIONS
-MS Access has many built-in functions.
-This reference contains such as the:
 string,
 numeric,
 date, and
 information functions in MS Access.
MS Access String Functions

Function Description
Asc Returns the number code that represents the
specific character
Chr Returns the character based on the number
code
Concat with & Concatenates multiple strings together into a
single string with the & operator
CurDir Returns the current path
Format Formats a string expression
InStr Returns the position of the first occurrence of
a string in another string
functions Description
InstrRev Returns the position of the first occurrence
of a string in another string, starting from
the end of the string
LCase Converts a string to lower-case
Left Extracts a substring from a string (starting
from left)
Len Returns the length of the specified string
LTrim Removes leading spaces from a string
Mid Extracts a substring from a string (starting
at any position)
Replace Replaces a sequence of characters in a
string with another set of characters (a
number of times)
Right Extracts a substring from a string (starting
from right)
functions description
RTrim Removes trailing spaces from a string
Space Returns a string with a specified number of
spaces
Split Splits a string into substrings (based on a
delimiter) and returns the result as an array of
substrings
Str Returns a string representation of a number
StrComp Returns an integer value representing the result
of a string comparison
StrConv Returns a converted string
StrReverse Returns a string whose characters are in reverse
order
Trim Removes leading and trailing spaces from a
string
UCase Converts a string to upper-case
MS Access Numeric Functions
function description

Abs Returns the absolute value of a number

Atn Returns the arctangent of a number
Avg Returns the average value of an expression
Cos Returns the cosine of an angle
Count Returns the number of records in a select
query
Exp Returns e raised to the nth power
Fix Returns the integer portion of a number
Format Takes a numeric expression and returns it as
a formatted string
Int Returns the integer portion of a number
functions description
Max Returns the maximum value of an expression
Min Returns the minimum value of an expression
Randomize Allows you to change the seed value used by the
random number generator for the Rnd() function

Rnd Generates a random number

Round Returns a number rounded to a certain number of
decimal places
Sgn Returns the sign of a number
Sqr Returns the square root of a number
Sum Returns the summed value of an expression
Val Accepts a string as input and returns the numbers
found in that string
Hour Returns the hour (from 0 to 23) for a time value

Minute Returns the minute of the hour (from 0 to 59) for a time
value
 MS Access Date Functions

functions description
Date Returns the current system date
DateAdd Returns a date after a time/date interval has
been added
DateDiff Returns the difference between two date
values
DatePart Returns a specified part of a date
DateSerial Returns a date given a year, month, and day
value
DateValue Converts a string to a date
Day Returns the day of the month (from 1 to 31) for
a date
Format Takes a date expression and returns it as a
formatted string
functions description
Month Returns the month (from 1 to 12) for a date
MonthName Returns the monthname given a number from 1 to
12

Now Returns the current system date and time

Second Returns the second of the minute (from 0 to 59) for a
time value

Time Returns the current system time

TimeSerial Returns a time given an hour, minute, and second
value

TimeValue Converts a string to a time

Weekday Returns a number (from 1 to 7) representing the day
of the week for a date

WeekdayName Returns the weekday name given a number from 1

to 7

Year Returns the year (four digits) for a date

MS Access Information Functions

functions description
CurrentUser Returns the name of the current Access user
Environ Return the value of an operating system
environment variable
IsDate Returns TRUE if the expression is a valid
date, otherwise FALSE
IsNull Returns TRUE if the expression is a null
value, otherwise FALSE
IsNumeric Returns TRUE if the expression is a valid
number, otherwise FALSE
UPDATED FEATURES
• There are 4 New and Updated Features in MS Access
2016
• If you have just upgraded your Ms Office application
suite from the 2013 edition to the 2016 edition, you are
likely to feel that Ms Access barely has changed a bit.
While the look and feel remains largely unchanged, there
are several changes in this popular database application
that’s worth a look. Here are a few of those recently
launched features and updates that one can find in the
latest edition of Access.
1. Tell Me Feature – When you open the Ms Access 2016
edition, apart from a slightly colorful interface, you will
also notice a new text box right on the ribbon on top. This
says ‘Tell me what you want to do’ and has an icon of a
bulb right next to it. Whatever task you want to do, you
can write here and will give you a list of all features and
programs that might help you in completing your task.
You can also use this feature for help with anything
regarding the application and it can also help you find a
button you are unable to locate.
1. New Themes – Now you also have the option of changing themes for your Access
program. Two new themes have been introduced in this edition. One is plane
white and other is colorful. You can access these themes by following these steps:
a.)go to file
2.Select options. 4. Go to the Drop down menu right next to
3.click on general. Office Themes.
5. Redesigned Templates – A part from the new themes, Access 2016 also provides you
with new desktop design templates. Five different desktop templates have been,
redesigned and modified to allow you to start working with Access databases with ease.
6. Bigger Show Table Dialog – The size of the Show Table Dialog box has been increased
in Access 2016, using this bigger dialog box, you will now have access to greater number
of queries and tables and databases. You can open this dialogue box in queries through
multiple ways. You also have the option of opening it in Relationship Window.
Many users of Access 2013 edition might find the overall interface to be more or less the
same; the difference will however come in the functionality. Further the changes
introduced in this edition are aimed at giving end users greater control. For example it is
now far easier to export linked data sources from the Access application into an Ms Excel
file. Moreover with SharePoint 2016, a host of Access web app services are now available
for use. Some of the prominent ones include improved datasheet filters and cascading
controls.
• Even the Latest Ms Access 2016 Database is Prone to
Crashes
• While the recent editions of Ms Access are way more
stable than editions released before 2007, they are far from
perfect. Even the latest Ms Access 2016 edition application
is prone to crashes and incidents of corruption of its
underlying database file are quite common. So
irrespective of the edition you are using, you should keep
an Access database repair tool like DataNumen Access
Repair handy to recover from a database crash. This
incisive tool can deal with extensive corruption of a MDB
file and it can assuredly bring back all data stored in it in
quick time.
 Recommended Access Tools
The best COM add-in for the VBA editor I have ever seen and
Mztools
it's FREE! Too many features to list. Be sure to get this one.

Free tools like my favorite Deep Search and Replace which will
V-Tools
go through all objects from tables to code.

Free customizable code indenter. Cleans up formatting with one

SmartIndenter click at procedure, module or project level. Perfect for code
you inherited from a sloppy programmer.

Now here's a tool I use all day long. It is a free online SQL
formatter that is highly customizable. I use it to make Access-
generated SQL statements readable. You can also translate SQL
SQLinForm into a string for other languages like VB (VBA). A desktop utility is
available for a reasonable fee with free annual updates. It has all
the same configurations plus it lets you format any selected
Access SQL right to your clipboard.

FMS Access Tools Although these are not free, they are reliable and easy to use.

If you deal with delimited files a lot you will find this a must-
have freeware. You can read a delimited file in a grid format
CSVed with tons of tools. There is an installed version as well as a
portable one.
Recommended Access Tools
• [Tab] sets focus to the next field.
• [Ctrl]+[Enter] inserts a new line in multi-line controls.
• [Ctrl]+[C] copies the selected text onto the clipboard.
• [Ctrl]+[X] cuts the selected text, and put it on the clipboard.
• [Ctrl]+[V] pastes the clipboard contents.
• [Ctrl]+[F] opens the Find dialog.
• [Ctrl]+[Z] undoes the last action.
• [Ctrl]+[A] selects all text in the current field.
• [Shift]+[F2] opens zoom box to edit the current field in its own, larger, dialog box.
• [Spacebar] toggles the value of a check box or option button.
• [F7] checks spelling.
• [Esc] undoes changes to the current field or the current record (press [Esc] twice to undo
both).
 But there are several lesser-known accelerator shortcut keys that can simplify your typing
and keep you working mouse-free. The lists below include some of the shortcuts that I find
most useful, but Access offers many more shortcuts for you to take advantage of. For a list,
refer to the "Keyboard Shortcuts" topic (online or in the Microsoft Access help file).

Don’t Type When Access Does it For You

• [Ctrl]+[;] inserts the current date.
This is invaluable if you keep historical records, or include the date in any notes or other fields in your
database. It not only saves time, but ensures data accuracy and keeps you from having to remember
the date.
• [Ctrl]+[:] inserts the current time.
Like the Insert Date shortcut, this feature can save more time than you realize, and it also helps to
keep your data accurate.
• [Ctrl]+['] inserts the value from the same field in the previous record.
When you need to enter several records consecutively, you probably find that many of the fields are
the same from record to record. Using the clipboard to copy data can help, but you may need to enter
several fields of duplicate information (your name in one field, the date in another field, and some
generic comments in a third). Use this shortcut to insert the same field's value from the previous
record.
• [Ctrl]+[Alt]+[Spacebar] inserts the default value for the current field.
This comes in handy when you start typing, and then realize that you should have kept the default.
Don’t Use the Mouse When You Can Use the Keyboard
• [Ctrl]+Arrow key combinations speed up navigation
[Ctrl]+[Left/Right Arrow] moves you to the beginning of the last/next word.
[Ctrl]+[Up/Down Arrow] moves you to the beginning of the last/next paragraph.
• [Shift] +Arrow key combinations select text
[Shift]+[Left/Right Arrow] selects one letter.
[Shift]+[Up/Down Arrow] selects one line.
[Ctrl]+ [Shift]+[Left/Right Arrow] selects a word.
[Ctrl]+ [Shift]+[Up/Down Arrow] selects a paragraph.
• [F2] switches between Edit mode and Navigation mode
Edit mode displays the insertion point. Navigation mode hides the insertion point, and selects the
entire field. When in Navigation mode, use the arrow keys to move between fields.
• [F4] opens a combo box or list box
Use this keyboard shortcut to drop down the list, and then use the arrow keys to select a value
from the list.
• [Ctrl]+[+] adds a new record
• [Ctrl]+[-] deletes the current record
• [Shift]+[Enter] saves the current record
Switching records also saves the data, but it’s a good idea to save frequently, especially if you’re
entering a lot of data.
• [Ctrl]+[PgUp] goes to the previous record
• [Ctrl]+[PgDn] goes to the next record
 Database Window and Searching
From anywhere in MS Access:
• [Alt]+[Ctrl]+[F] go to the Search box of the Navigation Pane
• [F11] to toggle (open or hide) the database window
• [Alt]+[F11] switch between VBA module editor and database
• [Ctrl]+[F4] close the currently open object (tabs)
• [Ctrl]+[F6] cycle between open objects (tabs)
From the database window:
• [F2] to rename the current object
• [F12] to save the current object as a new name
Conclusion
• Remembering these shortcuts may seem like a pain, and they may even slow you down at first. If
you make a conscious effort to use them, however, they will become second nature. Once you get
into the practice of using these shortcuts, you’ll wonder how you ever worked productively
without them.
What's the difference between Office 2010 and Office 2007?
• Here we look at the new MS Office 2010 Features and Functions.
• The Ribbon interface has been extended to all applications in Office, most notably
Outlook and Onenote. Users now have the ability to customise the Ribbon.
• The Office button in 2007 has been replaced by a File Menu icon.
• Main features of Office 2010:
1. New picture formatting tools like artistic effects and colour saturation in Word
2010 and Powerpoint 2010 give you more power to express your ideas visually.
2. Co-Authoring documents in real time in Word, Powerpoint, Onenote, and Excel Web
App. Note sharepoint workspace or a windows live account is required.
3. Excel 2010 has a newly introduced Slicers feature for Pivottables allowing you to filter
data on the fly.
4. In addition, Excel's new Sparklines feature allows you view trends in data by adding
an additional column right alongside your data, containing the graphs in each cell:

5. Powerpoint 2010 has the ability to edit and customize videos, you can trim, add fades
and bookmark points in your video. In addition you can record a presentation as a video
and save its as a Windows Media file.
6. Powerpoint 2010 allows you to broadcast a presentation over the internet. The
recipients of the broadcast can view the presentation in a Web browser and do not need
to have Powerpoint installed.
7. Outlook 2010 provides you with a new conversation view which allows you to group
e-mail replies from a single message into a single mail item. The new quick steps feature
in Outlook helps you manage your e-mails more effectively by allowing you to
customise actions for certain e-mails you choose.
8. Outlook 2010 allows you to import contacts from social networks such as facebook or
LinkedIN.
9. The new file menu icon exposes the new backstage view which permeates every
application. The backstage view not only deals with printing and saving but has options
for sharing, versioning, protection and removing metadata.
10. The new backstage view allows you not only to save to local or network PC's but you
can save a document to Sharepoint or Windows Skydrive. (Note in the latter case a
windows live account is required).
11. A new Powerpivot addin is available for Excel 2010. Powerpivot allows you analyse
large volumes of data from a SQL server database.
12. Access 2010 ships with the following new features: web based databases, Application
parts, Quick start fields, conditional formatting and Navigation form controls.
• Access 2007 ceased supporting many less widely used Access features, such as
Data Access Pages (DAP) and user-level security for the new .accdb and .accde
file formats. Access 2010 introduces Access Services and web databases to replace
DAP for browser-based database front ends.