Calculate time frame automatically


J

Jessica

I have a querie with fields taken from a specific table. The field are DOB,
time receiving services, date of entry and Age.
1. I need the age field to automatically have the age of the child base on
the DOB field and current date.

2. I need the time in care field to automatically enter the date of time in
care base on date of entry and current date.Then the difference between two
dates

all information needs to be specified in year and months
 
Ad

Advertisements

B

Beetle

You asked essentially the same question two days ago, which was answered.
Here is a repost of the answer if you're having trouble finding it.

***

These calculations should not be done at the table level, so you should
not have fields for clients age and time receiving services in your table.

It is rarely advisable to store calculated values in a table. They should
be done in a calculated field in a query, or in calculated controls on
forms/reports.

An example of an age calculation in a query field would be;

Age: DateDiff("yyyy", [BirthDate], Date) +
(Date < DateSerial(Year(Date), Month([BirthDate]), Day([BirthDate])))

You would then use the query where you would have otherwise used the table.

As far as your time receiving services, you would just calculate the
difference
between the entry date and today's date, or perhaps some type of
ending date field (using the DateDiff function). Again this calculation would
not be stored in your table.

To have the results displayed in years and months, you could use the
Format function.
 
J

Jessica

It work but is asking me to enter a parameter value, which i dont want to do.
I need for it to calcualte from todays date automatically.

Beetle said:
You asked essentially the same question two days ago, which was answered.
Here is a repost of the answer if you're having trouble finding it.

***

These calculations should not be done at the table level, so you should
not have fields for clients age and time receiving services in your table.

It is rarely advisable to store calculated values in a table. They should
be done in a calculated field in a query, or in calculated controls on
forms/reports.

An example of an age calculation in a query field would be;

Age: DateDiff("yyyy", [BirthDate], Date) +
(Date < DateSerial(Year(Date), Month([BirthDate]), Day([BirthDate])))

You would then use the query where you would have otherwise used the table.

As far as your time receiving services, you would just calculate the
difference
between the entry date and today's date, or perhaps some type of
ending date field (using the DateDiff function). Again this calculation would
not be stored in your table.

To have the results displayed in years and months, you could use the
Format function.

--
_________

Sean Bailey


Jessica said:
I have a querie with fields taken from a specific table. The field are DOB,
time receiving services, date of entry and Age.
1. I need the age field to automatically have the age of the child base on
the DOB field and current date.

2. I need the time in care field to automatically enter the date of time in
care base on date of entry and current date.Then the difference between two
dates

all information needs to be specified in year and months
 
J

Jessica

I'm not sure if you got my previous message, but is asking me to enter a
parameter value. Instead of just doing the calculation by it self from todays
date

Beetle said:
You asked essentially the same question two days ago, which was answered.
Here is a repost of the answer if you're having trouble finding it.

***

These calculations should not be done at the table level, so you should
not have fields for clients age and time receiving services in your table.

It is rarely advisable to store calculated values in a table. They should
be done in a calculated field in a query, or in calculated controls on
forms/reports.

An example of an age calculation in a query field would be;

Age: DateDiff("yyyy", [BirthDate], Date) +
(Date < DateSerial(Year(Date), Month([BirthDate]), Day([BirthDate])))

You would then use the query where you would have otherwise used the table.

As far as your time receiving services, you would just calculate the
difference
between the entry date and today's date, or perhaps some type of
ending date field (using the DateDiff function). Again this calculation would
not be stored in your table.

To have the results displayed in years and months, you could use the
Format function.

--
_________

Sean Bailey


Jessica said:
I have a querie with fields taken from a specific table. The field are DOB,
time receiving services, date of entry and Age.
1. I need the age field to automatically have the age of the child base on
the DOB field and current date.

2. I need the time in care field to automatically enter the date of time in
care base on date of entry and current date.Then the difference between two
dates

all information needs to be specified in year and months
 
B

Beetle

Can you post the SQL of your query?

(open the query in design view, go to View/SQL View, then copy and
paste it in a response)
--
_________

Sean Bailey


Jessica said:
I'm not sure if you got my previous message, but is asking me to enter a
parameter value. Instead of just doing the calculation by it self from todays
date

Beetle said:
You asked essentially the same question two days ago, which was answered.
Here is a repost of the answer if you're having trouble finding it.

***

These calculations should not be done at the table level, so you should
not have fields for clients age and time receiving services in your table.

It is rarely advisable to store calculated values in a table. They should
be done in a calculated field in a query, or in calculated controls on
forms/reports.

An example of an age calculation in a query field would be;

Age: DateDiff("yyyy", [BirthDate], Date) +
(Date < DateSerial(Year(Date), Month([BirthDate]), Day([BirthDate])))

You would then use the query where you would have otherwise used the table.

As far as your time receiving services, you would just calculate the
difference
between the entry date and today's date, or perhaps some type of
ending date field (using the DateDiff function). Again this calculation would
not be stored in your table.

To have the results displayed in years and months, you could use the
Format function.

--
_________

Sean Bailey


Jessica said:
I have a querie with fields taken from a specific table. The field are DOB,
time receiving services, date of entry and Age.
1. I need the age field to automatically have the age of the child base on
the DOB field and current date.

2. I need the time in care field to automatically enter the date of time in
care base on date of entry and current date.Then the difference between two
dates

all information needs to be specified in year and months
 
J

John W. Vinson

I have a querie with fields taken from a specific table. The field are DOB,
time receiving services, date of entry and Age.
1. I need the age field to automatically have the age of the child base on
the DOB field and current date.

2. I need the time in care field to automatically enter the date of time in
care base on date of entry and current date.Then the difference between two
dates

These fields SHOULD NOT EXIST in your table.

If you store the child's age in the database today, you can be absolutely
certain that it will be *wrong* within thirty days.

Instead, calculate it on demand using a calculated field in a Query. You can
do this in years and months (or any desired date units) using a handy VBA
function that MVP Doug Steele developed:

http://www.accessmvp.com/djsteele/Diff2Dates.html
 
Ad

Advertisements

J

Jessica

SELECT Clients.*,
DateDiff("yyyy",[BirthDate],[Date])+([Date]<DateSerial(Year([Date]),Month([BirthDate]),Day([BirthDate])))

Beetle said:
You asked essentially the same question two days ago, which was answered.
Here is a repost of the answer if you're having trouble finding it.

***

These calculations should not be done at the table level, so you should
not have fields for clients age and time receiving services in your table.

It is rarely advisable to store calculated values in a table. They should
be done in a calculated field in a query, or in calculated controls on
forms/reports.

An example of an age calculation in a query field would be;

Age: DateDiff("yyyy", [BirthDate], Date) +
(Date < DateSerial(Year(Date), Month([BirthDate]), Day([BirthDate])))

You would then use the query where you would have otherwise used the table.

As far as your time receiving services, you would just calculate the
difference
between the entry date and today's date, or perhaps some type of
ending date field (using the DateDiff function). Again this calculation would
not be stored in your table.

To have the results displayed in years and months, you could use the
Format function.

--
_________

Sean Bailey


Jessica said:
I have a querie with fields taken from a specific table. The field are DOB,
time receiving services, date of entry and Age.
1. I need the age field to automatically have the age of the child base on
the DOB field and current date.

2. I need the time in care field to automatically enter the date of time in
care base on date of entry and current date.Then the difference between two
dates

all information needs to be specified in year and months
 
B

Beetle

Sorry, I forgot to put () after the word Date in my example, so your query
interpreted it as a field name. Modify it like;

SELECT Clients.*,
DateDiff("yyyy",[BirthDate],Date())+(Date()<DateSerial(Year(Date()),Month([BirthDate]),Day([BirthDate])))


--
_________

Sean Bailey


Jessica said:
SELECT Clients.*,
DateDiff("yyyy",[BirthDate],[Date])+([Date]<DateSerial(Year([Date]),Month([BirthDate]),Day([BirthDate])))

Beetle said:
You asked essentially the same question two days ago, which was answered.
Here is a repost of the answer if you're having trouble finding it.

***

These calculations should not be done at the table level, so you should
not have fields for clients age and time receiving services in your table.

It is rarely advisable to store calculated values in a table. They should
be done in a calculated field in a query, or in calculated controls on
forms/reports.

An example of an age calculation in a query field would be;

Age: DateDiff("yyyy", [BirthDate], Date) +
(Date < DateSerial(Year(Date), Month([BirthDate]), Day([BirthDate])))

You would then use the query where you would have otherwise used the table.

As far as your time receiving services, you would just calculate the
difference
between the entry date and today's date, or perhaps some type of
ending date field (using the DateDiff function). Again this calculation would
not be stored in your table.

To have the results displayed in years and months, you could use the
Format function.

--
_________

Sean Bailey


Jessica said:
I have a querie with fields taken from a specific table. The field are DOB,
time receiving services, date of entry and Age.
1. I need the age field to automatically have the age of the child base on
the DOB field and current date.

2. I need the time in care field to automatically enter the date of time in
care base on date of entry and current date.Then the difference between two
dates

all information needs to be specified in year and months
 
T

Tom van Stiphout

On Fri, 28 Nov 2008 13:05:01 -0800, Jessica

Age should NOT be stored in the table, because it is a calculated
field. Use the DateDiff function to calculate age.

Fields can have a default value. The default value can be Date() or
Now(). See the table in design view.

-Tom.
Microsoft Access MVP
 
J

Jessica

What if a child is only a months

Beetle said:
Sorry, I forgot to put () after the word Date in my example, so your query
interpreted it as a field name. Modify it like;

SELECT Clients.*,
DateDiff("yyyy",[BirthDate],Date())+(Date()<DateSerial(Year(Date()),Month([BirthDate]),Day([BirthDate])))


--
_________

Sean Bailey


Jessica said:
SELECT Clients.*,
DateDiff("yyyy",[BirthDate],[Date])+([Date]<DateSerial(Year([Date]),Month([BirthDate]),Day([BirthDate])))

Beetle said:
You asked essentially the same question two days ago, which was answered.
Here is a repost of the answer if you're having trouble finding it.

***

These calculations should not be done at the table level, so you should
not have fields for clients age and time receiving services in your table.

It is rarely advisable to store calculated values in a table. They should
be done in a calculated field in a query, or in calculated controls on
forms/reports.

An example of an age calculation in a query field would be;

Age: DateDiff("yyyy", [BirthDate], Date) +
(Date < DateSerial(Year(Date), Month([BirthDate]), Day([BirthDate])))

You would then use the query where you would have otherwise used the table.

As far as your time receiving services, you would just calculate the
difference
between the entry date and today's date, or perhaps some type of
ending date field (using the DateDiff function). Again this calculation would
not be stored in your table.

To have the results displayed in years and months, you could use the
Format function.

--
_________

Sean Bailey


:

I have a querie with fields taken from a specific table. The field are DOB,
time receiving services, date of entry and Age.
1. I need the age field to automatically have the age of the child base on
the DOB field and current date.

2. I need the time in care field to automatically enter the date of time in
care base on date of entry and current date.Then the difference between two
dates

all information needs to be specified in year and months
 
K

Ken Sheridan

1. Paste the following function into a standard module in your database:

Public Function GetAge(varDob, Optional varDateAt)

Dim intYears As Integer, intMonths As Integer

' return date at current date if no second argument
' passed into function
If IsMissing(varDateAt) Then varDateAt = Date

If Not IsNull(varDob) Then
' get difference in years
intYears = DateDiff("yyyy", varDob, varDateAt)
' get difference in months
intMonths = DateDiff("m", varDob, _
DateSerial(Year(varDob), Month(varDateAt), Day(varDateAt)))
' adjust result if date of birth falls later in year
' than date at which age to be calculated
If varDateAt < DateSerial(Year(varDateAt), Month(varDob),
Day(varDob)) Then
intYears = intYears - 1
intMonths = intMonths + 12
End If

GetAge = intYears & " year" & _
IIf(intYears <> 1, "s", "") & ", " & _
intMonths & " month" & _
IIf(intMonths <> 1, "s", "")
End If

End Function


2. Save the module under a different name from that of the function, e.g.
mdlDateStuff

3. In your query call the function like so:

SELECT *,
GetAge([DoB]) As Age,
GetAge([Date of Entry]) As [Time in Care],
GetAge([Dob],[Date of Entry]) As [Age at Date of Entry]
FROM Clients;

The age et will be returned in the format #year(s), #month(s).

I've assumed that by "the difference between two" it’s the age at the time
of entry you wish to calculate.

Ken Sheridan
Stafford, England

Jessica said:
What if a child is only a months

Beetle said:
Sorry, I forgot to put () after the word Date in my example, so your query
interpreted it as a field name. Modify it like;

SELECT Clients.*,
DateDiff("yyyy",[BirthDate],Date())+(Date()<DateSerial(Year(Date()),Month([BirthDate]),Day([BirthDate])))


--
_________

Sean Bailey


Jessica said:
SELECT Clients.*,
DateDiff("yyyy",[BirthDate],[Date])+([Date]<DateSerial(Year([Date]),Month([BirthDate]),Day([BirthDate])))

:

You asked essentially the same question two days ago, which was answered.
Here is a repost of the answer if you're having trouble finding it.

***

These calculations should not be done at the table level, so you should
not have fields for clients age and time receiving services in your table.

It is rarely advisable to store calculated values in a table. They should
be done in a calculated field in a query, or in calculated controls on
forms/reports.

An example of an age calculation in a query field would be;

Age: DateDiff("yyyy", [BirthDate], Date) +
(Date < DateSerial(Year(Date), Month([BirthDate]), Day([BirthDate])))

You would then use the query where you would have otherwise used the table.

As far as your time receiving services, you would just calculate the
difference
between the entry date and today's date, or perhaps some type of
ending date field (using the DateDiff function). Again this calculation would
not be stored in your table.

To have the results displayed in years and months, you could use the
Format function.

--
_________

Sean Bailey


:

I have a querie with fields taken from a specific table. The field are DOB,
time receiving services, date of entry and Age.
1. I need the age field to automatically have the age of the child base on
the DOB field and current date.

2. I need the time in care field to automatically enter the date of time in
care base on date of entry and current date.Then the difference between two
dates

all information needs to be specified in year and months
 
Ad

Advertisements

G

Guest

Jessica said:
I have a querie with fields taken from a specific table. The field are DOB,
time receiving services, date of entry and Age.
1. I need the age field to automatically have the age of the child base on
the DOB field and current date.

2. I need the time in care field to automatically enter the date of time
in
care base on date of entry and current date.Then the difference between
two
dates

all information needs to be specified in year and months
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top