Problem w/ SWITCH function

G

Guest

I have a form with several date fields [EffectiveDate], [InitialTermExp],
[CurrentTermExp], and a combo box [cboRenewalConditions] that designates the
terms under which the agreement [AgName] referred to in the record renews. I
am trying to set up a complex AfterUpdate event so that when the user makes
certain choices under the combo box, the date [CurrentTermExp] automatically
fills in.

I tried to write a complex IIF function with a SWITCH function as the True
part, but Visual Basic is saying I have a "Compile Error" and need an
"Expression:=." Can someone tell me what's wrong with my code?

cboRenewalConditions_AfterUpdate
Iff(Not IsNull([EffectiveDate]) And Me![Expired/Terminated]=0,
SWITCH(Me![cboRenewalConditions]=1 And
DateSerial(Year(Now()),Month([EffectiveDate]),Day([EffectiveDate])<=Now()),
Me![CurrentTermExp] =
DateSerial(Year(Now())+1,Month([EffectiveDate]),Day([EffectiveDate]),
Me![cboRenewalConditions]=1 And
DateSerial(Year(Now()),Month([EffectiveDate]),Day([EffectiveDate])>Now(),
Me![CurrentTermExp]=DateSerial(Year(Now()),Month([EffectiveDate]),Day([EffectiveDate]),
Me![cboRenewalConditions]=2 And Not IsNull([InitialTermExp]),
Me![CurrentTermExp] = Me![InitialTermExp]), Me![CurrentTermExp]=Null))))
End If
 
S

Steve Schapel

Laura,

Well, there are a few problems here.

One is that it's IIf not Iff.

Then, there the IIf() function, and the If...Then...End If statement.
They are differnt from each other, and are used in differnt ways, but
you seem to have muddled up elements from both

Then, with the Switch function, you really have a syntax problem there.
The second argument of the Switch function defined the value returned
by the function if the first argument evaluates to True. So you are
saying you want the function to return this value:
"Me![CurrentTermExp] =
DateSerial(Year(Now())+1,Month([EffectiveDate]),Day([EffectiveDate])"
Well, apart from the fact that it is missing a ")" at the end, this is a
misunderstanding. This will not assign the date value to the
CurrentTermExp field, that's not what the Switch function does, it just
returns a value, if you see what I mean. Really, if you were trying to
create a calculated field in a query, or in the Control Source of an
unbound control on a form, or some such, then the IIf and Switch
functions might be a suitable approach.

So, I think I understand what you are trying to do, and this is probably
something like the approach I would take (caution: untested "air code"!)...

Private Sub cboRenewalConditions_AfterUpdate
Dim EvalDate As Date
EvalDate =
DateSerial(Year(Date),Month(Me.EffectiveDate),Day(Me.EffectiveDate))
If Not IsNull(Me.EffectiveDate) And Me.Expired/Terminated = 0 Then
Select Case Me.cboRenewalConditions
Case 1
If EvalDate > Date Then
Me.CurrentTermExp = EvalDate
Else
Me.CurrentTermExp = DateAdd("yyyy",1,EvalDate)
End If
Case 2
Me.CurrentTermExp = Me.InitialTermExp
End Select
Else
Me.CurrentTermExp=Null
End If
End Sub

--
Steve Schapel, Microsoft Access MVP


Laura_Christian said:
I have a form with several date fields [EffectiveDate], [InitialTermExp],
[CurrentTermExp], and a combo box [cboRenewalConditions] that designates the
terms under which the agreement [AgName] referred to in the record renews. I
am trying to set up a complex AfterUpdate event so that when the user makes
certain choices under the combo box, the date [CurrentTermExp] automatically
fills in.

I tried to write a complex IIF function with a SWITCH function as the True
part, but Visual Basic is saying I have a "Compile Error" and need an
"Expression:=." Can someone tell me what's wrong with my code?

cboRenewalConditions_AfterUpdate
Iff(Not IsNull([EffectiveDate]) And Me![Expired/Terminated]=0,
SWITCH(Me![cboRenewalConditions]=1 And
DateSerial(Year(Now()),Month([EffectiveDate]),Day([EffectiveDate])<=Now()),
Me![CurrentTermExp] =
DateSerial(Year(Now())+1,Month([EffectiveDate]),Day([EffectiveDate]),
Me![cboRenewalConditions]=1 And
DateSerial(Year(Now()),Month([EffectiveDate]),Day([EffectiveDate])>Now(),
Me![CurrentTermExp]=DateSerial(Year(Now()),Month([EffectiveDate]),Day([EffectiveDate]),
Me![cboRenewalConditions]=2 And Not IsNull([InitialTermExp]),
Me![CurrentTermExp] = Me![InitialTermExp]), Me![CurrentTermExp]=Null))))
End If
Click to expand...
 
G

Guest

Thanks so much! Works like a dream.

Steve Schapel said:
Laura,

Well, there are a few problems here.

One is that it's IIf not Iff.

Then, there the IIf() function, and the If...Then...End If statement.
They are differnt from each other, and are used in differnt ways, but
you seem to have muddled up elements from both

Then, with the Switch function, you really have a syntax problem there.
The second argument of the Switch function defined the value returned
by the function if the first argument evaluates to True. So you are
saying you want the function to return this value:
"Me![CurrentTermExp] =
DateSerial(Year(Now())+1,Month([EffectiveDate]),Day([EffectiveDate])"
Well, apart from the fact that it is missing a ")" at the end, this is a
misunderstanding. This will not assign the date value to the
CurrentTermExp field, that's not what the Switch function does, it just
returns a value, if you see what I mean. Really, if you were trying to
create a calculated field in a query, or in the Control Source of an
unbound control on a form, or some such, then the IIf and Switch
functions might be a suitable approach.

So, I think I understand what you are trying to do, and this is probably
something like the approach I would take (caution: untested "air code"!)...

Private Sub cboRenewalConditions_AfterUpdate
Dim EvalDate As Date
EvalDate =
DateSerial(Year(Date),Month(Me.EffectiveDate),Day(Me.EffectiveDate))
If Not IsNull(Me.EffectiveDate) And Me.Expired/Terminated = 0 Then
Select Case Me.cboRenewalConditions
Case 1
If EvalDate > Date Then
Me.CurrentTermExp = EvalDate
Else
Me.CurrentTermExp = DateAdd("yyyy",1,EvalDate)
End If
Case 2
Me.CurrentTermExp = Me.InitialTermExp
End Select
Else
Me.CurrentTermExp=Null
End If
End Sub

--
Steve Schapel, Microsoft Access MVP


Laura_Christian said:
I have a form with several date fields [EffectiveDate], [InitialTermExp],
[CurrentTermExp], and a combo box [cboRenewalConditions] that designates the
terms under which the agreement [AgName] referred to in the record renews. I
am trying to set up a complex AfterUpdate event so that when the user makes
certain choices under the combo box, the date [CurrentTermExp] automatically
fills in.

I tried to write a complex IIF function with a SWITCH function as the True
part, but Visual Basic is saying I have a "Compile Error" and need an
"Expression:=." Can someone tell me what's wrong with my code?

cboRenewalConditions_AfterUpdate
Iff(Not IsNull([EffectiveDate]) And Me![Expired/Terminated]=0,
SWITCH(Me![cboRenewalConditions]=1 And
DateSerial(Year(Now()),Month([EffectiveDate]),Day([EffectiveDate])<=Now()),
Me![CurrentTermExp] =
DateSerial(Year(Now())+1,Month([EffectiveDate]),Day([EffectiveDate]),
Me![cboRenewalConditions]=1 And
DateSerial(Year(Now()),Month([EffectiveDate]),Day([EffectiveDate])>Now(),
Me![CurrentTermExp]=DateSerial(Year(Now()),Month([EffectiveDate]),Day([EffectiveDate]),
Me![cboRenewalConditions]=2 And Not IsNull([InitialTermExp]),
Me![CurrentTermExp] = Me![InitialTermExp]), Me![CurrentTermExp]=Null))))
End If
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Invalid Use of Null - After Update 2
DateSerial function generates Run-time error 13: Type mismatch 4
Calc Date Years Month Days from DOB to DOD 2
DateDiff issue 7
Select case problem - else if 2
Date function for "Last Quarter" 2
date function 3
how to highlight the (date) object in form when birthday (last 14 days before birthday) 1

Top