Adding Days to an entered date

C

Carol

I'm creating a database of tasks that have due dates that are not fixed (not
always the same date). Each task has the following:
Last completed date
Frequency: description and numeric field. For example:
Annual - 366
Quarterly - 90
Next Due date: = Last completed date + Frequency #

I have this in an Excel spreadsheet currently. Excel converts the last
completed date to a number, adds the frequency #, then converts it back to a
date format.

How do I do this in Access??
Thanks,
Carol
 
A

Alex White MCDBA MCSE

use the datadd function because the way you are calculating (in days) may
not give you the results you want look into

dateadd incrementing 3 month not 90 days
dateadd incrementing 1 year not 366 days

*** Air code ****
3 months
Next_Due_Date = dateadd("m",3,Last_Completed_Date)

1 year
Next_Due_Date = dateadd("yyyy",1,Last_Completed_Date)
 
D

David Lloyd

Carol:

One option would be to use the DateAdd function. You can reference it in
VBA help.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I'm creating a database of tasks that have due dates that are not fixed (not
always the same date). Each task has the following:
Last completed date
Frequency: description and numeric field. For example:
Annual - 366
Quarterly - 90
Next Due date: = Last completed date + Frequency #

I have this in an Excel spreadsheet currently. Excel converts the last
completed date to a number, adds the frequency #, then converts it back to a
date format.

How do I do this in Access??
Thanks,
Carol
 
C

Carol

Alex,
Thanks, I'll change the frequency # format. However, I have one followup
question. Each task in the database can have a different frequency and is
entered in the database. In writing the dateadd, can the number (3 for
3months), reference the field that contains the frequency?
Thanks,
Carol
 
A

Alex White MCDBA MCSE

Yes,

if I understand you, you want to set the frequency in code e.g.
day/month/year?


e.g.

Dim strFreq As String
strFreq = "d" ' of course this could be either a string field or a
numeric e.g. 1 = d, 2 = m, 3 = yyyy
MsgBox DateAdd(strFreq, 20, Now())

so your table could have 2 fields in it,

Freq_Type as string
Frequency as numeric


dim strFreq as string
Select Case .fields("Freq_Type").value
case 1
strFreq = "d"
case 2
strFreq = "m"
case 3
strFreq = "yyyy"
case else
strFreq = "d"
end select
MsgBox DateAdd(strFreq,.fields(.fields("Frequency").value, Now())

I have not checked the above I hope it answers your question
 
C

Carol

Alex,
I don't think I explained it correctly.
I have the following fields in the main table
Task
Freq (Available choices are Annual, monthly, quarterly, etc)
Last completed date

Each task in the database can have a different frequency.

Is there a way to reference the Freq field in the dateadd calculation? For
example:
Task #1 Freq - Annual ("yyyy",1) Last complete date - 5/15/05
Next_Due_Date=Dateadd(Freq,Last_Completed_date)

rather than
=Dateadd("yyyy",1,Last_completed_date)
 
Top