If Statement in locked field

[Guest]

I am trying to write an If statement in my form that calculates the total
contract value of a sale. The text box is locked so people can't change
anything, but I want it to show them how much they sold to a customer. Here
is the formula that I'm trying to use:

=IF([Plan]="Smart Trunk",[NNI]*[Term Length],IF([Plan]="Super
Trunk",[NNI]*[Term Length],IF([Plan]="IAS",[NNI]*[Term
Length],IF([Completelink]=0,((([Trunks/Lines]*[RU])+([Trunks/Lines2]*[RU2])+([Trunks/Lines3]*[RU3])+([Trunks/Lines4]*[RU4]))*(1-[Discount
Rate]))*[Term Length],[Completelink]*([Term Length]/12)))))

I don't know if this makes sense, but can anyone let me know why this isn't
working?

 

[Guest]

Hi, Matt.

I can’t see any typos, although verify that [Plan] is truly a text field
rather than merely displaying text yet storing an underlying code.

I think, though, that complex nested IF function calls are better replaced
by a custom function stored in the form module, being much easier to read and
debug. You can also combine the first three into a single boolean value with
the OR conjunction:

Function MyResult() As Single

If ([Plan] = “Smart Trunk†OR [Plan] = “Super Trunk†OR [Plan] = “IASâ€)
Then
MyResult = [NNI]*[Term Length]
Else
If [Completelink] = 0 Then
MyResult = [Trunks/Lines] * [RU] + _
[Trunks/Lines2] * [RU2] + _
[Trunks/Lines3] * [RU3] + _
[Trunks/Lines4] * [RU4]
MyResult = MyResult * (1-[DiscountRate]) * [Term Length]
Else
MyResult = [Completelink] * [Term Length] / 12
End If
End If

End Function

Then set the ControlSource to: = MyResult()

Hope that helps.
Sprinks

 

[Guest]

Thanks for your response Sprinks. Would I enter this function in the
Expression Builder? This looks like a Visual Basic function. I could be
wrong, I haven't used it in a while. If this is a Visual Basic function,
would I need to write this in another place in my database and then reference
it in my control source?

Hi, Matt.

I can’t see any typos, although verify that [Plan] is truly a text field
rather than merely displaying text yet storing an underlying code.

I think, though, that complex nested IF function calls are better replaced
by a custom function stored in the form module, being much easier to read and
debug. You can also combine the first three into a single boolean value with
the OR conjunction:

Function MyResult() As Single

If ([Plan] = “Smart Trunk†OR [Plan] = “Super Trunk†OR [Plan] = “IASâ€)
Then
MyResult = [NNI]*[Term Length]
Else
If [Completelink] = 0 Then
MyResult = [Trunks/Lines] * [RU] + _
[Trunks/Lines2] * [RU2] + _
[Trunks/Lines3] * [RU3] + _
[Trunks/Lines4] * [RU4]
MyResult = MyResult * (1-[DiscountRate]) * [Term Length]
Else
MyResult = [Completelink] * [Term Length] / 12
End If
End If

End Function

Then set the ControlSource to: = MyResult()

Hope that helps.
Sprinks

I am trying to write an If statement in my form that calculates the total
contract value of a sale. The text box is locked so people can't change
anything, but I want it to show them how much they sold to a customer. Here
is the formula that I'm trying to use:

=IF([Plan]="Smart Trunk",[NNI]*[Term Length],IF([Plan]="Super
Trunk",[NNI]*[Term Length],IF([Plan]="IAS",[NNI]*[Term
Length],IF([Completelink]=0,((([Trunks/Lines]*[RU])+([Trunks/Lines2]*[RU2])+([Trunks/Lines3]*[RU3])+([Trunks/Lines4]*[RU4]))*(1-[Discount
Rate]))*[Term Length],[Completelink]*([Term Length]/12)))))

I don't know if this makes sense, but can anyone let me know why this isn't
working?

 

[Guest]

Hi, Matt.

The function definition should be entered in the form's Code Module. There
are a number of ways to enter the module, probably the easiest is, from Form
Design View, pressing <Ctrl-G>, which opens the module and places you in the
"Immediate Window"--used for testing the values of expressions as a debugging
tool. Move your cursor to the window above the Immediate Window, and enter
the function definition there.

Any function defined in a form's module has scope limited to that form,
which is fine here. If you dream up more general functions that you'd like
to access from anywhere, they should be defined in a general module (click
the Module tab, define a new module), and should be given universal scope
with the keyword Public:

Public Function MyGeneralFunction() As ...
.....
End Function

As to your form control, once the function is defined in its form module,
you get its value by assigning the ControlSource to the value of the function:

=MyResult()

Hope that helps.
Sprinks

Thanks for your response Sprinks. Would I enter this function in the
Expression Builder? This looks like a Visual Basic function. I could be
wrong, I haven't used it in a while. If this is a Visual Basic function,
would I need to write this in another place in my database and then reference
it in my control source?

Hi, Matt.

I can’t see any typos, although verify that [Plan] is truly a text field
rather than merely displaying text yet storing an underlying code.

I think, though, that complex nested IF function calls are better replaced
by a custom function stored in the form module, being much easier to read and
debug. You can also combine the first three into a single boolean value with
the OR conjunction:

Function MyResult() As Single

If ([Plan] = “Smart Trunk†OR [Plan] = “Super Trunk†OR [Plan] = “IASâ€)
Then
MyResult = [NNI]*[Term Length]
Else
If [Completelink] = 0 Then
MyResult = [Trunks/Lines] * [RU] + _
[Trunks/Lines2] * [RU2] + _
[Trunks/Lines3] * [RU3] + _
[Trunks/Lines4] * [RU4]
MyResult = MyResult * (1-[DiscountRate]) * [Term Length]
Else
MyResult = [Completelink] * [Term Length] / 12
End If
End If

End Function

Then set the ControlSource to: = MyResult()

Hope that helps.
Sprinks

I am trying to write an If statement in my form that calculates the total
contract value of a sale. The text box is locked so people can't change
anything, but I want it to show them how much they sold to a customer. Here
is the formula that I'm trying to use:

=IF([Plan]="Smart Trunk",[NNI]*[Term Length],IF([Plan]="Super
Trunk",[NNI]*[Term Length],IF([Plan]="IAS",[NNI]*[Term
Length],IF([Completelink]=0,((([Trunks/Lines]*[RU])+([Trunks/Lines2]*[RU2])+([Trunks/Lines3]*[RU3])+([Trunks/Lines4]*[RU4]))*(1-[Discount
Rate]))*[Term Length],[Completelink]*([Term Length]/12)))))

I don't know if this makes sense, but can anyone let me know why this isn't
working?

 

[Guest]

I entered this in the Code Module, and then set my control source to
MyResult(). I'm getting an outcome of "#Name?" I have all of my fields that
are being calculated set as either currency or number. Do they all have to
be the same? Would there be another reason for getting this result?

Thanks,

Matt

Hi, Matt.

The function definition should be entered in the form's Code Module. There
are a number of ways to enter the module, probably the easiest is, from Form
Design View, pressing <Ctrl-G>, which opens the module and places you in the
"Immediate Window"--used for testing the values of expressions as a debugging
tool. Move your cursor to the window above the Immediate Window, and enter
the function definition there.

Any function defined in a form's module has scope limited to that form,
which is fine here. If you dream up more general functions that you'd like
to access from anywhere, they should be defined in a general module (click
the Module tab, define a new module), and should be given universal scope
with the keyword Public:

Public Function MyGeneralFunction() As ...
....
End Function

As to your form control, once the function is defined in its form module,
you get its value by assigning the ControlSource to the value of the function:

=MyResult()

Hope that helps.
Sprinks

Thanks for your response Sprinks. Would I enter this function in the
Expression Builder? This looks like a Visual Basic function. I could be
wrong, I haven't used it in a while. If this is a Visual Basic function,
would I need to write this in another place in my database and then reference
it in my control source?

Hi, Matt.

I can’t see any typos, although verify that [Plan] is truly a text field
rather than merely displaying text yet storing an underlying code.

I think, though, that complex nested IF function calls are better replaced
by a custom function stored in the form module, being much easier to read and
debug. You can also combine the first three into a single boolean value with
the OR conjunction:

Function MyResult() As Single

If ([Plan] = “Smart Trunk†OR [Plan] = “Super Trunk†OR [Plan] = “IASâ€)
Then
MyResult = [NNI]*[Term Length]
Else
If [Completelink] = 0 Then
MyResult = [Trunks/Lines] * [RU] + _
[Trunks/Lines2] * [RU2] + _
[Trunks/Lines3] * [RU3] + _
[Trunks/Lines4] * [RU4]
MyResult = MyResult * (1-[DiscountRate]) * [Term Length]
Else
MyResult = [Completelink] * [Term Length] / 12
End If
End If

End Function

Then set the ControlSource to: = MyResult()

Hope that helps.
Sprinks

:

I am trying to write an If statement in my form that calculates the total
contract value of a sale. The text box is locked so people can't change
anything, but I want it to show them how much they sold to a customer. Here
is the formula that I'm trying to use:

=IF([Plan]="Smart Trunk",[NNI]*[Term Length],IF([Plan]="Super
Trunk",[NNI]*[Term Length],IF([Plan]="IAS",[NNI]*[Term
Length],IF([Completelink]=0,((([Trunks/Lines]*[RU])+([Trunks/Lines2]*[RU2])+([Trunks/Lines3]*[RU3])+([Trunks/Lines4]*[RU4]))*(1-[Discount
Rate]))*[Term Length],[Completelink]*([Term Length]/12)))))

I don't know if this makes sense, but can anyone let me know why this isn't
working?

 

[Guest]

Hi, Matt. Sorry you're having problems. Be sure that you included the equal
sign in your ControlSource, i.e.,

=MyResult()

not

MyResult(), which will generate the #Name? error. If that doesn't solve the
problem, I'd start by checking for typos in the fieldnames. Also, I forgot
to mention that you should convert all nulls to zero with the Nz() function
for all of the numeric fields, or you will trigger an error.

If ([Plan] = “Smart Trunk†OR [Plan] = “Super Trunk†OR [Plan] = “IASâ€)
Then
MyResult = Nz([NNI])*Nz([Term Length])
Else
If Nz([Completelink]) = 0 Then
MyResult = Nz([Trunks/Lines]) * Nz([[RU]) + _
Nz([Trunks/Lines2]) * Nz([[RU2]) + _
Nz([Trunks/Lines3]) * Nz([[RU3]) + _
Nz([Trunks/Lines4]) * Nz([[RU4])
MyResult = MyResult * (1-Nz([DiscountRate])) * Nz([[Term Length])
Else
MyResult = Nz([Completelink]) * Nz([[Term Length]) / 12
End If
End If

End Function

Hope that solves it.
Sprinks

I entered this in the Code Module, and then set my control source to
MyResult(). I'm getting an outcome of "#Name?" I have all of my fields that
are being calculated set as either currency or number. Do they all have to
be the same? Would there be another reason for getting this result?

Thanks,

Matt

Hi, Matt.

The function definition should be entered in the form's Code Module. There
are a number of ways to enter the module, probably the easiest is, from Form
Design View, pressing <Ctrl-G>, which opens the module and places you in the
"Immediate Window"--used for testing the values of expressions as a debugging
tool. Move your cursor to the window above the Immediate Window, and enter
the function definition there.

Any function defined in a form's module has scope limited to that form,
which is fine here. If you dream up more general functions that you'd like
to access from anywhere, they should be defined in a general module (click
the Module tab, define a new module), and should be given universal scope
with the keyword Public:

Public Function MyGeneralFunction() As ...
....
End Function

As to your form control, once the function is defined in its form module,
you get its value by assigning the ControlSource to the value of the function:

=MyResult()

Hope that helps.
Sprinks

Thanks for your response Sprinks. Would I enter this function in the
Expression Builder? This looks like a Visual Basic function. I could be
wrong, I haven't used it in a while. If this is a Visual Basic function,
would I need to write this in another place in my database and then reference
it in my control source?

:

Hi, Matt.

I can’t see any typos, although verify that [Plan] is truly a text field
rather than merely displaying text yet storing an underlying code.

I think, though, that complex nested IF function calls are better replaced
by a custom function stored in the form module, being much easier to read and
debug. You can also combine the first three into a single boolean value with
the OR conjunction:

Function MyResult() As Single

If ([Plan] = “Smart Trunk†OR [Plan] = “Super Trunk†OR [Plan] = “IASâ€)
Then
MyResult = [NNI]*[Term Length]
Else
If [Completelink] = 0 Then
MyResult = [Trunks/Lines] * [RU] + _
[Trunks/Lines2] * [RU2] + _
[Trunks/Lines3] * [RU3] + _
[Trunks/Lines4] * [RU4]
MyResult = MyResult * (1-[DiscountRate]) * [Term Length]
Else
MyResult = [Completelink] * [Term Length] / 12
End If
End If

End Function

Then set the ControlSource to: = MyResult()

Hope that helps.
Sprinks

:

I am trying to write an If statement in my form that calculates the total
contract value of a sale. The text box is locked so people can't change
anything, but I want it to show them how much they sold to a customer. Here
is the formula that I'm trying to use:

=IF([Plan]="Smart Trunk",[NNI]*[Term Length],IF([Plan]="Super
Trunk",[NNI]*[Term Length],IF([Plan]="IAS",[NNI]*[Term
Length],IF([Completelink]=0,((([Trunks/Lines]*[RU])+([Trunks/Lines2]*[RU2])+([Trunks/Lines3]*[RU3])+([Trunks/Lines4]*[RU4]))*(1-[Discount
Rate]))*[Term Length],[Completelink]*([Term Length]/12)))))

I don't know if this makes sense, but can anyone let me know why this isn't
working?

 

[Guest]

Sprinks,

Thanks for your help on this. I figured out what was wrong. For some
reason, Access makes you put an "I" in front of the IF. example: IIF....
Once I put the extra "I" in, it worked.

Hi, Matt. Sorry you're having problems. Be sure that you included the equal
sign in your ControlSource, i.e.,

=MyResult()

not

MyResult(), which will generate the #Name? error. If that doesn't solve the
problem, I'd start by checking for typos in the fieldnames. Also, I forgot
to mention that you should convert all nulls to zero with the Nz() function
for all of the numeric fields, or you will trigger an error.

If ([Plan] = “Smart Trunk†OR [Plan] = “Super Trunk†OR [Plan] = “IASâ€)
Then
MyResult = Nz([NNI])*Nz([Term Length])
Else
If Nz([Completelink]) = 0 Then
MyResult = Nz([Trunks/Lines]) * Nz([[RU]) + _
Nz([Trunks/Lines2]) * Nz([[RU2]) + _
Nz([Trunks/Lines3]) * Nz([[RU3]) + _
Nz([Trunks/Lines4]) * Nz([[RU4])
MyResult = MyResult * (1-Nz([DiscountRate])) * Nz([[Term Length])
Else
MyResult = Nz([Completelink]) * Nz([[Term Length]) / 12
End If
End If

End Function

Hope that solves it.
Sprinks

I entered this in the Code Module, and then set my control source to
MyResult(). I'm getting an outcome of "#Name?" I have all of my fields that
are being calculated set as either currency or number. Do they all have to
be the same? Would there be another reason for getting this result?

Thanks,

Matt

Hi, Matt.

The function definition should be entered in the form's Code Module. There
are a number of ways to enter the module, probably the easiest is, from Form
Design View, pressing <Ctrl-G>, which opens the module and places you in the
"Immediate Window"--used for testing the values of expressions as a debugging
tool. Move your cursor to the window above the Immediate Window, and enter
the function definition there.

Any function defined in a form's module has scope limited to that form,
which is fine here. If you dream up more general functions that you'd like
to access from anywhere, they should be defined in a general module (click
the Module tab, define a new module), and should be given universal scope
with the keyword Public:

Public Function MyGeneralFunction() As ...
....
End Function

As to your form control, once the function is defined in its form module,
you get its value by assigning the ControlSource to the value of the function:

=MyResult()

Hope that helps.
Sprinks

:

Thanks for your response Sprinks. Would I enter this function in the
Expression Builder? This looks like a Visual Basic function. I could be
wrong, I haven't used it in a while. If this is a Visual Basic function,
would I need to write this in another place in my database and then reference
it in my control source?

:

Hi, Matt.

I can’t see any typos, although verify that [Plan] is truly a text field
rather than merely displaying text yet storing an underlying code.

I think, though, that complex nested IF function calls are better replaced
by a custom function stored in the form module, being much easier to read and
debug. You can also combine the first three into a single boolean value with
the OR conjunction:

Function MyResult() As Single

If ([Plan] = “Smart Trunk†OR [Plan] = “Super Trunk†OR [Plan] = “IASâ€)
Then
MyResult = [NNI]*[Term Length]
Else
If [Completelink] = 0 Then
MyResult = [Trunks/Lines] * [RU] + _
[Trunks/Lines2] * [RU2] + _
[Trunks/Lines3] * [RU3] + _
[Trunks/Lines4] * [RU4]
MyResult = MyResult * (1-[DiscountRate]) * [Term Length]
Else
MyResult = [Completelink] * [Term Length] / 12
End If
End If

End Function

Then set the ControlSource to: = MyResult()

Hope that helps.
Sprinks

:

I am trying to write an If statement in my form that calculates the total
contract value of a sale. The text box is locked so people can't change
anything, but I want it to show them how much they sold to a customer. Here
is the formula that I'm trying to use:

=IF([Plan]="Smart Trunk",[NNI]*[Term Length],IF([Plan]="Super
Trunk",[NNI]*[Term Length],IF([Plan]="IAS",[NNI]*[Term
Length],IF([Completelink]=0,((([Trunks/Lines]*[RU])+([Trunks/Lines2]*[RU2])+([Trunks/Lines3]*[RU3])+([Trunks/Lines4]*[RU4]))*(1-[Discount
Rate]))*[Term Length],[Completelink]*([Term Length]/12)))))

I don't know if this makes sense, but can anyone let me know why this isn't
working?

 

[Guest]

Cool. And my pleasure.

Sprinks

Sprinks,

Thanks for your help on this. I figured out what was wrong. For some
reason, Access makes you put an "I" in front of the IF. example: IIF....
Once I put the extra "I" in, it worked.

Hi, Matt. Sorry you're having problems. Be sure that you included the equal
sign in your ControlSource, i.e.,

=MyResult()

not

MyResult(), which will generate the #Name? error. If that doesn't solve the
problem, I'd start by checking for typos in the fieldnames. Also, I forgot
to mention that you should convert all nulls to zero with the Nz() function
for all of the numeric fields, or you will trigger an error.

If ([Plan] = “Smart Trunk†OR [Plan] = “Super Trunk†OR [Plan] = “IASâ€)
Then
MyResult = Nz([NNI])*Nz([Term Length])
Else
If Nz([Completelink]) = 0 Then
MyResult = Nz([Trunks/Lines]) * Nz([[RU]) + _
Nz([Trunks/Lines2]) * Nz([[RU2]) + _
Nz([Trunks/Lines3]) * Nz([[RU3]) + _
Nz([Trunks/Lines4]) * Nz([[RU4])
MyResult = MyResult * (1-Nz([DiscountRate])) * Nz([[Term Length])
Else
MyResult = Nz([Completelink]) * Nz([[Term Length]) / 12
End If
End If

End Function

Hope that solves it.
Sprinks

I entered this in the Code Module, and then set my control source to
MyResult(). I'm getting an outcome of "#Name?" I have all of my fields that
are being calculated set as either currency or number. Do they all have to
be the same? Would there be another reason for getting this result?

Thanks,

Matt

:

Hi, Matt.

The function definition should be entered in the form's Code Module. There
are a number of ways to enter the module, probably the easiest is, from Form
Design View, pressing <Ctrl-G>, which opens the module and places you in the
"Immediate Window"--used for testing the values of expressions as a debugging
tool. Move your cursor to the window above the Immediate Window, and enter
the function definition there.

Any function defined in a form's module has scope limited to that form,
which is fine here. If you dream up more general functions that you'd like
to access from anywhere, they should be defined in a general module (click
the Module tab, define a new module), and should be given universal scope
with the keyword Public:

Public Function MyGeneralFunction() As ...
....
End Function

As to your form control, once the function is defined in its form module,
you get its value by assigning the ControlSource to the value of the function:

=MyResult()

Hope that helps.
Sprinks

:

Thanks for your response Sprinks. Would I enter this function in the
Expression Builder? This looks like a Visual Basic function. I could be
wrong, I haven't used it in a while. If this is a Visual Basic function,
would I need to write this in another place in my database and then reference
it in my control source?

:

Hi, Matt.

I can’t see any typos, although verify that [Plan] is truly a text field
rather than merely displaying text yet storing an underlying code.

I think, though, that complex nested IF function calls are better replaced
by a custom function stored in the form module, being much easier to read and
debug. You can also combine the first three into a single boolean value with
the OR conjunction:

Function MyResult() As Single

If ([Plan] = “Smart Trunk†OR [Plan] = “Super Trunk†OR [Plan] = “IASâ€)
Then
MyResult = [NNI]*[Term Length]
Else
If [Completelink] = 0 Then
MyResult = [Trunks/Lines] * [RU] + _
[Trunks/Lines2] * [RU2] + _
[Trunks/Lines3] * [RU3] + _
[Trunks/Lines4] * [RU4]
MyResult = MyResult * (1-[DiscountRate]) * [Term Length]
Else
MyResult = [Completelink] * [Term Length] / 12
End If
End If

End Function

Then set the ControlSource to: = MyResult()

Hope that helps.
Sprinks

:

I am trying to write an If statement in my form that calculates the total
contract value of a sale. The text box is locked so people can't change
anything, but I want it to show them how much they sold to a customer. Here
is the formula that I'm trying to use:

=IF([Plan]="Smart Trunk",[NNI]*[Term Length],IF([Plan]="Super
Trunk",[NNI]*[Term Length],IF([Plan]="IAS",[NNI]*[Term
Length],IF([Completelink]=0,((([Trunks/Lines]*[RU])+([Trunks/Lines2]*[RU2])+([Trunks/Lines3]*[RU3])+([Trunks/Lines4]*[RU4]))*(1-[Discount
Rate]))*[Term Length],[Completelink]*([Term Length]/12)))))

I don't know if this makes sense, but can anyone let me know why this isn't
working?