Add Days from another table to Date in current table

  • Thread starter Thread starter MrPaladin
  • Start date Start date
M

MrPaladin

I've tried searching for an answer and I see alot of reply:[Date]+number and
DateAdd("d",number,[Date]) but I cant seem to connect the dots to get the
number I want from another table.

I have two tables. Table1 is called Procedure and it contains a field
called ExpireDays which is a number (representing days). Table2 is called
Class and has a date field called ClassDate and another date feild called
ExpiresDate.

I want to use a form that will automatically calculate the ExpiresDate by
adding the number of days selected from a record in the Procedure table to
the ClassDate entered on the same form.

No matter what expression I have tried I get an error (or nothing at all
changes).

Any help is appreciated... Thankyou
 
MrPaladin said:
I've tried searching for an answer and I see alot of reply:[Date]+number
and
DateAdd("d",number,[Date]) but I cant seem to connect the dots to get the
number I want from another table.

I have two tables. Table1 is called Procedure and it contains a field
called ExpireDays which is a number (representing days). Table2 is called
Class and has a date field called ClassDate and another date feild called
ExpiresDate.

I want to use a form that will automatically calculate the ExpiresDate by
adding the number of days selected from a record in the Procedure table to
the ClassDate entered on the same form.

No matter what expression I have tried I get an error (or nothing at all
changes).

Any help is appreciated... Thankyou
Click to expand...
 
You can do it as a batch using an update query. To have it happen as you do
data entery on a form you need a query that has both tables joined. Both of
the queries must have a common field such as ClassID. Use an On Update event
of the field ,[Class].[ClassDate] in your form.

SET Class.ExpiresDate = DateAdd("d",Procedure.ExpireDays
,[Class].[ClassDate])
 
Normally you would not need the class.expiredays as I see it if a
Procedure's expiry date can't change over time(?), because then you *should*
calculate it when you need it, using a query, a function (even Dlookup).

hth

pieter

KARL DEWEY said:
You can do it as a batch using an update query. To have it happen as you
do
data entery on a form you need a query that has both tables joined. Both
of
the queries must have a common field such as ClassID. Use an On Update
event
of the field ,[Class].[ClassDate] in your form.

SET Class.ExpiresDate = DateAdd("d",Procedure.ExpireDays
,[Class].[ClassDate])


MrPaladin said:
I've tried searching for an answer and I see alot of reply:[Date]+number
and
DateAdd("d",number,[Date]) but I cant seem to connect the dots to get the
number I want from another table.

I have two tables. Table1 is called Procedure and it contains a field
called ExpireDays which is a number (representing days). Table2 is
called
Class and has a date field called ClassDate and another date feild called
ExpiresDate.

I want to use a form that will automatically calculate the ExpiresDate by
adding the number of days selected from a record in the Procedure table
to
the ClassDate entered on the same form.

No matter what expression I have tried I get an error (or nothing at all
changes).

Any help is appreciated... Thankyou
Click to expand...
Click to expand...
 
Thinking over it a bitthe original plan was to allow manager to change the
expiration as needed and not impact previous classes. But it would suit our
needs not to worry about that case and allow the expiration date to be
calc'ed in querie as you suggest.

I'll still add the expected expiration onto the form for ease.

Thank you all for the great responces.

Pieter Wijnen said:
Normally you would not need the class.expiredays as I see it if a
Procedure's expiry date can't change over time(?), because then you *should*
calculate it when you need it, using a query, a function (even Dlookup).

hth

pieter

KARL DEWEY said:
You can do it as a batch using an update query. To have it happen as you
do
data entery on a form you need a query that has both tables joined. Both
of
the queries must have a common field such as ClassID. Use an On Update
event
of the field ,[Class].[ClassDate] in your form.

SET Class.ExpiresDate = DateAdd("d",Procedure.ExpireDays
,[Class].[ClassDate])


MrPaladin said:
I've tried searching for an answer and I see alot of reply:[Date]+number
and
DateAdd("d",number,[Date]) but I cant seem to connect the dots to get the
number I want from another table.

I have two tables. Table1 is called Procedure and it contains a field
called ExpireDays which is a number (representing days). Table2 is
called
Class and has a date field called ClassDate and another date feild called
ExpiresDate.

I want to use a form that will automatically calculate the ExpiresDate by
adding the number of days selected from a record in the Procedure table
to
the ClassDate entered on the same form.

No matter what expression I have tried I get an error (or nothing at all
changes).

Any help is appreciated... Thankyou
Click to expand...
Click to expand...
Click to expand...
 
Back
Top