MS access Form calculation

  • Thread starter Thread starter dengi
  • Start date Start date
D

dengi

Hi,

I'm trying to workout how to build an expression on my form using MS Access.
Iam trying to get the system to calculate a date (i.e. expiry date of a
contract), for example the user enters the "signed date" (e.g. 20/04/2009)
and the "contract term" (e.g. 3years) how do I express this function so the
system calculates the expiry date (20/04/2012)?

thnx
dengi
 
Hi,

I'm getting an error below with that expression. When the user enters the
signed date and the contract term, then I want the system to calculate the
expiry date based on those 2 variables, how do I build an expression for
that?

thnx.

Douglas J. Steele said:
DateAdd("yyyy", 3, [SignedDate])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dengi said:
Hi,

I'm trying to workout how to build an expression on my form using MS
Access.
Iam trying to get the system to calculate a date (i.e. expiry date of a
contract), for example the user enters the "signed date" (e.g.
20/04/2009)
and the "contract term" (e.g. 3years) how do I express this function so
the
system calculates the expiry date (20/04/2012)?

thnx
dengi
Click to expand...
Click to expand...
 
DateAdd("yyyy", Nz(ContractTerm, 0), Nz([SignedDate], Date))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dengi said:
Hi,

I'm getting an error below with that expression. When the user enters the
signed date and the contract term, then I want the system to calculate the
expiry date based on those 2 variables, how do I build an expression for
that?

thnx.

Douglas J. Steele said:
DateAdd("yyyy", 3, [SignedDate])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dengi said:
Hi,

I'm trying to workout how to build an expression on my form using MS
Access.
Iam trying to get the system to calculate a date (i.e. expiry date of a
contract), for example the user enters the "signed date" (e.g.
20/04/2009)
and the "contract term" (e.g. 3years) how do I express this function so
the
system calculates the expiry date (20/04/2012)?

thnx
dengi
Click to expand...
Click to expand...
Click to expand...
 
still not working. apologies if i have not clarified properly. i have 3
fields in my form: install date, contract term & contract expiry date. the
user only enters the data in two fields: install date (e.g. 20/04/2009) and
contract term (e.g. 2 (in years)). i am trying to get the third field to
populate automatically with the date the contract will expire (so in this
case it should come up with 20/04/2011). how can i do that?
Douglas J. Steele said:
DateAdd("yyyy", Nz(ContractTerm, 0), Nz([SignedDate], Date))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dengi said:
Hi,

I'm getting an error below with that expression. When the user enters the
signed date and the contract term, then I want the system to calculate
the expiry date based on those 2 variables, how do I build an expression
for that?

thnx.

Douglas J. Steele said:
DateAdd("yyyy", 3, [SignedDate])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi,

I'm trying to workout how to build an expression on my form using MS
Access.
Iam trying to get the system to calculate a date (i.e. expiry date of a
contract), for example the user enters the "signed date" (e.g.
20/04/2009)
and the "contract term" (e.g. 3years) how do I express this function so
the
system calculates the expiry date (20/04/2012)?

thnx
dengi
Click to expand...
Click to expand...
Click to expand...
 
If you set the ControlSource of the third control to the formula I gave, you
should get what you're looking for.

If you're hoping to update a field in your table with that calculated value,
don't. As fellow Access MVP John Vinson likes to say "Storing calculated
data generally accomplishes only three things: it wastes disk space, it
wastes time (a disk fetch is much slower than almost any reasonable
calculation), and it risks data validity, since once it's stored in a table
either the Total or one of the fields that goes into the total may be
changed, making the value WRONG."

You can always create a query with a calculated field and use that query
wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dengi said:
still not working. apologies if i have not clarified properly. i have 3
fields in my form: install date, contract term & contract expiry date.
the user only enters the data in two fields: install date (e.g.
20/04/2009) and contract term (e.g. 2 (in years)). i am trying to get the
third field to populate automatically with the date the contract will
expire (so in this case it should come up with 20/04/2011). how can i do
that?
Douglas J. Steele said:
DateAdd("yyyy", Nz(ContractTerm, 0), Nz([SignedDate], Date))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dengi said:
Hi,

I'm getting an error below with that expression. When the user enters
the signed date and the contract term, then I want the system to
calculate the expiry date based on those 2 variables, how do I build an
expression for that?

thnx.

DateAdd("yyyy", 3, [SignedDate])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi,

I'm trying to workout how to build an expression on my form using MS
Access.
Iam trying to get the system to calculate a date (i.e. expiry date of
a
contract), for example the user enters the "signed date" (e.g.
20/04/2009)
and the "contract term" (e.g. 3years) how do I express this function
so the
system calculates the expiry date (20/04/2012)?

thnx
dengi
Click to expand...
Click to expand...
Click to expand...
 
Back
Top