Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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!

Thanks!

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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*

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial(CSV - cut and paste)

CBool,CBool(expression),Co

CByte,CByte(expression),Co

CCur,CCur(expression),"Coe

CDate,CDate(expression),Co

CDbl,CDbl(expression),"Coe

-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values."

CDec,CDec(expression),"Coe

+/-7.922816251426433759354

CInt,CInt(expression),"Coe

CLng,CLng(expression),"Coe

CSng,CSng(expression),Coer

CStr,CStr(expression),Coer

CVar,CVar(expression),Coer

CVDate,CVDate(expression),

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),"

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,

DateDiff,"DateDiff(interva

DatePart,"DatePart(interva

DateSerial,"DateSerial(yea

DateValue,DateValue(date),

Weekday,"Weekday(date, [firstdayofweek])",Returns

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),"Retur

Second,Second(time),"Retur

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

Year,Year(date),Returns a Variant (Integer) containing a whole number representing the year.

IsDate,IsDate(expression),

Format,"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),

LCase,LCase(string),Return

UCase,UCase(string),"Retur

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),"Retur

RTrim,RTrim(string),"Retur

Trim,Trim(string),"Returns

Space,Space(number),Return

String,"String(number, character)",Returns a Variant (String) containing a repeating character string of the length specified.

IsNumeric,IsNumeric(expres

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

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),Retur

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),Calculat

CreateObject,"CreateObject

CurDir,CurDir[(drive)],Ret

CVErr,CVErr(errornumber),R

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(a

DDERequest,"DDERequest(cha

DDESend,"DDESend(applicati

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( ),Yield

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({envstrin

EOF,EOF(filenumber),Return

Error,Error[(errornumber)]

EuroConvert,"EuroConvert(n

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(filenum

FileDateTime*,FileDateTime

FileLen*,FileLen(pathname)

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[(rangenu

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,"GetAllSett

GetAttr*,GetAttr(pathname)

GetObject,"GetObject([path

GetSetting*,"GetSetting(ap

GUIDFromString,GUIDFromStr

Hex,Hex(number),Returns a String representing the hexadecimal value of a number.

HyperlinkPart,,

IMEStatus,IMEStatus,Return

Input*,,

InputBox,"InputBox(prompt[

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),R

IsEmpty,IsEmpty(expression

IsError,IsError(expression

IsMissing,IsMissing(argnam

IsObject,IsObject(identifi

Last,Last(expr),Return a field value from the last record in the result set returned by a query.

LBound,"LBound(arrayname[,

Loc,Loc(filenumber),Return

LOF,LOF(filenumber),"Retur

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(numbe

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),Ret

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),Retu

Shell*,"Shell(pathname[,wi

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,StringFromG

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]])",Evaluat

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(hou

TimeValue,TimeValue(time),

TypeName,TypeName(varname)

UBound,"UBound(arrayname[,

Thanks for your help though!

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

Dex*

Microsoft Access

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

> 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*