Date and Time Functions and Formulas

Note: When your are working on formulas in the control's property dialog window or in query pertinent fields, right click and choose "Zoom", a special enlarged window will open to facilitate your work.

Note: Click on the Wizard's stick to open the Expression Generator. In the left bottom window of the dialog box, you can click on "Functions" to select any of the functions offered by Access.

==========
Here are the functions that I use and some examples:

Date, Between, And, Or, Day, Month, Year, Now, WeekDay, SerialDate, Left, Right, &, +, -, /, *, Len, IIF, Nz, Sum

Nz
The Nz function allows you to make calculations even if some of the controls are empty, or have a "Null" value". If you don't use this functions, you cannot sum a series of text boxes if even one as a Null value.
=NZ(text1,0)+NZ(text2,0)....

Using a format like "Fixed 2 decimals" in a text box, doesn't round up the result of a calculation to 2 decimals, it just shows the result with 2 decimals but calculates with all the decimals. Use the "Round" function even with "+" and "-", it rounds up your result to the number of decimals defined in the format. For some unknown reasons, I ended up with 0.999952 as the result of "1.3-0.3"
=round([textBox1]/textBox2

===================
Date and Time Functions and Formulas

Today
Date()

Yesterday
Date()-1

Do not use Now() because it includes date and time

Last 7 days (do not confuse with last week)
Between Date()-1 and Date()-7

Current week
Between Date()-(WeekDay(Date())-1) AND Date()+(7-WeekDay(Date()))


Last week
Between Date()-6-WeekDay(Date()) AND Date()-WeekDay(Date())

Week before last week
Between Date()-13-WeekDay(Date()) AND Date()-WeekDay(Date())-7

Current month:
Month([yourDateField])=Month(Now())

Current year:
Year([yourDateField])=Year(Now())

Last year (do not confuse with year ending today):
Year([yourDateField])=Year(Now())-1

Year ending today:
Between Date() And Date()-365
or more precisely:
Between Date() and SerialDate(Year(Now())-1;Month(Now());Day(Now()))+1

http://codeprogramer.blogspot.com

Table Tips

Do not use lists in the fields of the tables, you will be stuck with them along the way. Create List Boxes or Combo Boxes in forms and reports when you need them.

A double Primary Key means that you can have two records "Leclerc" but not two records "Pierre"/"Leclerc". Remember though that to be able to enter data through a query linked to such a table, both fields will have to be included in the query.

A numerical field is a field comprising numbers on which you will want to do mathematical operations. So, a telephone number, a postal code or an invoice number are text fields.

You can put numbers in both numerical and text fields of an Access table but the sorting results will be different:
In numerical fields: 1 ,2 ,3 ,4 ,5 ,6.......10.....20....30
In text fields: 1, 10, 11, 12....,2, 20, 21....3, 30, 31

Linking tables can present some limits. If you have a query within another query and both need to look at a linked table at the same time, ODBC cannot handle it. Sometimes, imported table are the solution especially in a datamart that is not used to enter data in the main database but to generate reports.

Query Tips

The query is where users and developers meet, it is the most vital part of the database.

Once the data have been queried, they can be organized and analyzed by the user on forms and reports to achieve the ultimate goal of a database: to inform.



Notes

If your query or your table feeds a form, there is no need to filter and sort whithin the query. You can manage these aspects in the "Filter/Sort" query of the form that you can access opening the form and going to the menu bar "Record/Filter/Advanced Filter/Sort". So, a query or a table can feed many forms.

There is no "If" in Access, there is "Iif". So, in the criteria zone or in expressions, use "Iif" instead of "If". This rule doesn't apply in VBA coding except when you are setting an object formula through coding.

For each report or form that you develop, always create a query. You never know when you will need a field from another table in your report or your from.

When your Information Management System includes reports in Excel, create a query in Access and use Microsoft Query only to get the results of the query created in Access. Access is superior to Microsoft Query to create queries.

Right click in the criteria or the field box and select "Zoom" when you are working with long criteria or formulas in calculated fields.

To replace a word in a long formula, double-click on it and enter the new value. It is much simpler than clicking at the beginning of the word and dragging the pointer over the word.