Welcome to SPN

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

Sign Up Now!

Complex(for me) IIf Statement

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

  1. F1stman

    F1stman
    Expand Collapse
    Guest

    Hello All,
    I am trying to create an if statement to select the correct account code on
    one form and place that code onto the current form. Something like:

    If PFS=1 then set value to [Central Exhibit].[Account Code 1] or if If PFS=2
    then set value to [Central Exhibit].[Account Code 2] or If PFS=3 then set
    value to [Central Exhibit].[Account Code 3], etc.

    PFS is on the current form and Account code is obviously on another form
    which is open at the same time. There are six total possibilities. I can get
    it to work with one IIf statement. After that I'm sunk. Thank you in advance.
    This group is sooo helpful and had taught me much. Adam
     
  2. Loading...

    Similar Threads Forum Date
    Canada Canada's Gift for Baby Sushmit's Firang Name (Foreign Name) Breaking News Jul 1, 2011
    Sabad and Naam (Word and Name) Sikh Sikhi Sikhism Oct 6, 2009
    Greenwich man uses love of travel, religion in selling statues (Greenwich Time) Interfaith Dialogues Jun 2, 2005
    Sikh News Got (Surname) in Sikhi Breaking News Feb 10, 2005

  3. SusanV

    SusanV
    Expand Collapse
    Guest

    Hi F1stman,

    Personally I would use a Case statement, as it will be easier to extend in
    the future should you need to add PFS options:

    Select Case PFS
    Case 1
    'what you want to happen
    Case 2
    'what you want for PFS of 2
    'etc etc
    End Select


    --
    hth,
    SusanV


    "F1stman" <F1stman@discussions.microsoft.com> wrote in message
    news:84842D5C-FE81-4FD4-885B-248AEB25375F@microsoft.com...
    > Hello All,
    > I am trying to create an if statement to select the correct account code
    > on
    > one form and place that code onto the current form. Something like:
    >
    > If PFS=1 then set value to [Central Exhibit].[Account Code 1] or if If
    > PFS=2
    > then set value to [Central Exhibit].[Account Code 2] or If PFS=3 then set
    > value to [Central Exhibit].[Account Code 3], etc.
    >
    > PFS is on the current form and Account code is obviously on another form
    > which is open at the same time. There are six total possibilities. I can
    > get
    > it to work with one IIf statement. After that I'm sunk. Thank you in
    > advance.
    > This group is sooo helpful and had taught me much. Adam
    >
     
  4. F1stman

    F1stman
    Expand Collapse
    Guest

    Hello Susan,
    Thank you for the quick response. Is there anywhere you know that I can
    learn more about the case statement. I'm not a whiz when it comes to VBA.
    There isn't much info in the assistance section of the Microsoft website or
    in this discussion group. Thanks, Adam

    "SusanV" wrote:

    > Hi F1stman,
    >
    > Personally I would use a Case statement, as it will be easier to extend in
    > the future should you need to add PFS options:
    >
    > Select Case PFS
    > Case 1
    > 'what you want to happen
    > Case 2
    > 'what you want for PFS of 2
    > 'etc etc
    > End Select
    >
    >
    > --
    > hth,
    > SusanV
    >
    >
    > "F1stman" <F1stman@discussions.microsoft.com> wrote in message
    > news:84842D5C-FE81-4FD4-885B-248AEB25375F@microsoft.com...
    > > Hello All,
    > > I am trying to create an if statement to select the correct account code
    > > on
    > > one form and place that code onto the current form. Something like:
    > >
    > > If PFS=1 then set value to [Central Exhibit].[Account Code 1] or if If
    > > PFS=2
    > > then set value to [Central Exhibit].[Account Code 2] or If PFS=3 then set
    > > value to [Central Exhibit].[Account Code 3], etc.
    > >
    > > PFS is on the current form and Account code is obviously on another form
    > > which is open at the same time. There are six total possibilities. I can
    > > get
    > > it to work with one IIf statement. After that I'm sunk. Thank you in
    > > advance.
    > > This group is sooo helpful and had taught me much. Adam
    > >

    >
    >
    >
     
  5. fredg

    fredg
    Expand Collapse
    Guest

    On Tue, 16 May 2006 10:03:02 -0700, F1stman wrote:

    > Hello Susan,
    > Thank you for the quick response. Is there anywhere you know that I can
    > learn more about the case statement. I'm not a whiz when it comes to VBA.
    > There isn't much info in the assistance section of the Microsoft website or
    > in this discussion group. Thanks, Adam
    >
    > "SusanV" wrote:
    >
    >> Hi F1stman,
    >>
    >> Personally I would use a Case statement, as it will be easier to extend in
    >> the future should you need to add PFS options:
    >>
    >> Select Case PFS
    >> Case 1
    >> 'what you want to happen
    >> Case 2
    >> 'what you want for PFS of 2
    >> 'etc etc
    >> End Select
    >>
    >> --
    >> hth,
    >> SusanV
    >>
    >> "F1stman" <F1stman@discussions.microsoft.com> wrote in message
    >> news:84842D5C-FE81-4FD4-885B-248AEB25375F@microsoft.com...
    >>> Hello All,
    >>> I am trying to create an if statement to select the correct account code
    >>> on
    >>> one form and place that code onto the current form. Something like:
    >>>
    >>> If PFS=1 then set value to [Central Exhibit].[Account Code 1] or if If
    >>> PFS=2
    >>> then set value to [Central Exhibit].[Account Code 2] or If PFS=3 then set
    >>> value to [Central Exhibit].[Account Code 3], etc.
    >>>
    >>> PFS is on the current form and Account code is obviously on another form
    >>> which is open at the same time. There are six total possibilities. I can
    >>> get
    >>> it to work with one IIf statement. After that I'm sunk. Thank you in
    >>> advance.
    >>> This group is sooo helpful and had taught me much. Adam
    >>>

    >>
    >>


    Did you not check VBA Help?
    There are some example of Select Case there.

    --
    Fred
    Please respond only to this newsgroup.
    I do not reply to personal e-mail
     
  6. F1stman

    F1stman
    Expand Collapse
    Guest

    Thank you so much Fred. I should have thought of that. Have a great day. Adam

    "fredg" wrote:

    > On Tue, 16 May 2006 10:03:02 -0700, F1stman wrote:
    >
    > > Hello Susan,
    > > Thank you for the quick response. Is there anywhere you know that I can
    > > learn more about the case statement. I'm not a whiz when it comes to VBA.
    > > There isn't much info in the assistance section of the Microsoft website or
    > > in this discussion group. Thanks, Adam
    > >
    > > "SusanV" wrote:
    > >
    > >> Hi F1stman,
    > >>
    > >> Personally I would use a Case statement, as it will be easier to extend in
    > >> the future should you need to add PFS options:
    > >>
    > >> Select Case PFS
    > >> Case 1
    > >> 'what you want to happen
    > >> Case 2
    > >> 'what you want for PFS of 2
    > >> 'etc etc
    > >> End Select
    > >>
    > >> --
    > >> hth,
    > >> SusanV
    > >>
    > >> "F1stman" <F1stman@discussions.microsoft.com> wrote in message
    > >> news:84842D5C-FE81-4FD4-885B-248AEB25375F@microsoft.com...
    > >>> Hello All,
    > >>> I am trying to create an if statement to select the correct account code
    > >>> on
    > >>> one form and place that code onto the current form. Something like:
    > >>>
    > >>> If PFS=1 then set value to [Central Exhibit].[Account Code 1] or if If
    > >>> PFS=2
    > >>> then set value to [Central Exhibit].[Account Code 2] or If PFS=3 then set
    > >>> value to [Central Exhibit].[Account Code 3], etc.
    > >>>
    > >>> PFS is on the current form and Account code is obviously on another form
    > >>> which is open at the same time. There are six total possibilities. I can
    > >>> get
    > >>> it to work with one IIf statement. After that I'm sunk. Thank you in
    > >>> advance.
    > >>> This group is sooo helpful and had taught me much. Adam
    > >>>
    > >>
    > >>

    >
    > Did you not check VBA Help?
    > There are some example of Select Case there.
    >
    > --
    > Fred
    > Please respond only to this newsgroup.
    > I do not reply to personal e-mail
    >
     
  7. SusanV

    SusanV
    Expand Collapse
    Guest

    Hi Adam,

    Here's some air-code, I'd put the this in the after update of whatever
    control you are grabbing the associated data from, say the field is txtPFS
    that the user is filling out, and the one you want to autofill is
    txtAcctCode, it'd be something like this:

    Private Sub txtPFS_AfterUpdate()

    Dim strPFS as String

    strPFS = Me.txtPFS

    Select Case strPFS
    Case 1
    Me.txtAcctCode = "account code 1"
    Case 2
    Me.txtAcctCode = "account code 2"
    Case 3
    Me.txtAcctCode = "account code 3"
    EndSelect
    End Sub


    Or,if you're pulling the actual fill for txtAcctCode from another form it's
    a bit trickier, but can be done, reference the form like this:

    Forms!OtherFormName.ControlName

    So you end up with something like this:

    Private Sub txtPFS_AfterUpdate()

    Dim strPFS as String

    strPFS = Me.txtPFS

    Select Case strPFS
    Case 1
    Me.txtAcctCode = Forms!OtherFormName.ControlName1
    Case 2
    Me.txtAcctCode = Forms!OtherFormName.ControlName2
    Case 3
    Me.txtAcctCode = Forms!OtherFormName.ControlName3
    EndSelect
    End Sub

    Below are some links and more info on Select Case, best of luck!

    ;-)

    SusanV


    Sure, here's a couple of links:

    http://www.blueclaw-db.com/select_case.htm
    http://www.functionx.com/vbaccess/Lesson06b.htm


    Visual Basic Help is (I think) clearer:
    Select Case Statement
    Executes one of several groups of statements, depending on the value of an
    expression.

    Syntax

    Select Case testexpression
    [Case expressionlist-n
    [statements-n]] ...
    [Case Else
    [elsestatements]]

    End Select

    The Select Case statement syntax has these parts:

    Part Description
    testexpression Required. Any numeric expression or string expression.
    expressionlist-n Required if a Case appears. Delimited list of one or
    more of the following forms: expression, expression To expression, Is
    comparisonoperator expression. The To keyword specifies a range of values.
    If you use the To keyword, the smaller value must appear before To. Use the
    Is keyword with comparison operators (except Is and Like) to specify a range
    of values. If not supplied, the Is keyword is automatically inserted.
    statements-n Optional. One or more statements executed if
    testexpression matches any part of expressionlist-n.
    elsestatements Optional. One or more statements executed if
    testexpression doesn't match any of the Case clause.



    Remarks

    If testexpression matches any Case expressionlist expression, the statements
    following that Case clause are executed up to the next Case clause, or, for
    the last clause, up to End Select. Control then passes to the statement
    following End Select. If testexpression matches an expressionlist expression
    in more than one Case clause, only the statements following the first match
    are executed.

    The Case Else clause is used to indicate the elsestatements to be executed
    if no match is found between the testexpression and an expressionlist in any
    of the other Case selections. Although not required, it is a good idea to
    have a Case Else statement in your Select Case block to handle unforeseen
    testexpression values. If no Case expressionlist matches testexpression and
    there is no Case Else statement, execution continues at the statement
    following End Select.

    You can use multiple expressions or ranges in each Case clause. For example,
    the following line is valid:

    Case 1 To 4, 7 To 9, 11, 13, Is > MaxNumber
    Note The Is comparison operator is not the same as the Is keyword used in
    the Select Case statement.

    You also can specify ranges and multiple expressions for character strings.
    In the following example, Case matches strings that are exactly equal to
    everything, strings that fall between nuts and soup in alphabetic order, and
    the current value of TestItem:

    Case "everything", "nuts" To "soup", TestItem
    Select Case statements can be nested. Each nested Select Case statement must
    have a matching End Select statement.


    Select Case Statement Example
    This example uses the Select Case statement to evaluate the value of a
    variable. The second Case clause contains the value of the variable being
    evaluated, and therefore only the statement associated with it is executed.

    Dim Number
    Number = 8 ' Initialize variable.
    Select Case Number ' Evaluate Number.
    Case 1 To 5 ' Number between 1 and 5, inclusive.
    Debug.Print "Between 1 and 5"
    ' The following is the only Case clause that evaluates to True.
    Case 6, 7, 8 ' Number between 6 and 8.
    Debug.Print "Between 6 and 8"
    Case 9 To 10 ' Number is 9 or 10.
    Debug.Print "Greater than 8"
    Case Else ' Other values.
    Debug.Print "Not between 1 and 10"
    End Select


    "F1stman" <F1stman@discussions.microsoft.com> wrote in message
    news:F9486209-EC43-4E8F-85E1-AB454EBECBD5@microsoft.com...
    > Hello Susan,
    > Thank you for the quick response. Is there anywhere you know that I can
    > learn more about the case statement. I'm not a whiz when it comes to VBA.
    > There isn't much info in the assistance section of the Microsoft website
    > or
    > in this discussion group. Thanks, Adam
    >
    > "SusanV" wrote:
    >
    >> Hi F1stman,
    >>
    >> Personally I would use a Case statement, as it will be easier to extend
    >> in
    >> the future should you need to add PFS options:
    >>
    >> Select Case PFS
    >> Case 1
    >> 'what you want to happen
    >> Case 2
    >> 'what you want for PFS of 2
    >> 'etc etc
    >> End Select
    >>
    >>
    >> --
    >> hth,
    >> SusanV
    >>
    >>
    >> "F1stman" <F1stman@discussions.microsoft.com> wrote in message
    >> news:84842D5C-FE81-4FD4-885B-248AEB25375F@microsoft.com...
    >> > Hello All,
    >> > I am trying to create an if statement to select the correct account
    >> > code
    >> > on
    >> > one form and place that code onto the current form. Something like:
    >> >
    >> > If PFS=1 then set value to [Central Exhibit].[Account Code 1] or if If
    >> > PFS=2
    >> > then set value to [Central Exhibit].[Account Code 2] or If PFS=3 then
    >> > set
    >> > value to [Central Exhibit].[Account Code 3], etc.
    >> >
    >> > PFS is on the current form and Account code is obviously on another
    >> > form
    >> > which is open at the same time. There are six total possibilities. I
    >> > can
    >> > get
    >> > it to work with one IIf statement. After that I'm sunk. Thank you in
    >> > advance.
    >> > This group is sooo helpful and had taught me much. Adam
    >> >

    >>
    >>
    >>
     
  8. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    In addition to the suggestions you've already seen, you could use the Choose
    function. For your example, you'd use

    =Choose(PFS, [Central Exhibit].[Account Code 1], [Central Exhibit].[Account
    Code 2], [Central Exhibit].[Account Code 3], [Central Exhibit].[Account Code
    4], [Central Exhibit].[Account Code 5], [Central Exhibit].[Account Code 6])

    At the risk of seeming rude, your table sounds as though it could use proper
    normalization. Having 6 fields named [Account Code n] is usually a sign that
    you've got a repeating group. Jeff Conrad has a bunch of good references on
    this topic at
    http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "F1stman" <F1stman@discussions.microsoft.com> wrote in message
    news:84842D5C-FE81-4FD4-885B-248AEB25375F@microsoft.com...
    > Hello All,
    > I am trying to create an if statement to select the correct account code

    on
    > one form and place that code onto the current form. Something like:
    >
    > If PFS=1 then set value to [Central Exhibit].[Account Code 1] or if If

    PFS=2
    > then set value to [Central Exhibit].[Account Code 2] or If PFS=3 then set
    > value to [Central Exhibit].[Account Code 3], etc.
    >
    > PFS is on the current form and Account code is obviously on another form
    > which is open at the same time. There are six total possibilities. I can

    get
    > it to work with one IIf statement. After that I'm sunk. Thank you in

    advance.
    > This group is sooo helpful and had taught me much. Adam
    >
     

Share This Page