Date Calculation Parameter

[Guest]

I have tried, unsuccessfully, to revise the code below so that when I enter a
date in the StartDate field, an input box will come up and ask me how many
days I want to add or subtract, then it will populate the FutureDate field.
I have tried putting this in the AfterUpdate for the StartDate field.
Anyway, obviously I am doing something wrong. I think it is the problem is
with the code: Selection.InsertBefore Format((Date1 + myValue), Mask). Can
someone help me? My aspiration is to then have another input box come up and
ask me how many months to add or subtract from the date and then a third
input box come up and ask me how many years to I want to add or subtract.


Private Sub StartDate_AfterUpdate()

Dim Message As String
Dim Mask As String
Dim Title As String
Dim Default As String
Dim Date1 As String
Dim MyText As String
Dim Var1 As String
Dim Var2 As String
Dim Var3 As String
Dim Var4 As String
Dim Var5 As String
Dim Var6 As String
Dim Var7 As String
Dim Var8 As String
Dim Date2 As String
Dim Date3 As String
Dim myValue As Variant
Dim StartDate As String

StartDate = Me.ActiveControl
Date = Me.StartDate




''Date mask below includes non-breaking spaces (Chr(160))
Mask = "mm/" & Chr(160) & "dd/" & Chr(160) & "yy" ' Set Date format
Default = "14" ' Set default.
Title = "Plus or minus date starting with " & Format(Date, Mask)
Date1 = Format(Date, Mask)
Var1 = "Enter number of days by which to vary above date. " _
& "The number entered will be added to "
Var2 = Format(Date + Default, Mask) ' Today plus default (14)
Var3 = Format(Date - Default, Mask) ' Today minus default (14)
Var4 = ". The default ("
Var5 = ") will produce the date "
Var6 = ". Minus (-"
Var7 = ". Entering '0' (zero) will insert "
Var8 = " (today). Click cancel to quit."
MyText = Var1 & Date1 & Var4 & Default & Var5 & Var2 & Var6 _
& Default & Var5 & Var3 & Var7 & Date1 & Var8
'
' Display InputBox and get number of days
GetInput:
myValue = InputBox(MyText, Title, Default)
'
If myValue = "" Then
End 'quit subroutine
End If
'
On Error GoTo Oops 'just in case
Selection.InsertBefore Format((Date1 + myValue), Mask)
Selection.Collapse (wdCollapseEnd)
End 'End subroutine
'
Oops: ' error handler in case user types something other than a number
'
MsgBox prompt:="Sorry, only a number will work, please try again.", _
Buttons:=vbExclamation, _
Title:="A number is needed here."
GoTo GetInput
End Sub


Thanks in advance.

 

[Douglas J. Steele]

This newsgroup is for questions about Access, the database product that's
part of Office Professional. Your code would appear to be related to Word.

You might be better off asking in a newsgroup related to Word.

 

[Guest]

Obviously that is my problem, because I am trying to use this code in Access!

 

[Jeff Boyce]

Sharon

It appears your code is trying to collect all of the user input at once.

Have you tried collecting each piece separately, using the InputBox approach
for each piece?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Well . . .Doug is correct is saying that this is Word code, I copied this
code from Graham Mayor. When this code is executed in Word, it adds or
subtracts 14 (or whatever you input) days to the date today. So, if am I
understanding what you are asking, it is supposed to be changing a portion of
the date and not the whole date at this point. Is that what you are
suggesting? I tried to modify the code to work in access so that when I put
in a date into StartDate Field, it would add or subtract days and enter it
into the Future Date field.

Or are you saying (I am brainstorming as I type, unfortunately my fingers
are faster than my brain!!! ) that I should just increment each of the
portions separately, 01 (January) + "however many months"; 31 + "however many
days"; 06 (2006) + "however many years"? Rather than using a date format?

 

[Jeff Boyce]

Sharon

I'm not clear on why you are asking for the increments. If you want a date
in the future, could you just ask for that date (e.g., a day 45 days from
now)?

If you want the ability to differentially increment days and months and
years (2 days, and 3 months and 4 years), then get each portion separately,
and use something like the DateSerial() function to generate your
FutureDate. It might look something like:

FutureDate = DateSerial(Year([YourDate]+[YourInput#OfYears],
Month([YourDate])+ ...)

Regards

Jeff Boyce
Microsoft Office/Access MVP