MS Access 2007 Step By Step - Asim Abbasi
CH1: Overview | CH2: MS Access Local & Linked Tables | CH3: MS Access Queries | CH4: MS Access Reports & Forms | CH5: MS Access Modules & Macros | CH6: MS Access Data Import/Export Feature | CH7: MS Access Built-In Functions
Chapter 7: MS Access Built-In Functions
Example: Finding Years of Service
Figure 7-7: Building years of service (YOS) derived field.
________________________________________
Let’s now look at how we will build the years of service derived
field/column. Over here we are presuming that you have already gone
through the 3 steps mentioned before this slide.
If you know the function name then there is no need to locate it in the
list. The list of functions basically provides the proper syntax of the
function. If you know already then just write it in the text area as
depicted in Figure 7-7 with label 1 and click the <OK> button as shown
with label 2.
Date() function provides the current system date and subtracting
HIRE_DATE will give us the total number of days of service. Dividing
number of days of service with 365 will give you years of service.
Finally we have used the Int() function to get the integer part of the
years of service. YOS is the alias for the column as shown with label 3
in Figure 7-7. Once you run this query you will see this column name as
YOS as shown with label 4 in Figure 7-7. Finally click the <OK> button
and you will be back to your query builder window with the derived
column as YOS.
Upon execution of this query you will see only the derived column name
i.e. YOS and not the expression. This has been depicted with the label 4
in the Figure 7-7.
Chapter 7: MS Access Built-In Functions
CH1: Overview | CH2: MS Access Local & Linked Tables | CH3: MS Access Queries | CH4: MS Access Reports & Forms | CH5: MS Access Modules & Macros | CH6: MS Access Data Import/Export Feature | CH7: MS Access Built-In Functions
Share with others: