formula question

[David]

Hello All,

I have a question. Is it possible to have a text box on a report take
data from a field and add it to the Now() function? I have a field that
stores either 273,365, or 730 days that the product will expire and want
it to calculate the expiration date when a report is printed out. FYI
the report will be printed out the day the product is made.


TIA,
David

 

[Rick Brandt]

Hello All,

I have a question. Is it possible to have a text box on a report take
data from a field and add it to the Now() function? I have a field
that stores either 273,365, or 730 days that the product will expire
and want it to calculate the expiration date when a report is printed
out. FYI the report will be printed out the day the product is made.


TIA,
David

Check help on the DateAdd() function. It does exactly what you want.

Actually just Now() + [Field] will do what you want.

 

[James Hahn]

Take a look at the dateadd function - it should do what you want (and IIRC
the example provided is very close to what you are trying to do).

 

[David]

Thank you Rick and James

 

[David]

I tried this =DateAdd("d", [Expiration] ,Now()) but I get an Expiration
pop up. The value I want added to todays date is in a table in the
Expiration column.

Any suggestions,
David

 

[James Hahn]

What exactly does the popup say?

Is [Expiration] numeric? Is there a current record from which [Expiration]
can be retrieved?

 

[David]

When I preview the report a popup comes up I'm assumimg it is asking for
an expiration. I have a table that has different numbers for each record
in a field called Expiration. When I put the function that I stated in
my last reply instead of adding whatever value is in that field for a
record to todays date I get a popup that says expiration and when I
click ok or cancl it shows up #error on the report.

Thanks,
David

 

[Dirk Goldgar]

I tried this =DateAdd("d", [Expiration] ,Now()) but I get an
Expiration pop up. The value I want added to todays date is in a
table in the Expiration column.

The table containing the field [Expiration] isn't part of the
recordsource of the form or report where you are using this expression,
you have to explicitly look it up using, for example, the DLookup
function. However, a better approach is probably to add that table to
the recordsource query, appropriately joined to one of the tables
already participating in the query. Then you can perform your DateAdd
calculation either in a calculated field in the query, or in the
controlsource expression of a control on the form. In the latter case,
you may need to add a control bound to [Expiration]; I'm not sure.