Using DateAdd to calc a contract's end date.

[Guest]

Hello,
I tried your suggestion by adding a field called term_type.
1 = Monthly, 2 = Weekly, and then on my form, I tried to bind it using the
"bind boxing" of the 2 radio buttons. Is that all I need to do to bind them?
I set the default value to =1 which is most common (month).
I also have the input field called "term."

Then, I added in the ControlSource for EndDate:

=DateAdd( Choose( [TermType], "m", "ww" ), [Term], [Acceptance_Date] )

Is gives me the #NAME? error. I think I'm missing a couple of steps still.
Do I need to put it in an Event Procedure.

Also, should I be storing the contract's end date in a main contract table
or calculating it on the form. I do refer to this date for reporting and in
my subform.

Thank you for all of your help!!!

gg
P.S. Initially I was calculating the enddate using this formula and it
worked but not for user entered weeks
Enddate=IIf(DateSerial(Year(DateAdd("m",nz([term_mos],0),nz([acceptance_date],0))),Month(DateAdd("m",nz([term_mos],0),nz([acceptance_date],0))),Day(DateAdd("m",nz([term_mos],0),nz([acceptance_date],0)))-1)

 

[Guest]

Try two things:
1. check if the names are correct
2. Try this, incase there are no values in the fields
=IIF(Not isnull([TermType]) and not Isnull([Acceptance_Date]),DateAdd(
Choose( [TermType], "m", "ww" ), nz([Term],0), [Acceptance_Date] )

 

[Guest]

Hi Ofer,

Thanks for your quick suggestions. I checked my syntax, and it is ok. Am I
missing any steps or putting it in the incorrect spot?

For the group control radio buttons, I put the following under the Event
procedure go here....(see below)

Private Sub optChoose_AfterUpdate()
' Populate rowsource of cboSelect

Dim strSQL As String

On Error GoTo HandleErr

Select Case optChoose
Case 1
' term in weeks
strSQL = "Select term in weeks "
Case 2
' term in months
strSQL = "Select term in months "
Case 3
' term in years
strSQL = "Select term in years "
Case Else
End Select

With Me!cboSelect
.Value = Null
.RowSource = strSQL
.Requery
.Value = .ItemData(0)
End With

ExitHere:
Exit Sub

HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in Form_FindForm.optChoose_AfterUpdate"
End Select
Resume ExitHere
Resume
End Sub

I added your suggestion in the Control Source area under the "All" tab for
the EndDate field (this is the calculation not a user input field).
=IIF(Not isnull([TermType]) and not Isnull([Acceptance_Date]),DateAdd(
Choose( [TermType], "m", "ww" ), nz([Term],0), [Acceptance_Date] )

Do I need to calculate or do anything for the term field (user input field)?

Hello,
I tried your suggestion by adding a field called term_type.
1 = Monthly, 2 = Weekly, and then on my form, I tried to bind it using the
"bind boxing" of the 2 radio buttons. Is that all I need to do to bind them?
I set the default value to =1 which is most common (month).
I also have the input field called "term."

Then, I added in the ControlSource for EndDate:

=DateAdd( Choose( [TermType], "m", "ww" ), [Term], [Acceptance_Date] )

Is gives me the #NAME? error. I think I'm missing a couple of steps still.
Do I need to put it in an Event Procedure.

Also, should I be storing the contract's end date in a main contract table
or calculating it on the form. I do refer to this date for reporting and in
my subform.

Thank you for all of your help!!!

gg
P.S. Initially I was calculating the enddate using this formula and it
worked but not for user entered weeks:
Enddate=IIf(DateSerial(Year(DateAdd("m",nz([term_mos],0),nz([acceptance_date],0))),Month(DateAdd("m",nz([term_mos],0),nz([acceptance_date],0))),Day(DateAdd("m",nz([term_mos],0),nz([acceptance_date],0)))-1)

Thank you, Ofer!

gg