Production Date/Expiry Date

[William Bradley]

I have two cells on a form. One of them is the "Production Date" and the
other is the "Expiry Date". The "Expiry Date" is 183 days after the
"Production Date."

On an Excel spreadsheet, the "Expiry Date" is automatically entered, when
the "Production Date" is entered. To do this the "Expiry Date" cell carries
the following formula: "=A15+183".

I would like to be able to do the same on an Access Form, with the results
written to the underlying table.

Any help is appreciated,

Bill.

[Rick Brandt]

"William Bradley" <br******@magma.ca> wrote in message
news:zf********************@magma.ca...

I have two cells on a form. One of them is the "Production Date" and the
other is the "Expiry Date". The "Expiry Date" is 183 days after the
"Production Date."

On an Excel spreadsheet, the "Expiry Date" is automatically entered, when
the "Production Date" is entered. To do this the "Expiry Date" cell carries
the following formula: "=A15+183".

I would like to be able to do the same on an Access Form, with the results
written to the underlying table.

This all made perfect sense until the last sentence. Why do you want to store this
value rather than calculate it on the fly? That is what Excel is doing. Why not
have Access do the same? Storing this in the table would make as much sense as
writing a macro in Excel that would Add 183 to the values found in column A and
writing the result to column B. Then every time you change something in column A you
would re-run the macro to make sure that column B is correct. Sounds silly when you
can just enter an expression in column B to do all of this for you automatically
doesn't it?

Just use a control on the form with an expression as its ControlSource that will do
the calculation and display the result. Saving it to the table accomplishes nothing
useful.

[William Bradley]

"Tim Satterwhite" <ti*****************@ucsfmedctr.org> wrote in message
news:bj*********@itssrv1.ucsf.edu...

Not to get too far off the subject, but it might be useful to store the
expiry date.

If the user is allowed or required by business practice (say, in the case of negotiations with the database user's customers) to override the default of production + 183 days, then it needs to be stored.

Of course, this all depends on what the purpose of "production" and "expiry" are. Whether the data should be stored or not is totally dependent on the
need for that derived information.

We manufacture biological products used in both medical, veterinary,
industrial and food investigations. We need to have hard coded information
on both the production date and expiry date of each product as it is
produced.

Bill.

[William Bradley]

"Marshall Barton" <ma*********@wowway.com> wrote in message
news:9a********************************@4ax.com...

William Bradley wrote:

I have two cells on a form. One of them is the "Production Date" and the
other is the "Expiry Date". The "Expiry Date" is 183 days after the
"Production Date."

On an Excel spreadsheet, the "Expiry Date" is automatically entered, when
the "Production Date" is entered. To do this the "Expiry Date" cell carriesthe following formula: "=A15+183".

I would like to be able to do the same on an Access Form, with the resultswritten to the underlying table.

I think what you're asking for can be done by using a line
of code in the Production Date text box's AfterUpdate event
procedure:

txtExpiryDate = DateAdd("d", 183, txtProductionDate)

Thank you for the above Marsh. I already have a line of code in the "After
Update" of the "ProductionDate" field. Is it possible to add yours, abvove,
as well? If so how? I tried it and it didn't work, probably due to my
missing something out.

Thank you,

Bill.

[Marshall Barton]

William Bradley wrote:

"Marshall Barton" <ma*********@wowway.com> wrote in message
news:9a********************************@4ax.com.. .

William Bradley wrote:

>I have two cells on a form. One of them is the "Production Date" and the
>other is the "Expiry Date". The "Expiry Date" is 183 days after the
>"Production Date."
>
>On an Excel spreadsheet, the "Expiry Date" is automatically entered, when
>the "Production Date" is entered. To do this the "Expiry Date" cellcarries >the following formula: "=A15+183".
>
>I would like to be able to do the same on an Access Form, with theresults >written to the underlying table.

I think what you're asking for can be done by using a line
of code in the Production Date text box's AfterUpdate event
procedure:

txtExpiryDate = DateAdd("d", 183, txtProductionDate)

Thank you for the above Marsh. I already have a line of code in the "After
Update" of the "ProductionDate" field. Is it possible to add yours, abvove,
as well? If so how? I tried it and it didn't work, probably due to my
missing something out.

Sure you can add a line of code to your existing procedure.

That line is simple enough that I can't see how you could be
leaving something out. Maybe you didn't change the names I
used to the ones you're using. Make sure that you replace
txtExpiryDate with the name of the text box bound to the
Expiry Date field. Similarly change txtProductionDate to
the name of the text box bound to the Production Date field.

--
Marsh