Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

IIf usage

Discussion in 'Information Technology' started by lmcdougall, Jul 28, 2006.

Tags:
  1. lmcdougall

    lmcdougall
    Expand Collapse
    Guest

    I am not an Access programmer just an old C programmer looking to make
    sense of a problem.
    My thanks in advance for any help.

    In a report I am trying to print a value base on a Boolean condition. I am
    familiar with the inline if stamen in C.
    I am getting the word Error printed in the report. This is what I have as a
    value source.
    =IIf([tblRepoClient]![PrintLien]<>0,[LienAmt],0)

    L.McDougall
     
  2. Loading...

    Similar Threads Forum Date
    India IIFA 2014: ‘Bhaag Milkha Bhaag’ wins 9 technical awards! Breaking News Mar 5, 2014
    Usage of the Word - Akaalpurkh in Sikhi Gurmat Vichaar Jun 28, 2009
    'Currywurst' sausage museum to open in Berlin (Reuters) Interfaith Dialogues Jul 1, 2005

  3. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    Try

    =IIf([PrintLien]=True,[LienAmt],0)

    --
    Good Luck
    BS"D


    "lmcdougall" wrote:

    > I am not an Access programmer just an old C programmer looking to make
    > sense of a problem.
    > My thanks in advance for any help.
    >
    > In a report I am trying to print a value base on a Boolean condition. I am
    > familiar with the inline if stamen in C.
    > I am getting the word Error printed in the report. This is what I have as a
    > value source.
    > =IIf([tblRepoClient]![PrintLien]<>0,[LienAmt],0)
    >
    > L.McDougall
    >
    >
    >
    >
     
  4. Grover Park George

    Grover Park George
    Expand Collapse
    Guest

    In Access, this error generally occurs when the name of the control on
    the report (or form) is the same as the underlying field in the
    object's recordsource. Try renaming the control on the report to
    txtLienAmt and see if that fixes it.

    George


    lmcdougall wrote:
    > I am not an Access programmer just an old C programmer looking to make
    > sense of a problem.
    > My thanks in advance for any help.
    >
    > In a report I am trying to print a value base on a Boolean condition. I am
    > familiar with the inline if stamen in C.
    > I am getting the word Error printed in the report. This is what I have as a
    > value source.
    > =IIf([tblRepoClient]![PrintLien]<>0,[LienAmt],0)
    >
    > L.McDougall
     
  5. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Pardon me for butting in, George... There is never a problem with a
    bound control having the same name as the underlying field. In fact,
    this is Access's default behaviour. The problem arises if there is a
    control named the same as a field in the report's record source, but the
    control is *not* bound to that control. So, L.McDougall will have a
    problem if the control where this IIf() expression is being entered is
    named the same as any field in the table/query that the report is based on.

    --
    Steve Schapel, Microsoft Access MVP

    Grover Park George wrote:
    > In Access, this error generally occurs when the name of the control on
    > the report (or form) is the same as the underlying field in the
    > object's recordsource. Try renaming the control on the report to
    > txtLienAmt and see if that fixes it.
     
  6. lmcdougall

    lmcdougall
    Expand Collapse
    Guest

    Ok,
    I got the message:(I think).
    do not name a control(Widget) with any name used in columns or fields
    previously used.
    I do not understand this " but the control is *not* bound to that
    control." .
    Can you explain more? Please.

    I am using a name is not used before. (for testing purposes) newfield123 and
    the behavior continues.
    #Error


    L.McDougall

    "Steve Schapel" <schapel@mvps.org.ns> wrote in message
    news:e6NbMtImGHA.3844@TK2MSFTNGP04.phx.gbl...
    > Pardon me for butting in, George... There is never a problem with a bound
    > control having the same name as the underlying field. In fact, this is
    > Access's default behaviour. The problem arises if there is a control
    > named the same as a field in the report's record source, but the control
    > is *not* bound to that control. So, L.McDougall will have a problem if
    > the control where this IIf() expression is being entered is named the same
    > as any field in the table/query that the report is based on.
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > Grover Park George wrote:
    >> In Access, this error generally occurs when the name of the control on
    >> the report (or form) is the same as the underlying field in the
    >> object's recordsource. Try renaming the control on the report to
    >> txtLienAmt and see if that fixes it.
     
  7. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    L.McDougall,

    I meant to put: "but the control is *not* bound to that field". I'm
    sorry for the confusion. What I mean to say is...
    If the report's underlying table/query has a field named Widget, and you
    have a textbox on the report which is bound to the Widget field, then it
    is fine if the textbox is named Widget, and it is also fine if the
    textbox is named txtWidget or something else, but it is *not* fine if
    *another* textbox, which is *not* bound to the Widget field, is named
    Widget.

    So, my thought was that you have a textbox, where you are entering an
    expression with the IIf() function, in its Control Source. Therefore
    this control is not bound to a field in the report's record source
    table/query. Therefore the textbox should not have the same name as any
    field in the report's record source table/query.

    Ok, so let's clarify... You have a textbox and the name of the textbox
    is newfield123, is that correct? And there is not a field called
    newfield123 in the table/query that the report is based on, correct?
    Ok, is the PrintLien field included in the report's underlying
    table/query? And if so, is it a Yes/No data type field? And is the
    LienAmt field also included in the report's underlying table/query? If
    so, what happens if you simplify the expression in the newfield123
    textbox like this?...
    =IIf([PrintLien],[LienAmt],0)

    --
    Steve Schapel, Microsoft Access MVP

    lmcdougall wrote:
    > Ok,
    > I got the message:(I think).
    > do not name a control(Widget) with any name used in columns or fields
    > previously used.
    > I do not understand this " but the control is *not* bound to that
    > control." .
    > Can you explain more? Please.
    >
    > I am using a name is not used before. (for testing purposes) newfield123 and
    > the behavior continues.
    > #Error
     
  8. Rob Parker

    Rob Parker
    Expand Collapse
    Guest

    And pardon me, also. I happen to believe, very strongly, that you are wrong
    when you say "There is never a problem with a bound control having the same
    name as the underlying field.". You've previously posted a similar
    "correction" to an answer of mine which was almost word-for-word the same as
    the one George gave here. I didn't pursue it firther then, but I feel that
    it does need clarifying.

    This situation (fairly common) where the name of a control is the same as
    the same of the field to which it is bound, will always give #error when
    used in an Iif() expression such as:
    =iif([fieldname]=something,somethingelse,[fieldname])
    It will not give #error if the control is named anything other than
    "fieldname". I find this behaviour in A97, A2000, A2002 and A2003.

    The Microsoft site itself notes this clash of field name and control name as
    the source of #error:
    http://office.microsoft.com/en-us/assistance/HA011814481033.aspx

    Rob

    "Steve Schapel" <schapel@mvps.org.ns> wrote in message
    news:e6NbMtImGHA.3844@TK2MSFTNGP04.phx.gbl...
    > Pardon me for butting in, George... There is never a problem with a bound
    > control having the same name as the underlying field. In fact, this is
    > Access's default behaviour. The problem arises if there is a control
    > named the same as a field in the report's record source, but the control
    > is *not* bound to that control. So, L.McDougall will have a problem if
    > the control where this IIf() expression is being entered is named the same
    > as any field in the table/query that the report is based on.
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > Grover Park George wrote:
    >> In Access, this error generally occurs when the name of the control on
    >> the report (or form) is the same as the underlying field in the
    >> object's recordsource. Try renaming the control on the report to
    >> txtLienAmt and see if that fixes it.
     
  9. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Rob,

    Thanks for this clarification.

    In the example you gave, you do not have a control whose Control Source
    is a field in the form's or report's Record Source. You have a control
    whose Control Source is a calculation based on an expression, in this
    case including the use of a function. You are absolutely correct, that
    this will cause an #Error if the control is named the same as
    [fieldname]. In fact. it will cause an #Error if it is named the same
    as *any* field in the form/report's record source table/query, including
    [fieldname]. We do not disagree on this. In fact if you read my posts,
    you will see that this is precisely what I advised.

    The question of the naming of a control which is bound to a field, which
    is what I was referring to, is an entirely different matter.

    --
    Steve Schapel, Microsoft Access MVP

    Rob Parker wrote:
    > And pardon me, also. I happen to believe, very strongly, that you are wrong
    > when you say "There is never a problem with a bound control having the same
    > name as the underlying field.". You've previously posted a similar
    > "correction" to an answer of mine which was almost word-for-word the same as
    > the one George gave here. I didn't pursue it firther then, but I feel that
    > it does need clarifying.
    >
    > This situation (fairly common) where the name of a control is the same as
    > the same of the field to which it is bound, will always give #error when
    > used in an Iif() expression such as:
    > =iif([fieldname]=something,somethingelse,[fieldname])
    > It will not give #error if the control is named anything other than
    > "fieldname". I find this behaviour in A97, A2000, A2002 and A2003.
    >
    > The Microsoft site itself notes this clash of field name and control name as
    > the source of #error:
    > http://office.microsoft.com/en-us/assistance/HA011814481033.aspx
     
  10. Rob Parker

    Rob Parker
    Expand Collapse
    Guest

    You're correct, in that the control's recordsource is not a field; it is an
    expression which refers to the field, which has the same name as the
    control. And, in common practice, this situation arises because the
    designer drags a field to the form, which creates a control with the field's
    name as the control's name. The designer then changes the control's source
    to be, not the field, but an expression which refers to the field. And
    bingo ... #error from the circular reference - which I claim (and I'm sure
    with a considerable amount of support) - arises from Access default
    behaviour of naming a control the same as the bound field.

    Can we agree on that :)

    Rob

    "Steve Schapel" <schapel@mvps.org.ns> wrote in message
    news:uXQnHXOmGHA.4700@TK2MSFTNGP02.phx.gbl...
    > Rob,
    >
    > Thanks for this clarification.
    >
    > In the example you gave, you do not have a control whose Control Source is
    > a field in the form's or report's Record Source. You have a control whose
    > Control Source is a calculation based on an expression, in this case
    > including the use of a function. You are absolutely correct, that this
    > will cause an #Error if the control is named the same as [fieldname]. In
    > fact. it will cause an #Error if it is named the same as *any* field in
    > the form/report's record source table/query, including [fieldname]. We do
    > not disagree on this. In fact if you read my posts, you will see that
    > this is precisely what I advised.
    >
    > The question of the naming of a control which is bound to a field, which
    > is what I was referring to, is an entirely different matter.
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > Rob Parker wrote:
    >> And pardon me, also. I happen to believe, very strongly, that you are
    >> wrong when you say "There is never a problem with a bound control having
    >> the same name as the underlying field.". You've previously posted a
    >> similar "correction" to an answer of mine which was almost word-for-word
    >> the same as the one George gave here. I didn't pursue it firther then,
    >> but I feel that it does need clarifying.
    >>
    >> This situation (fairly common) where the name of a control is the same as
    >> the same of the field to which it is bound, will always give #error when
    >> used in an Iif() expression such as:
    >> =iif([fieldname]=something,somethingelse,[fieldname])
    >> It will not give #error if the control is named anything other than
    >> "fieldname". I find this behaviour in A97, A2000, A2002 and A2003.
    >>
    >> The Microsoft site itself notes this clash of field name and control name
    >> as the source of #error:
    >> http://office.microsoft.com/en-us/assistance/HA011814481033.aspx
     
  11. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Rob,

    At the risk of belabouring a point...

    I think we can agree on most of it. :) The part I have difficulty with
    is "arises from Access default behaviour". I do understand what you
    mean, of course. But the way I look at it, it arises from the
    developer's failure to follow a simple rule. That simple rule is "don't
    name a control the same as a field if it isn't bound to that field".
    It's easy. A control with an expression control source, especially on a
    bound form, is frequently quite distinct from the bound controls. If
    you're going to make such a fundamental change to a control as changing
    its control source from a field to an expression, there are often
    implications for other properties of the control as well... Tab Stop,
    maybe Fore Color or Back Color, etc. And Name. If a developer messes
    with the Control Source of a control, and neglects to consider the
    implications for the Name property, I don't think he can blame Access.
    I think he should blame the fact that he doesn't know (or failed to
    observe) the rule "don't name a control the same as a field if it isn't
    bound to that field". To some extent, I believe that the lack of
    clarity about the rule is because another rule "don't name a control the
    same as the field it is bound to" has been perpetrated in newsgroup
    folklore. This rule is false, and also irrelevant to the problem, if
    you think carefully about it. What proportion of controls on a bound
    form or report are bound to fields, and what proportion have an
    expression in their Control Source? Of those with an expression, what
    proportion get that way via being modified from a control initially
    bound to a field? The false rule nominally applies itself to all
    controls. The true rule only needs to be considered in that small
    number of cases where you're messing with the control source of a bound
    control. Whether you like Access's default behaviour or not, I don't
    think it will change soon. So IMHO I think it's best to be as accurate
    as possible in the statement of the issue. Hope you can understand :).

    --
    Steve Schapel, Microsoft Access MVP


    Rob Parker wrote:
    > You're correct, in that the control's recordsource is not a field; it is an
    > expression which refers to the field, which has the same name as the
    > control. And, in common practice, this situation arises because the
    > designer drags a field to the form, which creates a control with the field's
    > name as the control's name. The designer then changes the control's source
    > to be, not the field, but an expression which refers to the field. And
    > bingo ... #error from the circular reference - which I claim (and I'm sure
    > with a considerable amount of support) - arises from Access default
    > behaviour of naming a control the same as the bound field.
    >
    > Can we agree on that :)
     
  12. lmcdougall

    lmcdougall
    Expand Collapse
    Guest

    That was fantastic!
    I am impress with the wiliness of you guys to help. Now I understand the
    concept very well and.
    have fixed the problem thanks to your kindness and deep knowledge of the
    matter.

    L.McDougall
    "Steve Schapel" <schapel@mvps.org.ns> wrote in message
    news:uhbOySPmGHA.1488@TK2MSFTNGP02.phx.gbl...
    > Rob,
    >
    > At the risk of belabouring a point...
    >
    > I think we can agree on most of it. :) The part I have difficulty with
    > is "arises from Access default behaviour". I do understand what you mean,
    > of course. But the way I look at it, it arises from the developer's
    > failure to follow a simple rule. That simple rule is "don't name a
    > control the same as a field if it isn't bound to that field". It's easy.
    > A control with an expression control source, especially on a bound form,
    > is frequently quite distinct from the bound controls. If you're going to
    > make such a fundamental change to a control as changing its control source
    > from a field to an expression, there are often implications for other
    > properties of the control as well... Tab Stop, maybe Fore Color or Back
    > Color, etc. And Name. If a developer messes with the Control Source of a
    > control, and neglects to consider the implications for the Name property,
    > I don't think he can blame Access. I think he should blame the fact that
    > he doesn't know (or failed to observe) the rule "don't name a control the
    > same as a field if it isn't bound to that field". To some extent, I
    > believe that the lack of clarity about the rule is because another rule
    > "don't name a control the same as the field it is bound to" has been
    > perpetrated in newsgroup folklore. This rule is false, and also
    > irrelevant to the problem, if you think carefully about it. What
    > proportion of controls on a bound form or report are bound to fields, and
    > what proportion have an expression in their Control Source? Of those with
    > an expression, what proportion get that way via being modified from a
    > control initially bound to a field? The false rule nominally applies
    > itself to all controls. The true rule only needs to be considered in that
    > small number of cases where you're messing with the control source of a
    > bound control. Whether you like Access's default behaviour or not, I
    > don't think it will change soon. So IMHO I think it's best to be as
    > accurate as possible in the statement of the issue. Hope you can
    > understand :).
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    >
    > Rob Parker wrote:
    >> You're correct, in that the control's recordsource is not a field; it is
    >> an expression which refers to the field, which has the same name as the
    >> control. And, in common practice, this situation arises because the
    >> designer drags a field to the form, which creates a control with the
    >> field's name as the control's name. The designer then changes the
    >> control's source to be, not the field, but an expression which refers to
    >> the field. And bingo ... #error from the circular reference - which I
    >> claim (and I'm sure with a considerable amount of support) - arises from
    >> Access default behaviour of naming a control the same as the bound field.
    >>
    >> Can we agree on that :)
     

Share This Page