expiration date

[David]

Hello Everyone,

My question is, I have a table with many different products some of them
have an expiration date of 2 years and some with only 1 year (I have not
added this yet to the table). I have my report draw from a query that asks
for a certain product and shows all the information of that product. Is
there a way to put a formula into a record that will show the expiration
date of a product when the form is viewed?

Thank you in advance,
David

 

[John Vinson]

Hello Everyone,

My question is, I have a table with many different products some of them
have an expiration date of 2 years and some with only 1 year (I have not
added this yet to the table). I have my report draw from a query that asks
for a certain product and shows all the information of that product. Is
there a way to put a formula into a record that will show the expiration
date of a product when the form is viewed?

Only if Access has some way of knowing how many years to add, but
sure. Let's say your Products table has a ProductLife number field
with 1 or 2 respectively; and the Products table or perhaps more
appropriately the Lot or Batch table has a date/time ProductionDate
field.

Just put a textbox on your form with the label Expiration Date and a
Control Source

=DateAdd("yyyy", [ProductLife], [ProductionDate])

John W. Vinson[MVP]

 

[David]

Hello Everyone,

My question is, I have a table with many different products some of them
have an expiration date of 2 years and some with only 1 year (I have not
added this yet to the table). I have my report draw from a query that asks
for a certain product and shows all the information of that product. Is
there a way to put a formula into a record that will show the expiration
date of a product when the form is viewed?

Only if Access has some way of knowing how many years to add, but
sure. Let's say your Products table has a ProductLife number field
with 1 or 2 respectively; and the Products table or perhaps more
appropriately the Lot or Batch table has a date/time ProductionDate
field.

Just put a textbox on your form with the label Expiration Date and a
Control Source

=DateAdd("yyyy", [ProductLife], [ProductionDate])

John W. Vinson[MVP]

Thank you so much John that worked Great. Although the Production Date
field that I have todays date show in displays todays date but does not
change with tomorrows date when I open the table. I got my form to work
like you said but I want the user when printing out the form to see when
the product expires.

Also I am going to mess around with it a little but, how could I change
the expiration to months instead of years?



Thanks again John,
David

 

[David]

Thank you so much John that worked Great. Although the Production Date
field that I have todays date show in displays todays date but does not
change with tomorrows date when I open the table. I got my form to work
like you said but I want the user when printing out the form to see when
the product expires.

Also I am going to mess around with it a little but, how could I change
the expiration to months instead of years?



Thanks again John,
David

Clarification

I want the user when printing out the form to see when
the product expires. It expires the day the form is printed out.

 

[David]

Thanks agian John I figured out both off my question by inserting this
into the text box on the form =DateAdd("d",[Product Life],Now()) I
decided the expiration to be in days instead.

Thanks,
David

 

[John Vinson]

Thanks agian John I figured out both off my question by inserting this
into the text box on the form =DateAdd("d",[Product Life],Now()) I
decided the expiration to be in days instead.

I'd use Date() - today's date - instead of Now(), which is the current
date and time accurate to a few microseconds (unless you want the
expiration date to be 3:13:22pm [Product Life] days from now). Note
also that "printing out a Form" is rather a bad idea; forms are
optimized for onscreen viewing not for printing (unless you're using a
Webpage rather than an Access form). Also setting the control source
of a textbox as you describe will generate a different expiration date
every time you run it, and not record that information anywhere (I'm
not sure that isn't what you want, but just to be sure...!)

John W. Vinson[MVP]