Using Functions to calculate dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone help?

I want to be able to create a field witin Accesss that will enter a date
based on a previous date plus a number of days (in this case 28 days). I
also need the field to automatically update if another field contains a date
which will then prompt a further calculation. To put it simply:

The database will contain details of clients which will have a response date
of 28 calendar days after the initial contact date. However the response can
be altered if the client needs to provide additional information, whereupon
the response timescale is halted and only restarted when the client provides
the addiiotnal data.

I can do all this in Excel using a combination of SUM and IF functions, but
because I am total novice with VB, I don't how to do this in Access. Is what
I am trying to do possible?
 
I want to be able to create a field witin Accesss that will enter a date
based on a previous date plus a number of days (in this case 28 days). I
also need the field to automatically update if another field contains a
date which will then prompt a further calculation.
Click to expand...

There are two ways you can accomplish this:-

At data entry time, you can have an AfterUpdate event that detects entries
in the txtAdditionalInformationSuppliedDate control and writes a new value
to the FinalResponseDueByDate field. This is not so good, because if
someone alters one of the fields without using your form (eg using Excel,
vba, table datasheets, etc) then the final response will get out of synch
v quickly.

At query time, you should be able to calculate the correct date, along the
lines of

SELECT 28 + IIF(AddnlInfoDt IS NULL, InitReturnDt, AddnlInfoDt)
AS FinalResponseDt
FROM etc

It's slightly more complicated in SQL Server because the functions are a
bit different, but the same sort of thing applies. This is the better
method, though, because it is guaranteed to be always correct. It's also a
bit faster, because adding 28 is faster than fetching an extra value from
the hdd.

Hope that helps


Tim F
 
Back
Top