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