EXTRA NOTES DATES in formulas - Springlake-Earth ISD
ACCESS FUNCTIONSAsc 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.ExampleAsc ( "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.ExampleAbs (-1.45)?? returns 1,45 Abs (2.17)??? returns 2,17Atn 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.ExampleAtn (15)?? returns 1,50422816301907 Atn (21)?? returns 1,52321322351791Avg functionIn Access, the Avg function calculates the arithmetic mean of a set of values in a select query.Avg ( expression )Choose FunctionChoose(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.index?is position number in the list of values to return.choice is variant expression containing one of the possible choices.ExampleChoose(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.ExampleChr( 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,416146836547142Count 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.ExampleCurDir () ??????????? 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.ExamplesDate()?? returns a value such as '05.05.2005'VBA CodeDim MyDate MyDate = DateNow 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.IntervalDescriptionyyyy YearqQuartermMonthyDay of yearwWeekdaywwWeekhHournMinutesSecondnumber 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). date?is the date to which the interval is added.ExamplesDateAdd ('m', 4, #01/22/2004# )?????? returns 05/22/2004 DateAdd ('yyyy',3, #11/10/2003# )???? returns 11/10/2006EXTRA NOTES DATES in formulasTo subtract simple dates:1912 can be written as 12 2012 can be written as 112EXCELACCESS=”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 dateDATE function (turns date to serial #)DATESERIAL function (turns date to serial #)=NOW() returns current date=DATE() returns current dateDATES in formulasTo 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.25To 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 NumberYEAR – RETURNS # OF YEARS=year(40178)-year(F2)(In F2 appears: 2/7/1996 then it returns 1996)DateDiff FunctionDateDiff?( 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.IntervalDescriptionyyyy YearqQuartermMonthyDay of yearwWeekdaywwWeekhHournMinutesSeconddate1, date 2 are the two dates to calculate you want to use in the calculation. firstdayofweek?is 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. ConstantValue DescriptionvbUseSystem0Use the NLS API settingsvbSunday 1Sunday (default used)vbMonday2MondayvbTuesday 3TuesdayvbWednesday 4WednesdayvbThursday 5ThursdayvbFriday 6FridayvbSaturday 7Saturdayfirstweekofyear?is optional argument that specifies the first week of the year. The following is a list of valid parameters for firstweekday.ConstantValue DescriptionvbUseSystem0Use the NLS API settingsvbFirstJan1 1Start with?the first week in which January 1 occurs (default).vbFirstFourDays2Start with the?first week in the year that has at least 4 daysvbFirstFullWeek3Start ?with the?first full week of the yearExamplesDateDiff ('m', #12/17/2003#, #01/22/2004# )?????????? returns 1 DateDiff ('yyyy', #11/05/2001#, #11/10/2003# )??????? returns 2DatePart 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.IntervalDescriptionyyyy YearqQuartermMonthyDay of yearwWeekdaywwWeekhHournMinutesSeconddate is the date value that you wish to evaluate.firstdayofweek?is 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.ConstantValue DescriptionvbUseSystem0Use the NLS API settingsvbSunday 1Sunday (default used)vbMonday2MondayvbTuesday 3TuesdayvbWednesday 4WednesdayvbThursday 5ThursdayvbFriday 6FridayvbSaturday 7Saturdayfirstweekofyear?is 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.ConstantValue DescriptionvbUseSystem0Use the NLS API settingsvbFirstJan1 1Start with?the first week in which January 1 occurs (default).vbFirstFourDays2Start with the?first week in the year that has at least 4 daysvbFirstFullWeek3Start ?with the?first full week of the yearExamplesDatePart ('m', #12/17/2003# )??????????? returns 12DatePart ('yyyy', #11/05/2001# )???????? returns 2001 DatePart ('d', #11/05/2005#)???????????? returns 5DateSerial 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. year?is a number between 100? and 9999, inclusive, that represents the year value of the date.month?is a number between?1 and?12, inclusive, that represents the month value of the date.day?is a number between?1 and?31, inclusive, that represents the?day value of the date.ExamplesDateSerial?(2004,3,24 )???????? returns 03/24/2004 DateSerial(1998,7-5,17 )??????? returns 02/17/1998 DateSerial(2000,5,24-9 )??????? returns 05/15/200DateValue 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.ExamplesDateValue("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 numeric?or string expression, that can represent a date. If number is Null, this function returns a Null.ExamplesDay(#05/05/1985#)???? returns 5 Day (#17/07/2005#)??? returns 17Exp Function Exp?( number )In Access, the Exp function returns a double specifying e raised to the nth power. The constant e is approximately 2.718282.The syntax for the?Exp 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,3890560989FileDateTime Function FileDateTime( pathname )In Access, the FileDateTime function returns the date and time?when 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. ExampleFileDateTime ("C:\examples.mdb")????? returns? 25/05/2006 8:18:18 PMFileDateTime ("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 the?First function is:The required expression argument?is the value that you'd like to return from the result set. The First function?returns only the first value in the result set.ExampleFirts(ItemDesired)This example uses the?First 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 -16Format FunctionFormat ( 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 is?a valid named or user-defined format expression.?You can either define your own format or use one of the predefined Access formats?such as:FormatExplanationGeneral DateDisplays date based on your system settingsLong DateDisplays date based on your system's long date settingMedium DateDisplays date based on your system's medium date settingShort DateDisplays date based on your system's short date settingLong TimeDisplays time based on your system's long time settingMedium TimeDisplays time based on your system's medium time settingShort TimeDisplays time based on your system's short time settingGeneral NumberDisplays a number without thousand separators.CurrencyDisplays thousand separators as well as two decimal places.FixedDisplays at least one digit to the left of the decimal place and two digits to the right of the decimal place.StandardDisplays the thousand separators, at least one digit to the left of the decimal place, and two digits to the right of the decimal place.PercentDisplays a percent value - that is, a number multiplied by 100 with a percent sign. Displays two digits to the right of the decimal place.ScientificScientific notation.Yes/NoDisplays No if the number is 0. Displays Yes if the number is not 0.True/FalseDisplays True if the number is 0. Displays False if the number is not 0.On/OffDisplays 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:ConstantValueExplanationvbUseSystem0Uses the NLS API settingVbSunday1Sunday (default, if parameter is omitted)vbMonday2MondayvbTuesday3TuesdayvbWednesday4WednesdayvbThursday5ThursdayvbFriday6FridayvbSaturday7Saturdayfirstweekofyear 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:ConstantValueExplanationvbUseSystem0Uses the NLS API settingvbFirstJan11The week that contains January 1.vbFirstFourDays2The first week that has at least 4 days in the year.vbFirstFullWeek3The first full week of the year.ExampleFormat (#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 numeric?or string expression, that can represent a time. If number is Null, this function returns a Null.ExamplesHour (#4:37:12 PM#)?????? returns 16 Hour (#11:24:12 AM#)????? returns 11IIF Function IIf ( expression, truepart, falsepart )In Access, the IIF function returns one of two arguments depending on the evaluation of an expression.IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True.expression is the value that you want to test.truepart is the value returned if?expression evaluates to TRUE.falsepart is the value returned if?expression evaluates to FALSE.ExampleIIf ([Price] > 400, "expensive", "cheap")????? Returns "expensive" if the value in the Price field is???????????????????????????????????????????????greater than 400. Otherwise, returns "cheap". 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 Int function 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.ExamplesInt (2.45)?????? returns 2 Int (3.78)?????? returns 3 Int (-4.89)????? returns -5Last 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.. ExampleLast (ItemDesired)LCase Function Lcase ( string )In Access, the LCase function returns a Variant in which all letters of an argument have been converted to lowercase.The argument string can be any string expression. However, if string contains Null, Null is returned. Only uppercase letters are converted to lowercase; all lowercase letters and nonletter characters remain unchanged.ExampleLCase( "Hello World" )????? returns "hello world" LCase( "TEST123" )????????? returns "test123" Left Function Left ( string, length )In Access, the Left function returns the leftmost n characters of a string.The argument string can be any string expression. However, if string contains Null, Null is returned. The argument length is a Long expression indicating how many characters to return. It must be between 0 and approximately 65,535, inclusive. If length is 0, the return value is a zero-length string. If length is greater than or equal to the number of characters in string, the entire string is returned. To find the number of characters in string, use Len(string).ExampleLeft("WebCheatSheet",3)?????? returns "Web" Left("Articles", 15)???????????????? returns "Articles"Len Function Len ( string )In Access, the Len function returns the length of the specified string. The required string argument is any valid string expression. If the argument to Len is Null, the function returns a Null. ExampleLen ("access")???????????????????????? returns 6 Len ("")????? returns 17 Len ("hello world")?????????????????? returns 11Log Function Log?( number )In Access, the Log function returns the natural logarithm of a number. The natural logarithm is the logarithm to the base e. The constant e is approximately 2.718282.The argument number can be any valid numeric expression greater than zero.Log(3.1)?????? ?returns 1,1314021115 Log(5)?????????? returns 1,6094379124 Log(17)???????? returns 2,8332133441LTrim Function LTrim ( string )In Access, the LTrim function returns a copy of a string with leading spaces removed.The string argument can be any string expression. If string contains Null, Null is returned.ExampleLTrim(" WebCheatSheet ")?????? returns "WebCheatSheet " LTrim("tutorials ")?????????????????? returns "tutorials "LTrim(" articles")??????????????????? returns "articles" Max ( expression )Max Function In Access, the Max function returns the maximum of a set of values in a select query.The expression argument represents a string expression identifying the field that contains the data you want to evaluate or an expression that performs a calculation using the data in that field. Operands in expression can include the name of a table field, a constant, or a function (not one of the other SQL aggregate functions). You can use Max to determine the largest values in a field based on the specified aggregation, or of FormBottom of FormMid Function Mid( (string, start [, length]) )In Access, the Mid function returns a string containing a specified number of characters from a string.string String expression from which another string is created. This can be any string expression. If string contains Null, Null is returned. start ??Long expression that indicates the character position in string at which the part to be taken begins. If start is greater than the number of characters in string, Mid returns a zero-length string (""). length ??Long expression that indicates the number of characters to return. If omitted or if there are fewer than length characters in the text (including the character at start), all characters from the start position to the end of the string are returned.ExampleMid ("WebCheatSheet",1,3 )??????????????returns " Web" Mid ("access functions",8,4)???????????????returns "func" Mid ("access functions",8)????????????????? returns "access functions" Min Function Min( expression )In Access, the Min function returns the minimum of a set of values contained in a select query.The expression argument represents a string expression identifying the field that contains the data you want to evaluate or an expression that performs a calculation using the data in that field. Operands in expr can include the name of a table field, a constant, or a function (not one of the other SQL aggregate functions). You can use Min to determine the smallest values in a field based on the specified aggregation, or grouping.Minute Function Minute ( time )In Access, the Minute function returns an Integer specifying a whole number between 0 and 59, inclusive, representing the minute of the hour.The required time argument is any numeric?or string expression, that can represent a time. If number is Null, this function returns a Null.ExamplesMinute (#4:37:12 PM#)?????? returns 37Minute(#11:24:12 AM#)?????? returns 24Month Function Month ( date )In Access, the Month function returns an Integer between 1 and 12, inclusive, containing the month part of a date.The required time argument is any numeric?or string expression, that can represent a date. If number is Null, this function returns a Null.ExamplesMonth (#05/05/1985#)????? returns 5 Month (#17/07/2005#)????? returns 7MonthName Function MonthName ( month [, abbreviate] )In Access, the MonthName function returns a string containing the specified month.The required month argument is a value from 1 to 12, representing the month. The abbreviate argument is optional. It accepts a boolean value, that indicates if the month name is to be abbreviated. If omitted, the default is False, which means that the month name is not abbreviated.ExamplesMonthName (5)?????????????? returns 'May' MonthName (5,False)?????? returns 'May' MonthName (1,True)??????? returns 'May'Now Function Now ( )In Access, the Now function returns the current date and time according your computer's system date and time.The Now function returns a?date and time that are stored internally as a double-precision number. ExamplesNow ()????? returns such as '05/05/2005 11:24:41 PM'Replace FunctionReplace ( expression, find, replacement, [start, [count, [compare]]] )In Access, the Replace function returns a string in which a sequence of characters has been replaced with another set of characters a specified number of times.The return value of the Replace function is a string, that begins at the position specified by start and concludes at the end of the expression string. It is not a copy of the original string from start to finish.expression?is the string expression containing substring to replace.find is the substring being searched for in expression.replacement will replace find in expression.start is optional. This is the position in?expression to begin the search. If this parameter is omitted, 1 is assumed.count is optional. This is the number of substitutions?to perform. If omitted, the default value is 1, which means make all possible pare is optional. The following is a list of valid parameters:ConstantValueDescriptionvbUseCompareOption1Performs a comparison using the setting of the Option Compare statement.vbBinaryCompare0Performs a binary comparison.vbTextCompare1Performs a textual comparison.vbDatabaseCompare2Performs a comparison based on information in your databaseExampleReplace("Green Apple", "Green", "Yellow")??????returns "Yellow Apple" Replace("Green Apple", "e", "a", 7)??????????????? returns "Green Appla" Replace("Green Apple", "e", "a", 1, 2)??????????? returns "Graan Apple"Right Function Right( string )In Access, the Right function returns the rightmost n characters of a string.The argument string can be any string expression. However, if string contains Null, Null is returned. The argument length is a Long expression indicating how many characters to return. It must be between 0 and approximately 65,535, inclusive. If length is 0, the return value is a zero-length string. If length is greater than or equal to the number of characters in string, the entire string is returned. To find the number of characters in string, use Len(string). ExampleRight ("WebCheatSheet",11)?????? returns "CheatSheet" Right("Articles", 15)???????????????????returns "articles" Rnd Function Rnd?( number )In Access, the Rnd function returns a single containing a random number. You can specify the random number to be a value between 2 user-specified numbers.The argument number can be any valid numeric expression. The Rnd function returns a value less than 1 but greater than or equal to zero. To produce random integers in a given range, use this formula:Int((upperbound - lowerbound + 1) * Rnd + lowerbound)Here, upperbound is the highest number in the range, and lowerbound is the lowest number in the range.ExampleInt((7-1 +1) * Rnd + 1)????????????? returns a random value between 1 and 6.Int((30-20 +1) * Rnd + 20)?????????? returns a random value between 20 and 30.RTrim Function RTrim ( string )In Access, the RTrim function returns a copy of a string with trailing spaces removed.The string argument can be any string expression. If string contains Null, Null is returned.ExampleRTrim("? WebCheatSheet " )??????? returns " WebCheatSheet" RTrim("tutorials ")????????????????????? returns "tutorials" RTrim(" articles")?????????????????????? returns " articles" Second Function Second?(?time )In Access, the Second function returns a number between 0 and 59, inclusive, representing the second of the minute of the hour.The required time argument is any numeric?or string expression, that can represent a time. If number is Null, this function returns a Null.ExamplesSecond (#05/05/1985 3:45:21 PM#)????? returns 21 Second (#10:07:43 AM#)???????????????????? returns 43Second Function Second?(?time )In Access, the Second function returns a number between 0 and 59, inclusive, representing the second of the minute of the hour.The required time argument is any numeric?or string expression, that can represent a time. If number is Null, this function returns a Null.ExamplesSecond (#05/05/1985 3:45:21 PM#)????? returns 21 Second (#10:07:43 AM#)???????????????????? returns 43Sgn Function Sgn( number )In Access, the Sgn function returns a variant indicating the sign of a number.The argument number can be any valid numeric expression. Its sign determines the value returned by the Sgn function.If number is greater than zero, the Sgn function will return 1.If number is equal to zero, the Sgn function will return 0.If number is?less than zero, the Sgn function will return -1.ExampleSgn (2.38)?????? returns 1 Sgn (0)????????? returns 0 Sgn (-2.38)??????returns -1Sin Function Sin?( number )In Access, the Sin function returns the sine of an angle.The argument number can be any valid numeric expression that expresses an angle in radians.?The Sin function takes an angle and returns the ratio of two sides of a right triangle. The ratio is the length of the side opposite the angle divided by the length of the hypotenuse.Sin (15)?????? returns 0,65028784 Sin (2)??????? returns 0,90929742Space Function Space( number )In Access, the Space function returns a string consisting of the specified number of spaces.The argument number specifies the number of spaces you want in the string. It can be of any numeric data type, but is rounded to a Long and must be between 0 and approximately 65,535, inclusive.ExampleSpace(2)??? returns "? " Space(7)??? returns?"?????? " Sqr Function Sqr?( number )In Access, the Sqr function returns the square root of a number.The argument number can be any valid numeric expression greater than or equal to zero. Sqr (0)????? returns 0 Sqr (3)????? returns 1,73205 Sqr (16)???? returns 4Str Function Str( number )In Access, the Str function returns string representation of a number.The required number argument is a Long containing any valid numeric expression. When numbers are converted to strings, a leading space is always reserved for the sign of number. If number is positive, the returned string contains a leading space and the plus sign is implied.ExampleStr (7)????????? returns " 7" Str (-154)???? returns "-154" StrConv FunctionStrConv ( string, conversion, LCID )In Access, the StrConv function convert a string to upper case, lower case, proper case, or several other formats.string?String expression to be converted.conversation The type of conversion to perform. LCID?LCID is optional. The LocaleID, if different than the system LocaleID. If this parameter is omitted, the StrConv function assumes the system LocaleID.The syntax for the StrConv function is:The following is a list of valid parameters for conversion. ConstantValue DescriptionvbUpperCase1Converts the string to uppercase characters.vbLowerCase2Converts the string to lowercase characters.vbProperCase3Converts the first letter of every word in string to uppercase.vbUnicode64Converts the string to Unicode.vbFromUnicode128Converts the string from Unicode to the default code page of the system.Sum Function Sum ( expression )In Access, the Sum function returns the sum of a set of values in a set of values in a select query.The expression argument represents a string expression identifying the field that contains the numeric data you want to add or an expression that performs a calculation using the data in that field. Operands in expr can include the name of a table field, a constant, or a function (not one of the other SQL aggregate functions). The Sum function totals the values in a field. It ignores records that contain Null fields. Switch FunctionSwitch ( expression1, value1, expression2, value2, ... expression_n, value_n )In Access, the Switch function evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.The Switch function argument list consists of pairs of expressions and values. The expressions are evaluated from left to right, and the value associated with the first expression to evaluate to True is returned. If the parts aren't properly paired, a run-time error occurs. Switch evaluates all of the expressions, even though it returns only one of them. Switch returns Null in two cases: if none of the expressions is true, or if the first True expression has a corresponding value that is Null.expression1, expression2, expression_n is a list of expressions you want to evaluate. value1, value2, ... value_n is a list of values to be returned if the corresponding expression is True.ExampleSwitch (OwnerID=1, "Table", OwnerID=2, "Desk", OwnerID=15, "Mirrow")???? returns: ???? "Table", if the OwnerID field is 1.???? "Desk", if the OwnerID field is 2.???? "Mirrow", if the OwnerID field is 15. Tan Function Tan?( number )In Access, the Tan function returns the tangent of an angle.The argument number can be any valid numeric expression that expresses an angle in radians.?Tan takes an angle and returns the ratio of two sides of a right triangle. The ratio is the length of the side opposite the angle divided by the length of the side adjacent to the angle.Tan (15)???? returns -0,855993400 Tan (2)????? returns -2,185039863Time Function Time?(?1)In Access, the Time function returns the current system time.The Time function returns a time stored internally as the fractional part of a?double-precision number.ExamplesTime ()????? returns a value such as '5:22:43 PM'TimeSerial Function TimeSerial?( hour, minute, second?)In Access, the TimeSerial function returns the time for a specific hour, minute, and second.The TimeSerial function returns a?time that is stored internally as a double-precision fractional number between 0 and 0.99999. This number represents a time between 0:00:00 and 23:59:59, or 12:00:00 A.M. and 11:59:59 P.M., inclusive. If the time specified by the three arguments, either directly or by expression, falls outside the acceptable range of times, an error occurs.hour is a number between 0 (12:00 A.M.) and 23 (11:00 P.M.), inclusive, or a numeric expression.minute is a number between 0 and?59, inclusive, or a numeric expression.second is a number between 0 and?59, inclusive, or a numeric expression.TimeValue FunctioneValue?( string )In Access, the TimeValue function the time represented by a string argument.function is:TimThe required?string argument is normally a string expression representing a time from 0:00:00 to 23:59:59,? inclusive. You can enter valid times using a 12- or 24-hour clock. If time contains Null, Null is returned. If?string includes date information, TimeValue does not return it. However, if?string includes valid date information, an error occurs.ExamplesTimeValue ("27/11/2005 4:47:31 AM")???? returns '4:47:31 AM' TimeValue ("4:47:31 AM")?????????????????????? returns '4:47:31 AM' TimeValue ("17:22:43")????????????????????????? returns '5:22:43 PM'Trim Function Trim ( string )In Access, the Trim function returns a copy of a string with leading and trailing spaces removed.The string argument can be any string expression. If string contains Null, Null is returned.ExampleTrim (" WebCheatSheet ")???????? returns?"WebCheatSheet" Trim?("tutorials ")???????????????????? returns "tutorials" Trim?(" articles")??????????????????????returns?"articles" UCase Function UCase(String)In Access, the UCase function returns a string in which all letters of an argument have been converted to uppercase.The argument string can be any string expression. If String contains Null, Null is returned. Only lowercase letters are converted to uppercase; all uppercase letters and nonletter characters remain unchanged.ExampleUCase("WebCheatSheet") ?????????? returns "WEBCHEATSHEET" UCase("Have a nice day")????????? returns "HAVE A NICE DAY" Val Function Val( string )In Access, the Val function returns a variant indicating the sign of a number.The required string argument is any valid string expression. It is a sequence of characters that can be interpreted as a numeric value. Val stops reading the string at the first character that it cannot recognize as part of a number. Val also strips blanks, tabs, and linefeeds from the argument string. Symbols and characters often considered part of a numeric value, such as the dollar sign and commas, are not recognized by Val as numeric. The Val function always returns a Double. If the result of the Val function is assigned to a variable, the Double returned by Val is forced into the datatype of the variable.?ExampleVal ( 237 and 54)??????? returns 237 Val ( 2 4 54 7)??????????? returns 24547 Val?(17 Main Road?)?????returns 17Weekday FunctionWeekday?(?date, [firstdayofweek] )In Access, the Weekday function returns an integer between 1 (Sunday) and 7 (Saturday) that represents the day of the week corresponding to the date and the first day of the week provided as arguments.The argument date is any numeric expression that can represent a date and/or time. Numbers to the left of the decimal point in date represent the date; numbers to the right represent the time. If date is Null, this function returns a Null. The firstweekday argument is an optional integer argument that specifies the first day of the week. The following is a list of valid parameters for firstweekday.ConstantValue DescriptionvbUseSystem0Use the NLS API settingsvbSunday 1Sunday (default used)vbMonday2MondayvbTuesday 3TuesdayvbWednesday 4WednesdayvbThursday 5ThursdayvbFriday 6FridayvbSaturday 7SaturdayExamplesWeekday (#17/07/2005#,vbSunday)??? ? returns 1 Weekday (#17/07/2005#,4)???????????? returns 5 Weekday (#17/07/2005#)?????????????? returns 1 Access: WeekdayName FunctionWeekdayName?(?weekday, [abbreviate], [firstdayofweek] )In Access, the WeekdayName function returns the name of the day of the week corresponding to the date and the first day of the week provided as arguments.The required argument?weekday is a number between 1 and 7 that represent a day of the week. The abbreviate argument is optional. It accepts a boolean value, that indicates if the month name is to be abbreviated. If omitted, the default is False, which means that the month name is not abbreviated. The firstweekday argument is also optional. It?determines what day is to be the first day of the week. The following is a list of valid parameters for firstweekday.ConstantValue DescriptionvbUseSystem0Use the NLS API settingsvbSunday 1Sunday (default used)vbMonday2MondayvbTuesday 3TuesdayvbWednesday 4WednesdayvbThursday 5ThursdayvbFriday 6FridayvbSaturday 7SaturdayExamplesWeekdayName (5)??????????????????????? ??? returns 'Thursday' WeekdayName (5,True)???????????????????? returns 'Thu'WeekdayName (5,True,5)????????????????? returns 'Mon' WeekdayName (5,True,vbTuesday)? ? returns 'Mon'Year FunctionYear?(?date )In Access, the Year function returns an integer between 100 and 9999, inclusive, that represents the year of a date argument. The required time argument is any numeric?or string expression, that can represent a date. If number is Null, this function returns a Null.ExamplesYear (#05/05/1985#)????? returns 1985 Year?(#17/07/2005#)????? returns 2005INSTR in formulas – Searches for a character in a string of text (field)What field are you searchingPut character you are searching for between quote marksTo search:OptionalINSTR (start, [FIELD], “ “, compare)Record 1RETURNS/UPDATESEx. What text place contains a space?1 2 3 4 5 J O H N Text place for space =5 ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- how to make consecutive dates in excel
- important dates in american history
- important dates in us history
- summer solstice dates in history
- important dates in history timeline
- how to calculate dates in excel spreadsheet
- famous dates in american history
- famous dates in history timeline
- significant dates in history
- famous dates in history
- important dates in world history
- most famous dates in history