473,683 Members | 3,757 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Access Built-in Functions

MMcCarthy
14,534 Recognized Expert Moderator MVP
Access has a number of built-in functions which can be generally used in queries or VBA code. Some of the more common ones are:

Note: anything in square brackets [] is optional

Date Functions

Date() - Returns the current system date

Now() - Returns the current system timestamp (date and time)

Year(dateValue) - Returns the 4 digit year of any given date

Month(dateValue ) - Returns the month number of any given date

MonthName(numbe r, [abreviate]) - Returns the monthname for any given number 1 - 12, putting true in the [abreviate section will abreviate the month name. The default is false. To get the month name of any given date this function would have to be used in conjunction with the previous function. e.g. MonthName(Month (dateValue))

Weekday(dateVal ue, [FirstDayofWeek]) - Returns the day of the week of any given date value 1-7. The first day of the week can be set e.g. vbMonday. The default is Sunday.

WeekdayName(num ber, [abbreviate], [firstdayofweek]) - Returns the weekday name of any given number. To get the weekday name of a given date this function can be used in conjunction with the previous one. e.g. WeekdayName(Wee kday(dateValue) )

Day(dateValue) - Returns the day of the month (1-31) of any given date value.

DateSerial(year , month, day) - Returns a date for any given year, month and day

Time() - Returns the current system time

Hour(timeValue) - Returns the hour value of any given time or timestamp

Minute(timeValu e) - Returns the minute value of any given time or timestamp

Second(timeValu e) - Returns the second value of any given time or timestamp

For the following functions you will need to know what intervals are:
Expand|Select|Wrap|Line Numbers
  1. Value  Description
  2. yyyy   Year
  3. q      Quarter                                         
  4. m      Month                                         
  5. y      Day of the year                                         
  6. d      Day                                         
  7. w      Weekday                                         
  8. ww     Week                                         
  9. h      Hour                                         
  10. n      Minute                                         
  11. s      Second
  12.  
DateAdd(interva l, number, dateValue) - Returns the new date value after the addition of a number of specified intervals. e.g. DateAdd("yyyy", 3, #01/05/2001#) would return "01/05/2004"

DateDiff(interv al, dateValue1, dateValue2, [firstdayofweek], [firstweekofyear]) - Returns the difference in the specified interval between two given dates. e.g. DateDiff("yyyy" , #01/05/2001#, #02/07/2006#) would return 5. firstdayofweek is optional. It specifies the first day of the week. Access assumes that Sunday is the first day of the week. firstweekofyear is optional. It specifies the first week of the year. Access assumes that the week containing Jan 1st is the first week of the year.

DatePart(interv al, dateValue, [firstdayofweek], [firstweekofyear]) - Returns the specified part of any given date. e.g. DatePart("yyyy" , #02/07/2005#) would return 2005.

Number Functions

The following are normally used in SELECT queries known as aggregate queries. Please note if other fields are also returned by the query then the results will be grouped on those field values.

Avg(fieldName) - Returns the average of a series of values in a given field

Count(fieldName) - Returns the total number of records returned by a query

Sum(fieldName) - Returns the sum of a series of values in a given field

Max(fieldName) - Returns the largest of a series of values in a given field

Min(fieldName) - Returns the smallest of a series of values in a given field

The following can be used in queries or VBA code.

Int(numberValue ) - Returns the integer part of any given number. Please note this will not round a number up to the nearest integer.

Round(numberVal ue, [decimal places) - Returns a number rounded to the specified number of decimal places. If the decimal places is left blank then the default is 0 which means an integer is returned. The Round function behaves a little unexpectedly as it uses round-to-even logic. If the number that you are rounding ends with a 5, the Round function will round the expression so that the last digit is an even number.
For example:
Round(22.35, 1) would return 12.4 (rounds up)
Round(22.65, 1) would return 12.6 (rounds down)
Round(22.75, 1) would return 12.8 (rounds up)

Val(stringValue ) - Returns any numbers found in a string

String Functions

Instr([start position], StringBeingSear ched, StringSearchedF or, [compare]) - Returns the position number in the string being searched of the string or character being searched for. The start position will default to position 1 (the start of the string). The compare is for the type of comparison to perform. The valid choices are vbUseCompareOpt ion, vbBinaryCompare , vbTextCompare, and vbDatabaseCompa re.

Len(StringValue ) - Returns the number of characters in a given string. This will include spaces.

Left(stringValu e, Number of characters) - Return a substring of the required length starting at the left of the string.

Right(stringVal ue, Number of characters) - Return a substring of the required length starting at the right of the string.

Mid(stringValue , Start Postion, Number of characters) - Return a substring of the required length starting at the given start position in the string.

Domain Aggregate Functions (functions which lookup values in a table or query)

DLookup (expression, domain, [criteria]) - Returns a single value found.In expression normally you would put a field name however it can also be a calculation. For domain you would put the table name or query name. Criteria is optional. Think of it as the WHERE clause. e.g. DLookup("OrderD ate", "Orders", "OrderID = 10248")

DCount (expression, domain, [criteria]) - Returns the number of records in a table or query based on the criteria given. Criteria is optional.

DMax (expression, domain, [criteria]) - Returns the maximun value in the specified field.

DSum (expression, domain, [criteria]) - Returns the sum of values in a specified field or calculated expression.

Others

IIf (condition, value if true, value if false) - Returns whatever value is placed in Value if true if condition is true or whatever is in Value if false if conditon is false. In this case condition is the value you are testing. e.g. IIf(NumberField =0, "no value", NumberField)

IsNull(expressi on) - Returns true if the expression evaluates to Null and false if it doesn't.

Nz (variant, [value if null]) - Returns the value if null whenever the variant has a null value. The default for value if null is an empty string. Although this specifies a variant it will accept a value of any datatype and treat it as a variant.
Nov 27 '07 #1
1 19398
Coni
14 New Member
Thank you for posting these useful functions.
Sep 7 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

1
1247
by: sss | last post by:
Hello All, I am currently creating a browser interface for our thick client which was built on Visual Basic using COM ( a three tier environment). We support environments where the SQL database server is separate from the application server. The problem I am facing is this.
1
2681
by: acrocker | last post by:
I would like to provide access to users who need to be taken directly to the relevant page without further navigation instructions. Unfortunately the page I want to access is built using a javascript query string within an html form. I do not have enough knowledge to decode how to build the direct URL. Here is the base page;- o http://full-time.thefa.com/gen/Index.do?league=8673133
9
3206
by: Alan Lane | last post by:
Hello world: Background: Yesterday, January 21, Doug Steele was kind enough to help me out on a Left Join problem. I was trying to return all stores and their Gross Adds for December, 2004 (even though some of them had no Gross Adds for that month). My query would only show the non-zero GA stores. Doug stated "It's because of the Where clause. The stores which don't have any records for the period in question are being eliminated...
16
4007
by: MLH | last post by:
I have created an A97 runtime distribution for the first time in my life this morning. I had precious little time to do it and was unable to create the distributable installation fileset with the option of it creating a shortcut for me. I'm sure it will. But for now, what exactly can I enter into the shortcut properties to describe the 'location' information that will have the runtime A97 open the subject mde file using the desired...
43
2894
by: sinister | last post by:
Is MS Access a true RDBMS?
2
1502
by: richp | last post by:
I recently built an Access 2003 database on my workstation. I have a client .mdb file with my forms/code, which links tables from an .mdb on our server. When I copy the client .mdb to a user's workstation and use it, the links are fine but I get an error on the following line: Line: Me.txtWhen = Now Error: "You can't assign a value to this object" >From what I can tell the machines have the same OS, Access version (2003), and Jet...
48
4925
by: meyer | last post by:
Hi everyone, which compiler will Python 2.5 on Windows (Intel) be built with? I notice that Python 2.4 apparently has been built with the VS2003 toolkit compiler, and I read a post from Scott David Daniels where he said that probably the VS2003 toolkit will be used for Python 2.5 again. However, even before the release of Python 2.5, I cannot seem to find many retailers around here that still carry Visual Studio 2003, and some were a...
1
1538
by: brownr9 | last post by:
Hello, We're new to Access 2003 programming, and we are developing a database for our church. We built the database from the IMPORT of a Excel spreadsheet. Now we are trying to write a FORM that we can use to serach for 1 or more criteria, and display the results from the database. For example,
1
1405
by: anthony.k.farrant | last post by:
Hello everybody, I am new to Access. I have to rewrite an app built with Access upon SQL Server. Access links to SQL Server with linked tables. Now i'd like to change the connection properties for the tables. I open up design view and the properties and I change the description. But my changes never stay. I know the field name is "description" so
1
9611
by: rrrelic | last post by:
Can someone help me with this? I am running Access 2003 and attempting to import and append multiple XML files into Access. I currently do this process using the Import tool built into Access’s File menu. The only problem with this is that I have to do one file at a time and I have hundreds of files to import. I would like to be able to use code to loop through all the files contained within a specified folder and import and append the...
0
8504
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9043
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8771
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5789
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4299
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4519
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2934
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2195
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1921
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.