Problem with DateAdd function

[Guest]

I am using the DateAdd expression in a Form to calculate a future date.

Date of next calibration =DateAdd("m",[Intervals],[Date Of Last Calibration])

The calculation works OK and the value shows up in the form correctly,
however, it does not update the ‘Date of next calibration’ field in the
underlying table.
Please can anyone give me some advice on how to update the field in the
underlying table?
Sorry if its an easy question but I’m new to Access.

 

[Steve Schapel]

Sean,

The first point is that you probably shouldn't have a "‘Date of next
calibration’ field in the underlying table" in the first place. If this
value can be calculated, as it seems, by reference to the value of the
Intervals field, then the general wisdom if database design principles
says that it should be so calculated whenever you need it, rather than
redundantly storing the calculated value.

However, if you have a valid reason for storing this data, you would
need to use code to write it to the field. Possible on the After Update
event of the Date Of Last Calibration control on your form, something
like this...
Me.Date_of_next_calibration =
DateAdd("m",Me.Intervals,Me.Date_Of_Last_Calibration)

 

[Guest]

Steve,
Thanks for replying so quickly.
I have tried your code but the "Date of next calibration" in the underlying
table still does not update.

I need to use the calculated value when I print a Report of items out of
calibration. That is items with a "Date of next calibration" less than Now
(today's date).

Sean Meyrick

Sean,

The first point is that you probably shouldn't have a "‘Date of next
calibration’ field in the underlying table" in the first place. If this
value can be calculated, as it seems, by reference to the value of the
Intervals field, then the general wisdom if database design principles
says that it should be so calculated whenever you need it, rather than
redundantly storing the calculated value.

However, if you have a valid reason for storing this data, you would
need to use code to write it to the field. Possible on the After Update
event of the Date Of Last Calibration control on your form, something
like this...
Me.Date_of_next_calibration =
DateAdd("m",Me.Intervals,Me.Date_Of_Last_Calibration)

--
Steve Schapel, Microsoft Access MVP

I am using the DateAdd expression in a Form to calculate a future date.

Date of next calibration =DateAdd("m",[Intervals],[Date Of Last Calibration])

The calculation works OK and the value shows up in the form correctly,
however, it does not update the ‘Date of next calibration’ field in the
underlying table.
Please can anyone give me some advice on how to update the field in the
underlying table?
Sorry if its an easy question but I’m new to Access.

 

[Luke Dalessandro]

Sean, you can just base the report on a query with the calculated "date
of next calibration", rather than basing it on the table directly

Something like:

SELECT * FROM

WHERE DateAdd("d",

.[Intervals],

.[DateOfLastCalibration]) < DateValue(Now())

Will do the trick... You can then set that query as the report's data
source or use it to build a report using the wizard.

Steve's code really should work fine though...

Luke

Steve,
Thanks for replying so quickly.
I have tried your code but the "Date of next calibration" in the underlying
table still does not update.

I need to use the calculated value when I print a Report of items out of
calibration. That is items with a "Date of next calibration" less than Now
(today's date).

Sean Meyrick




:

Sean,

The first point is that you probably shouldn't have a "‘Date of next
calibration’ field in the underlying table" in the first place. If this
value can be calculated, as it seems, by reference to the value of the
Intervals field, then the general wisdom if database design principles
says that it should be so calculated whenever you need it, rather than
redundantly storing the calculated value.

However, if you have a valid reason for storing this data, you would
need to use code to write it to the field. Possible on the After Update
event of the Date Of Last Calibration control on your form, something
like this...
Me.Date_of_next_calibration =
DateAdd("m",Me.Intervals,Me.Date_Of_Last_Calibration)

--
Steve Schapel, Microsoft Access MVP

I am using the DateAdd expression in a Form to calculate a future date.

Date of next calibration =DateAdd("m",[Intervals],[Date Of Last Calibration])

The calculation works OK and the value shows up in the form correctly,
however, it does not update the ‘Date of next calibration’ field in the
underlying table.
Please can anyone give me some advice on how to update the field in the
underlying table?
Sorry if its an easy question but I’m new to Access.

 

[Guest]

Steve,
Thanks it now works!!
I have now put the Event in the correct place on the Form.
Many thanks
Sean

Sean,

The first point is that you probably shouldn't have a "‘Date of next
calibration’ field in the underlying table" in the first place. If this
value can be calculated, as it seems, by reference to the value of the
Intervals field, then the general wisdom if database design principles
says that it should be so calculated whenever you need it, rather than
redundantly storing the calculated value.

However, if you have a valid reason for storing this data, you would
need to use code to write it to the field. Possible on the After Update
event of the Date Of Last Calibration control on your form, something
like this...
Me.Date_of_next_calibration =
DateAdd("m",Me.Intervals,Me.Date_Of_Last_Calibration)

--
Steve Schapel, Microsoft Access MVP

I am using the DateAdd expression in a Form to calculate a future date.

Date of next calibration =DateAdd("m",[Intervals],[Date Of Last Calibration])

The calculation works OK and the value shows up in the form correctly,
however, it does not update the ‘Date of next calibration’ field in the
underlying table.
Please can anyone give me some advice on how to update the field in the
underlying table?
Sorry if its an easy question but I’m new to Access.