Tổng quan SQL_ Visual Basic Function Reference

Chia sẻ: Nguyễn Thị Tú Uyên | Ngày: | Loại File: PDF | Số trang:10

Thêm vào BST

Báo xấu

222
lượt xem 58
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Finding all the most useful functions in Help can be difficult at best. When you know the function name you want, you can type it in the Visual Basic Editor, highlight the name, and then press F1 to go directly to the help topic. Following is a list of functions, categorized by type, that you might need to use. The tables list the function name and a brief description of what the function does. You can easily find details about the particular function syntax in Help once you know the function name....

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Tổng quan SQL_ Visual Basic Function Reference

Part 9: Articles Article 3 Visual Basic Function Reference Finding all the most useful functions in Help can be difficult at best. When you know the function name you want, you can type it in the Visual Basic Editor, highlight the name, and then press F1 to go directly to the help topic. Following is a list of functions, categorized by type, that you might need to use. The tables list the function name and a brief description of what the function does. You can easily find details about the particular function syntax in Help once you know the function name. As you know, you can call most Visual Basic functions from queries that you create in an Access desktop database (.mdb). If you convert your application to SQL Server, you need to know the equivalent function or clause you can use to obtain the same result. The tables also list the SQL Server equivalent for all function types except User Interface/File System Functions. Table A3-1. Arithmetic Functions Name Description SQL Server Equivalent Abs Returns the absolute value of a number. ABS Asc Returns the integer value of a character. ASCII Atn Returns the arctangent of a number. ATAN Cos Returns the cosine of a number that is an COS angle specified in radians. DDB Returns a double value containing the depreciation (none) of a value for a specific time period. Exp Returns the value of the base of the natural EXP logarithm (e) raised to the exponent you supply. See also Log. Fix Returns the value of the number you supply CAST, CONVERT (to truncated to an integer. If the number is tinyint, smallint, int, negative, Fix returns the first integer that or bigint) is greater than or equal to the number. See also Int. FV Calculates the future value of an annuity. (none) A49 Part 9: Articles
Part 9: Articles Microsoft Office Access 2003 Inside Out Table A3-1. Arithmetic Functions Chapter A3 Name Description SQL Server Equivalent Int Returns the value of the number you supply trun- FLOOR cated to an integer. If the number is negative, Int returns the first integer that is less than or equal to the number. See also Fix. IPmt Returns the interest payment for a given period (none) of an annuity. Chapter A3 IRR Returns the internal rate of return for a series of (none) periodic cash flows. LBound Returns the lowest available subscript for the (none) array and dimension you specify. See also UBound. Log Returns the natural logarithm of the number you LOG supply. See also Exp. MIRR Returns the modified internal rate of return for (none) Article 3 a series of periodic cash flows. NPer Returns the number of payment periods for (none) an annuity. NPV Returns the net present value of an investment. (none) Pmt Returns the payment required for an annuity. (none) PPmt Returns the amount applied to principal for a (none) Chapter A3 given payment period of an annuity. PV Returns the present value of an annuity. (none) Rate Returns the interest rate of an annuity. (none) Rnd Returns a random number. RAND Round Rounds a number to the specified number of ROUND decimal places. Sin Returns the sine of a number that is an angle SIN Article 3 specified in radians. SLN Returns the straight-line depreciation of an asset (none) for a single period. Sqr Returns the square root of a number. SQRT SYD Returns the sum-of-the-years’ digits depreciation (none) of an asset. Chapter A3 Tan Returns the tangent of a number that is an angle TAN specified in radians. UBound Returns the highest available subscript for the (none) array and dimension you specify. See also LBound. A50 Part 9: Articles
Part 9: Articles Visual Basic Function Reference Table A3-2. Conversion Functions Chapter A3 Name Description SQL Server Equivalent CBool Evaluates an expression and returns True (−1) or CAST, CONVERT False (0). CByte Converts a value to Byte data type. CAST, CONVERT CCur Converts a value to Currency data type. CAST, CONVERT CDate Converts a value to Date/Time data type. CAST, CONVERT CDbl Converts a value to Double data type. CAST, CONVERT Chapter A3 CDec Converts a value to Decimal data type. CAST, CONVERT CInt Converts a value to Integer data type. CAST or CONVERT used The function rounds fractions. with ROUND CLng Converts a value to Long Integer data type. CAST or CONVERT used The function rounds fractions. with ROUND CSng Converts a value to Single data type. CAST, CONVERT CStr Converts a value to a String data type. A Null CAST, CONVERT Article 3 value generates an error. Boolean values convert to yes or no. Dates convert to a string in your system’s short date format. CVar Converts a value to a Variant data type. If the CAST, CONVERT value is a number, the value must be in the ranges valid for CDbl. Chapter A3 Val Converts the numbers found in a string to a (none) valid numeric data type. Article 3 Chapter A3 A51 Part 9: Articles
Part 9: Articles Microsoft Office Access 2003 Inside Out Table A3-3. Date/Time Functions Chapter A3 Name Description SQL Server Equivalent Date Returns the current system date as a Variant CAST or CONVERT used data type. with GETDATE DateAdd Adds a specified interval to a date value. DATEADD DateDiff Finds the difference between two date/time DATEDIFF values in the interval you specify. DatePart Returns a requested portion of a date (second, DATEPART Chapter A3 minute, hour, week, weekday, day, day of year, month, quarter, or year). DateSerial Returns a date value calculated from supplied CAST, CONVERT integer year, month, and day values. The year value must be between 100 and 9999. DateValue Returns the date portion of a date/time value. CAST, CONVERT Day Returns the numeric day portion of a date/time DAY Article 3 value. See also DatePart. Hour Returns the hour portion of a date/time value. DATEPART See also DatePart. Minute Returns the minute portion of a date/time DATEPART value. See also DatePart. Month Returns the numeric month portion of a MONTH date/time value. See also DatePart. Chapter A3 MonthName Returns the name of the month of a date/time DATENAME value. Now Returns the current system date and time as GETDATE a Variant data type. Second Returns the seconds portion of a date/time DATEPART value. See also DatePart. Time Returns the current system time as a Variant CAST or CONVERT with Article 3 data type. GETDATE Timer Returns a Double data type containing the (none) number of seconds elapsed since midnight, accurate to .01 seconds. Chapter A3 A52 Part 9: Articles
Part 9: Articles Visual Basic Function Reference Table A3-3. Date/Time Functions Chapter A3 Name Description SQL Server Equivalent TimeSerial Returns a time value calculated from supplied CAST, CONVERT integer hour, minute, and second values. TimeValue Returns the time portion of a date/time value. CAST, CONVERT WeekDay Returns the integer day of the week from a DATEPART or DATENAME date/time value. Sunday is 1, Monday is 2, with CASE and so on. Chapter A3 WeekDayName Returns the name of the day from a date/time DATENAME value. Year Returns the year portion of a date/time value. YEAR See also DatePart. Article 3 Chapter A3 Article 3 Chapter A3 A53 Part 9: Articles
Part 9: Articles Microsoft Office Access 2003 Inside Out Table A3-4. Logic Functions Chapter A3 SQL Server Name Description Equivalent Choose Returns a value from a list based on an integer index in CASE the first argument. IIf Evaluates the first argument for True/False. If True, the CASE function evaluates the second argument; otherwise, the function evaluates the third argument. Chapter A3 IsArray Returns True if the argument you supply is an array. (none) IsDate Returns True if the argument you supply can be converted ISDATE to a date. IsEmpty Returns True if the Variant argument you supply has never (none) been initialized. IsError Returns True if the number you supply is a valid error value. (none) IsMissing Returns True if an optional argument to your Sub or Function (none) Article 3 procedure has not been supplied. IsNull Returns True if the argument you supply is the Null value. CASE Note that you cannot compare a variable to the constant Null (If A = Null Then…). IsObject Returns True if the argument you supply is an object (none) variable. Sgn Returns an indication whether the number you supply is SIGN Chapter A3 negative, positive, or zero. StrComp Compares two strings. You can optionally specify a com- (none) parison that is binary or case-sensitive. (Default string comparison in Access is not case-sensitive.) Switch Accepts a series of pairs of expressions (primary and CASE secondary). The primary expression of each pair must be an expression that can be evaluated to True or False. Article 3 Evaluates the expressions left to right and returns the secondary expression for the first primary expression that evaluates to True. TypeName Returns the data type of the variable or expression you (none) supply as a spelled out name of the data type. See also VarType. VarType Returns an integer code indicating the data type of the (none) Chapter A3 variable or expression you supply. See also TypeName. A54 Part 9: Articles
Part 9: Articles Visual Basic Function Reference Table A3-5. String Functions Chapter A3 SQL Server Name Description Equivalent Chr Returns the character value of an integer CHAR character code. Format Returns a string containing the value you supply, CAST or CONVERT formatted according to the format string you with limitations specify. Chapter A3 FormatCurrency Formats the number you supply as a currency CAST or CONVERT string. with limitations FormatDateTime Formats the date/time value you supply as a CAST or CONVERT date and/or time string. with limitations FormatNumber Formats the number you supply as a string CAST or CONVERT with the specified decimal places and negative with limitations indicator characters. See also Str. Article 3 FormatPercent Multiplies the number you supply by 100 and CAST or CONVERT returns a string with a trailing percent (%) sign. with limitations Hex Returns a string containing the hexadecimal (none) (base 16) value of the number you supply. InStr Returns the integer offset position of a search CHARINDEX, string within another string, searching the PATINDEX target string from the beginning. Chapter A3 InStrRev Returns the integer offset position of a search (none) string within another string, searching the target string from the end. Join Concatenates the one-dimensional array you (none) supply into a single string separated by the delimiter you specify. See also Split. LCase Converts a string to all lowercase characters. LOWER See also UCase and StrConv. Article 3 Left Returns the requested number of leftmost LEFT characters from a string. Len Returns the current length of a string. LEN LTrim Returns a string with any leading blanks LTRIM removed from the string you specify. Mid Returns the specified number of characters SUBSTRING Chapter A3 starting from a specified position in the middle of a string. Oct Returns a string containing the octal (base 8) (none) value of the number you supply. A55 Part 9: Articles
Part 9: Articles Microsoft Office Access 2003 Inside Out Table A3-5. String Functions Chapter A3 SQL Server Name Description Equivalent Partition Returns a string range name for a numeric CASE variable based on the range start, stop, and interval values you supply. Replace Examines a string you supply and returns a REPLACE string with all occurrences of one string replaced by another string. Chapter A3 Right Returns the requested number of rightmost RIGHT characters from a string. RTrim Returns a string with any trailing blanks RTRIM removed from the string you specify. Space Returns a string containing the specified SPACE number of spaces. Split Returns a zero-based one-dimensional array. (none) Article 3 It fills the array with the substrings it finds by parsing a string you supply with a delimiter you specify. See also Join. Str Converts a number to a string. See also STR FormatNumber and Format. StrConv Converts a string according to the method (none) Chapter A3 you specify. Options include all uppercase, all lowercase, and proper case. See also LCase and UCase. StrReverse Returns a string in which the order of characters REVERSE in the string you supply is reversed. String Returns a string of the length you specify filled REPLICATE with the character that you supply. Trim Returns a string with any leading and trailing LTRIM and RTRIM Article 3 blanks removed from the string you specify. UCase Converts a string to all uppercase characters. UPPER See also LCase and StrConv. Chapter A3 A56 Part 9: Articles
Part 9: Articles Visual Basic Function Reference Table A3-6. User Interface/File System Functions Chapter A3 Name Description Command Returns the string of characters following the /cmd switch in the command or shortcut you used to start your application. CurDir Returns the current path or the current path on the specified drive. Dir Returns a file name based on a supplied path and search criteria. After calling Dir once with a path argument and criteria, you can call Dir without arguments to fetch additional files in the path that also Chapter A3 meet the criteria. Dir returns a zero length string when no more files meet the criteria. FileDateTime Returns the create date or the last modification date of the file path you specify. FileLen Returns the size of the file path you specify. GetAttr Returns the attributes of the file or path you specify. InputBox Prompts the user with a message you supply and returns the user Article 3 response. MsgBox Displays a message you supply in a dialog box and returns an indica- tion of which button the user clicked in response. RGB Returns the RGB value based on red, green, and blue values you supply. Shell Executes the program you specify. Chapter A3 Article 3 Chapter A3 A57 Part 9: Articles