EXTRA NOTES DATES in Formulas

ACCESS FUNCTIONS

Asc Function
Asc ( string ) / /
In Access, the Asc function returns an integer representing the ANSI code corresponding to the first character in a string.
The required string argument is any valid string expression. If the string contains no characters, a run-time error occurs. If more than one character is entered, the function will return the value for the first character and ignore all of the characters after the first.
Example
Asc ( "a" ) returns 97
Asc ( "A" ) returns 65
Asc ( "apple" )returns 97
Abs Function
Abs( number ) / /
In Access, the Abs funtion returns the absolute value of a number.
The argument number can be any valid numeric expression. If number contains Null, Null is returned. The absolute value of a number is its unsigned magnitude. For example, ABS(-1) and ABS(1) both return 1.
Example
Abs (-1.45) returns 1,45
Abs (2.17) returns 2,17
Atn Function Atn( number ) / /
In Access, the Atn function returns a double containing the arctangent of a number.
The required number argument can be any valid numeric expression. The Atn function takes the ratio of two sides of a right triangle (number) and returns the corresponding angle in radians. The ratio is the length of the side opposite the angle divided by the length of the side adjacent to the angle.
Example
Atn (15) returns 1,50422816301907
Atn (21) returns 1,52321322351791
Avg function / /
In Access, the Avg function calculates the arithmetic mean of a set of values in a select query.
Avg ( expression )
Choose Function
Choose(index, choice-1[,choice-2, ... [, choice-n]] ) / /
In Access, the Choose function selects and returns a value from a list of arguments based on a given position.
You can use Choose to look up a value in a list of possibilities. If index is 1, Choose returns the first choice in the list; if index is 2, it returns the second choice, and so on. The Choose function returns a Null if index is less than 1 or greater than the number of choices listed.
indexis position number in the list of values to return.
choice is variant expression containing one of the possible choices.
Example
Choose(1, "Have", "a", "nice", "day") returns "Have"
Choose(3, "Have", "a", "nice", "day") returns "nice"
Choose(4, "Have", "a", "nice", "day") returns "day"
Choose(7, "Have", "a", "nice", "day") returns NULL
Choose(2.87, "Have", "a", "nice", "day") returns "a"
Chr Function Chr ( charcode ) / /
In Access the Chr function returns the character associated with the specified ANSI code.
The required charcode argument is the NUMBER used to retrieve the character. The argument charcode is an integer between 0 and 255, inclusive. Applications for Microsoft Windows use the ANSI character set. ANSI character codes in the range 0 to 31, inclusive, are the same as the standard, nonprintable ASCII codes. For example, Chr(13) returns a carriage-return character, and Chr(10) returns a linefeed character. Together they can be used to force a new line when message strings are formatted with MsgBox or InputBox.
Example
Chr( 65 ) returns "A"
Chr( 97 ) returns "a"
Cos Function Cos ( number ) / /
In Access, the Cos function returns a double containing the cosine of an angle.
The syntax for the Cos function is:
The argument number can be any valid numeric expression that expresses an angle in radians.The Cos function takes an angle and returns the ratio of two sides of a right triangle. The ratio is the length of the side adjacent to the angle divided by the length of the hypotenuse.
Cos(15) returns -0,759687912858821
Cos (2) returns -0,416146836547142
Count Function Count ( expression ) / /
In Access, the Count function calculates the number of records in a select query.
The required expression argument represents a string expression identifying the field that contains the data you want to count or an expression that performs a calculation using the data in the field. Operands in expression can include the name of a table field or function (not other SQL aggregate functions). You can count any kind of data, including text. The Count function doesn't count records that have Null fields unless expression is the asterisk (*) wildcard character. If you use an asterisk, Count calculates the total number of records, including those that contain Null fields.
.
CurDir Function CurDir ( drive ) / /
In Access, the CurDir function returns a string containing the full path of the specified drive.
The optional drive argument is a string expression that specifies an existing drive. If this parameter is omitted or it is a zero-length string (""), CurDir returns the path for the current drive.
Example
CurDir () returns "C:\Documents and Settings\UserName\My Documents"
CurDir( "H" ) returns "H:\"
Date Function Date ( ) / /
In Access, the Date function returns the current system date.
Date returns a variant (date) containing a date stored internally as a Double.
Examples
Date() returns a value such as '05.05.2005'
VBA Code
Dim MyDate
MyDate = Date
Now the MyDate variable would contain the current system date.
DateAdd Function
DateAdd( interval, number, date ) / /
In Access, the DateAdd function Adds a specified time interval to a date.
You can use the DateAdd function to add a specified time interval to or subtract a specified time interval from a date. For example, you can use DateAdd to calculate a date 15 days from today or a time that is 30 minutes from now.
interval is the interval of time you want to add to date. The following is a list of valid interval values.
Interval / Description
yyyy / Year
q / Quarter
m / Month
y / Day of year
w / Weekday
ww / Week
h / Hour
n / Minute
s / Second
number is the number of intervals you want to add to date. It can be positive (to get dates in the future) or negative (to get dates in the past).
dateis the date to which the interval is added.
Examples
DateAdd ('m', 4, #01/22/2004# ) returns 05/22/2004
DateAdd ('yyyy',3, #11/10/2003# ) returns 11/10/2006

EXTRA NOTES DATES in formulas

To subtract simple dates:

1912 can be written as 12 2012 can be written as 112

EXCEL / ACCESS
=”1/31/11”-“1/31/10”
Simple dates / =#1/31/11#-#1/31/10#
Simple Dates
=Now()-“12/1/2001”
Subtracts 12/1/2001 from current date / =Date()-#12/1/2001#
Subtracts 12/1/2001 from current date
DATE function (turns date to serial #) / DATESERIAL function (turns date to serial #)
=NOW() returns current date / =DATE() returns current date

DATES in formulas

To subtract date field from date: (Date field must be formatted as date and not text)

ACCESS #1/31/11#-[Date of Entry]

To get answer in years:

#1/31/11#-[Date of Entry]/365.25

To get answer to return in whole numbers:

INT(#1/31/11#-[Date of Entry]/365.25)

To convert a date to serial #

Format date as General Number

YEAR – RETURNS # OF YEARS

=year(40178)-year(F2)(In F2 appears: 2/7/1996 then it returns 1996)

DateDiff Function
DateDiff( interval, date1, date2[, firstdayofweek[, firstweekofyear]]) / /
In Access, the DateDiff function returns the number of time intervals between two dates.
You can use the DateDiff function to determine how many time intervals exist between two dates. For example, you can use DateDiff to calculate the number of days between an order date and its shipdate or the number of weeks between today and the end of the year.
interval is the interval of time to use to calculate the difference between date1 and date2. The following is a list of valid interval values.
Interval / Description
yyyy / Year
q / Quarter
m / Month
y / Day of year
w / Weekday
ww / Week
h / Hour
n / Minute
s / Second
date1, date 2 are the two dates to calculate you want to use in the calculation.
firstdayofweekis optional argument that specifies the first day of the week. If not specified, Sunday is assumed. If not specified, the first week is assumed to be the week in which Jan 1 occurs. The following is a list of valid parameters for firstweekofyear.
Constant / Value / Description
vbUseSystem / 0 / Use the NLS API settings
vbSunday / 1 / Sunday (default used)
vbMonday / 2 / Monday
vbTuesday / 3 / Tuesday
vbWednesday / 4 / Wednesday
vbThursday / 5 / Thursday
vbFriday / 6 / Friday
vbSaturday / 7 / Saturday
firstweekofyearis optional argument that specifies the first week of the year. The following is a list of valid parameters for firstweekday.
Constant / Value / Description
vbUseSystem / 0 / Use the NLS API settings
vbFirstJan1 / 1 / Start withthe first week in which January 1 occurs (default).
vbFirstFourDays / 2 / Start with thefirst week in the year that has at least 4 days
vbFirstFullWeek / 3 / Start with thefirst full week of the year
Examples
DateDiff ('m', #12/17/2003#, #01/22/2004# ) returns 1
DateDiff ('yyyy', #11/05/2001#, #11/10/2003# ) returns 2
DatePart Function
DatePart( interval,date[,firstdayofweek[,firstweekofyear]]) / /
In Access, the DatePart function returns the specified part of a given date.
You can use the DatePart function to inspect a date and return a specific interval of time. For example, you can use DatePart to calculate the day of the week for an order’s ship date or the current hour.
interval is the interval of time you use to return. The following is a list of valid interval values.
Interval / Description
yyyy / Year
q / Quarter
m / Month
y / Day of year
w / Weekday
ww / Week
h / Hour
n / Minute
s / Second
date is the date value that you wish to evaluate.
firstdayofweekis optional argument that specifies the first day of the week. If not specified, Sunday is assumed. The following is a list of valid parameters for firstdayofweek.
Constant / Value / Description
vbUseSystem / 0 / Use the NLS API settings
vbSunday / 1 / Sunday (default used)
vbMonday / 2 / Monday
vbTuesday / 3 / Tuesday
vbWednesday / 4 / Wednesday
vbThursday / 5 / Thursday
vbFriday / 6 / Friday
vbSaturday / 7 / Saturday
firstweekofyearis optional argument that specifies the first week of the year.If not specified, the first week is assumed to be the week in which Jan 1 occurs. The following is a list of valid parameters for firstweekofyear.
Constant / Value / Description
vbUseSystem / 0 / Use the NLS API settings
vbFirstJan1 / 1 / Start withthe first week in which January 1 occurs (default).
vbFirstFourDays / 2 / Start with thefirst week in the year that has at least 4 days
vbFirstFullWeek / 3 / Start with thefirst full week of the year
Examples
DatePart ('m', #12/17/2003# ) returns 12
DatePart ('yyyy', #11/05/2001# ) returns 2001
DatePart ('d', #11/05/2005#) returns 5
DateSerial Function
DateSerial( year, month, day) / /
In Access, the DateSerial function returns the date for a specific year, month, and day.
The DateSerial function returns a date that is stored internally as a double-precision number. This number represents a date from January 1, 100 through December 31, 9999. If the date specified by the three arguments, either directly or by expression, falls outside the acceptable range of dates, an error occurs.
yearis a number between 100 and 9999, inclusive, that represents the year value of the date.
monthis a number between1 and12, inclusive, that represents the month value of the date.
dayis a number between1 and31, inclusive, that represents theday value of the date.
Examples
DateSerial(2004,3,24 ) returns 03/24/2004
DateSerial(1998,7-5,17 ) returns 02/17/1998
DateSerial(2000,5,24-9 ) returns 05/15/200
DateValue Function
DateValue( string ) / /
In Access, the DateValue function returns the date represented by the date of a String argument.
The argument string is a String representing a date from January 1, 100 through December 31, 9999. It can include a time component, if desired. If string includes only numbers, DateValue recognizes the order for month, day, and year according to the Date Format setting in the International section of the Microsoft Windows Control Panel. If the year part of string is omitted, DateValue uses the current year from your computer’s system date. If string includes time information, DateValue does not return it.
Examples
DateValue("May 5") returns 05/05/2005
DateValue ("May 5,1985") returns 05/05/1985
DateValue ("11/22/93") returns 11/22/1993
Day Function
Day(date ) / /
In Access, the Day function returns an integer between 1 and 31, inclusive, that represents the day of the month corresponding to the date provided as an argument.
The required time argument is any numericor string expression, that can represent a date. If number is Null, this function returns a Null.
Examples
Day(#05/05/1985#) returns 5
Day (#17/07/2005#) returns 17
Exp Function
Exp( number ) / /
In Access, the Exp function returns a double specifying eraised to the nth power. The constant e is approximately 2.718282.
The syntax for theExp function is:
The argument number can be any valid numeric expression.If the value of number exceeds 709.782712893, an error occurs.
Exp(3.1) returns 22.197951281
Exp(-2) returns 0.1353352832
Exp(2) returns 7,3890560989
FileDateTime Function
FileDateTime( pathname ) / /
In Access, the FileDateTime function returns the date and timewhen a file was created or last modified.:
The required pathname argument is a string expression that specifies a file name. The pathname may include the directory or folder, and the drive.
Example
FileDateTime ("C:\examples.mdb") returns 25/05/2006 8:18:18 PM
FileDateTime ("H:\Docs\order.xls") returns 10/10/2000 10:10:10 PM
First Function
First( expression ) / /
In Access, the First function return a field value from the first record in the result set of a query.
The syntax for theFirst function is:
The required expression argumentis the value that you'd like to return from the result set. The First functionreturns only the first value in the result set.
Example
Firts(ItemDesired)
This example uses theFirst function to return the first ItemDesired value.

Fix Function

Fix ( number )

/ /
In Access, the Fix function returns the integer portion of a number.
The syntax for the Fix function is:
The argument number can be any valid numeric expression. Fix removes the fractional part of number and returns the resulting integer value. The data type of the return value is the same as that of the number argument. If number is a string that can be converted to a number, the return type will be a double. If the number is negative, Fix returns the first negative integer greater than or equal to number. See Int.
Fix (3.45) returns 3
Fix (2.74) returns 2
Fix (-16.58) returns -16
Format Function
Format ( expression [, format [, firstdayofweek [,firstweekofyear ] ] ] ) / /
In Access, the Format function returns a string containing an expression formatted according to instructions contained in a format expression.
expression is the value to format. It can be any valid expression.
format is optional. It isa valid named or user-defined format expression.You can either define your own format or use one of the predefined Access formatssuch as:
Format / Explanation
General Date / Displays date based on your system settings
Long Date / Displays date based on your system's long date setting
Medium Date / Displays date based on your system's medium date setting
Short Date / Displays date based on your system's short date setting
Long Time / Displays time based on your system's long time setting
Medium Time / Displays time based on your system's medium time setting
Short Time / Displays time based on your system's short time setting
General Number / Displays a number without thousand separators.
Currency / Displays thousand separators as well as two decimal places.
Fixed / Displays at least one digit to the left of the decimal place and two digits to the right of the decimal place.
Standard / Displays the thousand separators, at least one digit to the left of the decimal place, and two digits to the right of the decimal place.
Percent / Displays a percent value - that is, a number multiplied by 100 with a percent sign. Displays two digits to the right of the decimal place.
Scientific / Scientific notation.
Yes/No / Displays No if the number is 0. Displays Yes if the number is not 0.
True/False / Displays True if the number is 0. Displays False if the number is not 0.
On/Off / Displays Off if the number is 0. Displays On is the number is not 0.
firstdayofweek is optional. It is a constant that specifies the first day of the week. If not specified, Sunday is assumed. This parameter can be one of the following values:
Constant / Value / Explanation
vbUseSystem / 0 / Uses the NLS API setting
VbSunday / 1 / Sunday (default, if parameter is omitted)
vbMonday / 2 / Monday
vbTuesday / 3 / Tuesday
vbWednesday / 4 / Wednesday
vbThursday / 5 / Thursday
vbFriday / 6 / Friday
vbSaturday / 7 / Saturday
firstweekofyear is optional. It is a value that specifies the first week of the year. If not specified, the first week is assumed to be the week in which Jan 1 occurs.This parameter can be one of the following values:
Constant / Value / Explanation
vbUseSystem / 0 / Uses the NLS API setting
vbFirstJan1 / 1 / The week that contains January 1.
vbFirstFourDays / 2 / The first week that has at least 4 days in the year.
vbFirstFullWeek / 3 / The first full week of the year.
Example
Format (#25/05/2006#, "Long Date") returns 'May 25, 2004'
Format (#25/05/2006#, "mm/dd/yyyy") returns '05/25/2006'
Format ('0.745','Percent') returns '74.50%'
Format ('25748','Currency') returns '$25,748.00'
Hour Function Hour ( time ) / /
In Access, the Hour function returns a number between 0 and 23, inclusive, that represents the hour of the day corresponding to the time provided as an argument.
The required time argument is any numericor string expression, that can represent a time. If number is Null, this function returns a Null.
Examples
Hour (#4:37:12 PM#) returns 16
Hour (#11:24:12 AM#) returns 11
IIF Function IIf ( expression, truepart, falsepart ) / /
Inst Function –(See End of File)
Int Function Int( number ) / /
In Access, the Int function returns the integer portion of a number.
The argument number can be any valid numeric expression. The Intfunction removes the fractional part of number and return the resulting integer value. The data type of the return value is the same as that of the number argument. If number is negative, Int returns the first negative integer less than or equal to number. If number is a string that can be converted to a number, the return type will be a double. If the numeric expression results in a Null, Int return a Null.
Examples
Int (2.45) returns 2
Int (3.78) returns 3
Int (-4.89) returns -5
Last Function Last ( expression ) / /
In Access, the Last function return a field value from the last record in the result set of a query.
The required expression argument is the value that you'd like to return from the result set. The Last function returns only the last value in the result set..