Solved

List of Functions

Posted on 2003-11-06
9
1,484 Views
Last Modified: 2011-10-13
Anybody have a good, comprehensive list of Access functions available for use in queries (e.g., format, cint, round, etc.)  If you've got the associated parameters, I'd love to have them on the list too!

Thanks!
0
Comment
Question by:AirResourceSpec
  • 4
  • 4
9 Comments
 
LVL 19

Expert Comment

by:Dexstar
ID: 9695615
AirResourceSpec:

> Anybody have a good, comprehensive list of Access functions available for use
> in queries (e.g., format, cint, round, etc.)  If you've got the associated
> parameters, I'd love to have them on the list too!

MSDN is your friend:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/acidxFunctions.asp

Hope that helps,
Dex*
0
 
LVL 1

Author Comment

by:AirResourceSpec
ID: 9701399
I was really looking for a list with more detail!?!  I finally took the MSDN list that I had and looked up more details, parameters, etc. in help and will post it here when I'm finished with it.
0
 
LVL 19

Accepted Solution

by:
Dexstar earned 125 total points
ID: 9702201
Oh, pardon me... I didn't notice they didn't give you but a list of those functions.  I found what you were looking for:  The VB Language Reference on MSDN.

There didn't seem to be one main start page, so just click on this link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctAbs.asp

And it will bring up one function definition, but you'll have the complete list along the side in the table of contents... Just browse and click on the one you want.

Enjoy
Dex*
0
 
LVL 1

Author Comment

by:AirResourceSpec
ID: 9724358
Here's what I was looking for:
(CSV - cut and paste)

CBool,CBool(expression),Coerces an expression to a boolean data type. Any valid string or numeric expression.
CByte,CByte(expression),Coerces an expression to a byte data type. 0 to 255.
CCur,CCur(expression),"Coerces an expression to a currency data type.  -922,337,203,685,477.5808 to 922,337,203,685,477.5807."
CDate,CDate(expression),Coerces an expression to a date data type. Any validdate expression.
CDbl,CDbl(expression),"Coerces an expression to a double data type. -1.79769313486232E308 to
-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values."
CDec,CDec(expression),"Coerces an expression to a decimal data type. +/-79,228,162,514,264,337,593,543,950,335 for zero-scaled numbers, that is, numbers with no decimal places. For numbers with 28 decimal places, the range is
+/-7.9228162514264337593543950335. The smallest possible non-zero number is 0.0000000000000000000000000001."
CInt,CInt(expression),"Coerces an expression to an integer data type. -32,768 to 32,767; fractions are rounded."
CLng,CLng(expression),"Coerces an expression to a long data type. -2,147,483,648 to 2,147,483,647; fractions are rounded."
CSng,CSng(expression),Coerces an expression to a single data type. -3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values.
CStr,CStr(expression),Coerces an expression to a string data type.
CVar,CVar(expression),Coerces an expression to a variant data type. Same range as Double for numerics. Same range as String for non-numerics.
CVDate,CVDate(expression),Returns a Variant whose subtype is Date instead of an actual Date type
Val,Val(string),Returns the numbers contained in a string as a numeric value of appropriate type.
Str,Str(number),Returns a Variant (String) representation of a number.
VarType,VarType(varname),"Returns an Integer indicating the subtype of a variable. vbEmpty=0, vbNull=1, vbInteger=2,  vbLong=3, vbSingle=4, vbDouble=5, vbCurrency=6, vbDate=7, vbString=8, vbObject=9, vbError=10, vbBoolean=11, vbVariant=12, vbDataObject=13, vbDecimal=14, vbByte=17, vbUserDefinedType=36, vbArray=8192"
Date,Date,Returns a Variant (Date) containing the current system date.
Now,Now,Returns a Variant (Date) specifying the current date and time according your computer's system date and time.
Time,Time,Returns a Variant (Date) indicating the current system time.
DateAdd,"DateAdd(interval, number, date)",Returns a Variant (Date) containing a date to which a specified time interval has been added.
DateDiff,"DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])",Returns a Variant (Long) specifying the number of time intervals between two specified dates.
DatePart,"DatePart(interval, date[,firstdayofweek[, firstweekofyear]])",Returns a Variant (Integer) containing the specified part of a given date.
DateSerial,"DateSerial(year, month, day)","Returns a Variant (Date) for a specified year, month, and day."
DateValue,DateValue(date),Returns a Variant (Date).
Weekday,"Weekday(date, [firstdayofweek])",Returns a Variant (Integer) containing a whole number representing the day of the week.
Hour,Hour(time),"Returns a Variant (Integer) specifying a whole number between 0 and 23, inclusive, representing the hour of the day."
Minute,Minute(time),"Returns a Variant (Integer) specifying a whole number between 0 and 59, inclusive, representing the minute of the hour."
Second,Second(time),"Returns a Variant (Integer) specifying a whole number between 0 and 59, inclusive, representing the second of the minute."
Day,Day(date),"Returns a Variant (Integer) specifying a whole number between 1 and 31, inclusive, representing the day of the month."
Month,Month(date),"Returns a Variant (Integer) specifying a whole number between 1 and 12, inclusive, representing the month of the year."
Year,Year(date),Returns a Variant (Integer) containing a whole number representing the year.
IsDate,IsDate(expression),Returns a Boolean value indicating whether an expression can be converted to a date.
Format,"Format(expression[, format[, firstdayofweek[, firstweekofyear]]])",Returns a Variant (String) containing an expression formatted according to instructions contained in a format expression.
IIf,"IIf(expr, truepart, falsepart)","Returns one of two parts, depending on the evaluation of an expression."
InStr,"InStr([start, ]string1, string2[, compare])",Returns a Variant (Long) specifying the position of the first occurrence of one string within another.
Mid,"Mid(string, start[, length])",Returns a Variant (String) containing a specified number of characters from a string.
StrComp,"StrComp(string1, string2[, compare])",Returns a Variant (Integer) indicating the result of a string comparison.
StrConv,"StrConv(string, conversion, LCID)",Returns a Variant (String) converted as specified.
IsNull,IsNull(expression),Returns a Boolean value that indicates whether an expression contains no valid data (Null).
LCase,LCase(string),Returns a String that has been converted to lowercase.
UCase,UCase(string),"Returns a Variant (String) containing the specified string, converted to uppercase."
Left,"Left(string, length)",Returns a Variant (String) containing a specified number of characters from the left side of a string.
Right,"Right(string, length)",Returns a Variant (String) containing a specified number of characters from the right side of a string.
Len,Len(string | varname),Returns a Long containing the number of characters in a string or the number of bytes required to store a variable.
LTrim,LTrim(string),"Returns a Variant (String) containing a copy of a specified string without leading spaces (LTrim), trailing spaces (RTrim), or both leading and trailing spaces (Trim)."
RTrim,RTrim(string),"Returns a Variant (String) containing a copy of a specified string without leading spaces (LTrim), trailing spaces (RTrim), or both leading and trailing spaces (Trim)."
Trim,Trim(string),"Returns a Variant (String) containing a copy of a specified string without leading spaces (LTrim), trailing spaces (RTrim), or both leading and trailing spaces (Trim)."
Space,Space(number),Returns a Variant (String) consisting of the specified number of spaces.
String,"String(number, character)",Returns a Variant (String) containing a repeating character string of the length specified.
IsNumeric,IsNumeric(expression),Returns a Boolean value indicating whether an expression can be evaluated as a number.
Int,Int(number),Returns the integer portion of a number.
Rnd,Rnd[(number)],Returns a Single containing a random number.
Sgn,Sgn(number),Returns a Variant (Integer) indicating the sign of a number.
Atn,Atn(number),Returns a Double specifying the arctangent of a number.
Cos,Cos(number),Returns a Double specifying the cosine of an angle.
Sin,Sin(number),Returns a Double specifying the sine of an angle.
Tan,Tan(number),Returns a Double specifying the tangent of an angle.
Abs,Abs(number),Returns a value of the same type that is passed to it specifying the absolute value of a number.
Sqr,Sqr(number,Returns a Double specifying the square root of a number.
Log,Log(number),Returns a Double specifying the natural logarithm of a number.
StDev,StDev(expr),Return estimates of the standard deviation for a population sample represented as a set of values contained in a specified field on a query.
StDevP,StDevP(expr),Return estimates of the standard deviation for a population represented as a set of values contained in a specified field on a query.
Var,Var(expr),Return estimates of the variance for a population or a population sample represented as a set of values contained in a specified field on a query.
VarP,VarP(expr),Return estimates of the variance for a population or a population sample represented as a set of values contained in a specified field on a query.
Array,Array(arglist),Returns a Variant containing an array.
Asc,Asc(string),Returns an Integer representing the character code corresponding to the first letter in a string.
Avg,Avg(expr),Calculates the arithmetic mean of a set of values contained in a specified field on a query.
Choose,"Choose(index, choice-1[, choice-2, ... [, choice-n]])",Selects and returns a value from a list of arguments.
Chr,Chr(charcode),Returns a String containing the character associated with the specified character code.
Command*,Command,Returns the argument portion of the command line used to launch Microsoft Visual Basic or an executable program developed with Visual Basic.
Count,Count(expr),Calculates the number of records returned by a query.
CreateObject,"CreateObject(class,[servername])",Creates and returns a reference to anActiveX object.
CurDir,CurDir[(drive)],Returns a Variant (String) representing the current path.
CVErr,CVErr(errornumber),Returns a Variant of subtype Error containing an error number specified by the user.
DAvg,"DAvg(expr, domain, [criteria])","You can use the DAvg function to calculate the average of a set of values in a specified set of records (a domain). Use the DAvg function in Visual Basic code or in a macro, in a query expression, or in a calculated control."
Dcount,"DCount(expr, domain, [criteria])","You can use the DCount function to determine the number of records that are in a specified set of records (a domain). Use the DCount function in Visual Basic, a macro, a query expression, or a calculated control."
DDB,"DDB(cost, salvage, life, period[, factor])",Returns a Double specifying the depreciation of an asset for a specific time period using the double-declining balance method or some other method you specify.
DDE,"DDE(application, topic, item)","You can use the DDE function to initiate a dynamic data exchange (DDE) conversation with another application, request an item of information from that application, and display that information in a control on a form or report."
DDEInitiate,"DDEInitiate(application, topic)",You can use the DDEInitiate function to begin a dynamic data exchange (DDE) conversation with another application. The DDEInitiate function opens a DDE channel for transfer of data between a DDE server and client application.
DDERequest,"DDERequest(channum, item)",You can use the DDERequest function over an open dynamic data exchange (DDE) channel to request an item of information from a DDE server application.
DDESend,"DDESend(application, topic, item, data)",You can use the DDESend function to initiate a dynamic data exchange (DDE) conversation with another application and send an item of information to that application from a control on a form or report.
DFirst,"DFirst(expr, domain, [criteria])","You can use the DFirst and DLast functions to return a random record from a particular field in a table or query when you simply need any value from that field. Use the DFirst and DLast functions in a macro, module, query expression, or calculated control on a form or report."
Dir*,"Dir[(pathname[, attributes])]","Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive."
DLast,"DLast(expr, domain, [criteria])","You can use the DFirst and DLast functions to return a random record from a particular field in a table or query when you simply need any value from that field. Use the DFirst and DLast functions in a macro, module, query expression, or calculated control on a form or report."
DLookup,"DLookup(expr, domain, [criteria])","You can use the DLookup function to get the value of a particular field from a specified set of records (a domain). Use the DLookup function in Visual Basic, a macro, a query expression, or a calculated control on a form or report."
DMax,"DMax(expr, domain, [criteria])","You can use the DMin and DMax functions to determine the minimum and maximum values in a specified set of records (a domain). Use the DMin and DMax functions in Visual Basic, a macro, a query expression, or a calculated control."
DMin,"DMin(expr, domain, [criteria])","You can use the DMin and DMax functions to determine the minimum and maximum values in a specified set of records (a domain). Use the DMin and DMax functions in Visual Basic, a macro, a query expression, or a calculated control."
DoEvents,DoEvents( ),Yields execution so that the operating system can process other events.
DStDev,"DStDev(expr, domain, [criteria])","You can use the DStDev and DStDevP functions to estimate the standard deviation across a set of values in a specified set of records (a domain). Use the DStDev and DStDevP functions in Visual Basic, a macro, a query expression, or a calculated control on a form or report."
DStDevP,"DStDevP(expr, domain, [criteria])","You can use the DStDev and DStDevP functions to estimate the standard deviation across a set of values in a specified set of records (a domain). Use the DStDev and DStDevP functions in Visual Basic, a macro, a query expression, or a calculated control on a form or report."
DSum,"DSum(expr, domain, [criteria])","You can use the DSum functions to calculate the sum of a set of values in a specified set of records (a domain). Use the DSum function in Visual Basic, a macro, a query expression, or a calculated control."
DVar,,
DVarP,,
Environ*,Environ({envstring | number}),Returns the String associated with an operating system environment variable.
EOF,EOF(filenumber),Returns an Integer containing the Boolean value True when the end of a file opened for Random or sequential Input has been reached.
Error,Error[(errornumber)],Returns the error message that corresponds to a given error number.
EuroConvert,"EuroConvert(number, sourcecurrency, targetcurrency, [fullprecision, triangulationprecision])",You can use the EuroConvert function to convert a number to euro or from euro to a participating currency. You can also use it to convert a number from one participating currency to another by using the euro as an intermediary (triangulation). The EuroConvert function uses fixed conversion rates established by the European Union.
Eval,Eval(stringexpr),You can use the Eval function to evaluate an expression that results in a text string or a numeric value.
Exp,Exp(number),Returns a Double specifying e (the base of natural logarithms) raised to a power.
FileAttr,"FileAttr(filenumber, returntype)",Returns a Long representing the file mode for files opened using the Open statement.
FileDateTime*,FileDateTime(pathname),Returns a Variant (Date) that indicates the date and time when a file was created or last modified.
FileLen*,FileLen(pathname),Returns a Long specifying the length of a file in bytes.
First,First(expr),Return a field value from the first record in the result set returned by a query.
Fix,Fix(number),Returns the integer portion of a number.
FreeFile,FreeFile[(rangenumber)],Returns an Integer representing the next file number available for use by the Open statement.
FV,"FV(rate, nper, pmt[, pv[, type]])","Returns a Double specifying the future value of an annuity based on periodic, fixed payments and a fixed interest rate."
GetAllSettings,"GetAllSettings(appname, section)",Returns a list of key settings and their respective values (originally created with SaveSetting) from an application's entry in the Windows registry or (on the Macintosh) information in the application’s initialization file.
GetAttr*,GetAttr(pathname),"Returns an Integer representing the attributes of a file, directory, or folder."
GetObject,"GetObject([pathname] [, class])",Returns a reference to an object provided by an ActiveX component.
GetSetting*,"GetSetting(appname, section, key[, default])",Returns a key setting value from an application's entry in the Windows registry or (on the Macintosh) information in the application’s initialization file.
GUIDFromString,GUIDFromString(stringexpression),"The GUIDFromString function converts a string to a GUID, which is an array of type Byte."
Hex,Hex(number),Returns a String representing the hexadecimal value of a number.
HyperlinkPart,,
IMEStatus,IMEStatus,Returns an Integer specifying the current Input Method Editor (IME) mode of Microsoft Windows; available in East Asian versions only.
Input*,,
InputBox,"InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])","Displays a prompt in a dialog box, waits for the user to input text or click a button, and returns a String containing the contents of the text box."
IPmt,"IPmt(rate, per, nper, pv[, fv[, type]])","Returns a Double specifying the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate."
IRR,"IRR(values()[, guess])",Returns a Double specifying the internal rate of return for a series of periodic cash flows (payments and receipts).
IsArray,IsArray(varname),Returns a Boolean value indicating whether a variable is an array.
IsEmpty,IsEmpty(expression),Returns a Boolean value indicating whether a variable has been initialized.
IsError,IsError(expression),Returns a Boolean value indicating whether anexpression is an error value.
IsMissing,IsMissing(argname),Returns a Boolean value indicating whether an optional Variant argument has been passed to a procedure.
IsObject,IsObject(identifier),Returns a Boolean value indicating whether an identifier represents an object variable.
Last,Last(expr),Return a field value from the last record in the result set returned by a query.
LBound,"LBound(arrayname[, dimension])",Returns a Long containing the smallest available subscript for the indicated dimension of an array.
Loc,Loc(filenumber),Returns a Long specifying the current read/write position within an open file.
LOF,LOF(filenumber),"Returns a Long representing the size, in bytes, of a file opened using the Open statement."
Max,Max(expr),Return the maximum of a set of values contained in a specified field on a query.
Min,Min(expr),Return the minimum of a set of values contained in a specified field on a query.
MIRR,"MIRR(values(), finance_rate, reinvest_rate)",Returns a Double specifying the modified internal rate of return for a series of periodic cash flows (payments and receipts).
MsgBox,"MsgBox(prompt[, buttons] [, title] [, helpfile, context])","Displays a message in a dialog box, waits for the user to click a button, and returns an Integer indicating which button the user clicked."
NPer,"NPer(rate, pmt, pv[, fv[, type]])","Returns a Double specifying the number of periods for an annuity based on periodic, fixed payments and a fixed interest rate."
NPV,"NPV(rate, values())",Returns a Double specifying the net present value of an investment based on a series of periodic cash flows (payments and receipts) and a discount rate.
Nz,"expression.Nz(Value, ValueIfNull)","You can use the Nz function to return zero, a zero-length string ("" ""), or another specified value when a Variant is Null. Variant."
Oct,Oct(number),Returns a Variant (String) representing the octal value of a number.
Partition,"Partition(number, start, stop, interval)",Returns a Variant (String) indicating where a number occurs within a calculated series of ranges.
Pmt,"Pmt(rate, nper, pv[, fv[, type]])","Returns a Double specifying the payment for an annuity based on periodic, fixed payments and a fixed interest rate."
PPmt,"PPmt(rate, per, nper, pv[, fv[, type]])","Returns a Double specifying the principal payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate."
PV,"PV(rate, nper, pmt[, fv[, type]])","Returns a Double specifying the present value of an annuity based on periodic, fixed payments to be paid in the future and a fixed interest rate."
QBColor,QBColor(color),Returns a Long representing the RGB color code corresponding to the specified color number.
Rate,"Rate(nper, pmt, pv[, fv[, type[, guess]]])",Returns a Double specifying the interest rate per period for an annuity.
RGB,"RGB(red, green, blue)",Returns a Long whole number representing an RGB color value.
Seek,Seek(filenumber),Returns a Long specifying the current read/write position within a file opened using the Open statement.
Shell*,"Shell(pathname[,windowstyle])","Runs an executable program and returns a Variant (Double) representing the program's task ID if successful, otherwise it returns zero."
SLN,"SLN(cost, salvage, life)",Returns a Double specifying the straight-line depreciation of an asset for a single period.
Spc,Spc(n),Used with the Print # statement or the Print method to position output.
StringFromGUID,StringFromGUID(guid),"The StringFromGUID function converts a GUID, which is an array of type Byte, to a string."
Sum,Sum(expr),Returns the sum of a set of values contained in a specified field on a query.
Switch,"Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])",Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.
SYD,"SYD(cost, salvage, life, period)",Returns a Double specifying the sum-of-years' digits depreciation of an asset for a specified period.
Tab,Tab[(n)],Used with the Print # statement or the Print method to position output.
Timer,Timer,Returns a Single representing the number of seconds elapsed since midnight.
TimeSerial,"TimeSerial(hour, minute, second)","Returns a Variant (Date) containing the time for a specific hour, minute, and second."
TimeValue,TimeValue(time),Returns a Variant (Date) containing the time.
TypeName,TypeName(varname),Returns a String that provides information about a variable.
UBound,"UBound(arrayname[, dimension])",Returns a Long containing the largest available subscript for the indicated dimension of an array.

Thanks for your help though!
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 19

Expert Comment

by:Dexstar
ID: 9734290
Nice... Where did you find that?

Dex*
0
 
LVL 1

Author Comment

by:AirResourceSpec
ID: 9734333
I took the list from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/acidxFunctions.asp and individually looked up each function in Access or VB.  It took a while, but my users are lovin' me!  :-)

If you want it, I can email you the Excel file with some nice formatting... just let me know.
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9734675
Very cool.  If you don't mind, I could totally put a copy of that to good use.  My contact info is in my profile... :)

Dex*
0
 
LVL 1

Author Comment

by:AirResourceSpec
ID: 9735030
Ok, I feel like an idiot... I clicked on your name above to view your profile, but I am not seeing your email address.  Where do I find it?  Thanks!  (blush!)
0
 

Expert Comment

by:Dalexan
ID: 36965698
You should post this excel file within this question.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now