Dates Manipulating in Access 1

[skrappySenior]

I am new to Access and VBA. I am learning the old-fashioned way of books with a candle in front of a fireplace....I have read many of the solutions regarding date formating and calculating. For me as a first time user they were too ambigous. I have documented below precise steps that I have learned.

Expressions in Forms - can be added to an unbound control

[COLOR=blue]=date()[/color]

displays date as mm-dd-yyyy

[COLOR=blue]=format(now(),"??")[/color]

displays the part of the current date by replacing the ?? with mm (2-day month), ww (2-digit week of the month 1-53) yy (2-digit year) or yyyy (4-digit year)

Below is a forumula, used to calculate age. The existing table field is called "dob". Added to the form was an unbound text box (right click and bring up properties) in the control source field (on the data or all tab) the following code was entered:

[COLOR=blue]=Format(Now(),"yyyy")-Format([dob],"yyyy")[/color]

 

[adalger]

A better formula for age is

=DateDiff("m", birthday, Now()) / 12

TMTOWDI - it's not just for Perl any more

 

[skrappySenior]

The post by Adalger works. This is the kind of help this old lady needs! I added the rounding function as follows:

=Round(DateDiff("m",[dob],Now())/12,0)

My formula is correct only if you have passed the month of birth in the current year.

 

[adalger]

Good catch. I put that code into a function declared as integer, so it automatically truncated for me.

TMTOWDI - it's not just for Perl any more

 

[Crowley16]

well, you could make it more exact by using "D" and dividing by 365.25...

--------------------
Procrastinate Now!

 

[Remou]

Don't forget the FAQs:

Age Calculation Function
faq181-3769
How to calculate age (another method).
faq181-85
Select birthdays within the next 30 days ...
faq181-1374

And the threads with completed functions:
Calculate Age
thread700-1211994

 

[PHV]

I've posted the following many times:
=DateDiff("yyyy", [DOB], Date()) + (Format([DOB], "mmdd") > Format(Date(), "mmdd"))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886