Welcome to SPN

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

Sign Up Now!

iif based on first char ofmthe field vale

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

  1. Rony

    Rony
    Expand Collapse
    Guest

    Hi
    In my table structure i have field "CPARTY" Text field
    and the values are 12367867
    23467898

    Based on the First Character of the value I want to classify it as a Bank
    or a customer, something like this

    IIF (CPARTY,char(1))=1, banks
    IIF (CPARTY,char(1))=2,customers

    How can i contruct iif statement.

    Thanks
    -
    Ron
     
  2. Loading...


  3. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Rony,

    Try this:

    Customer Type: IIF(Left$([CPARTY], 1) = "1", "banks", "customers")


    Tom Wickerath
    Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "Rony" wrote:

    > Hi
    > In my table structure i have field "CPARTY" Text field
    > and the values are 12367867
    > 23467898
    >
    > Based on the First Character of the value I want to classify it as a Bank
    > or a customer, something like this
    >
    > IIF (CPARTY,char(1))=1, banks
    > IIF (CPARTY,char(1))=2,customers
    >
    > How can i contruct iif statement.
    >
    > Thanks
    > -
    > Ron
     
  4. Kernow Girl

    Kernow Girl
    Expand Collapse
    Guest

    Hi Rony -
    It would go ----

    IIF(left([cparty] = 1 ,"Banks, IIf(left([cparty]=2,"Customers "," " ))

    Structure is ==== IIf( test, true, false) You can 'embed' IIF statements
    in IIF statements (can't remeber how many, look in the HELP screens.) In
    yours, the FALSE is the next IIF.

    Hope this is what you need ---

    Yours - Dika

    "Rony" wrote:

    > Hi
    > In my table structure i have field "CPARTY" Text field
    > and the values are 12367867
    > 23467898
    >
    > Based on the First Character of the value I want to classify it as a Bank
    > or a customer, something like this
    >
    > IIF (CPARTY,char(1))=1, banks
    > IIF (CPARTY,char(1))=2,customers
    >
    > How can i contruct iif statement.
    >
    > Thanks
    > -
    > Ron
     
  5. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Dika,

    I believe this would be more correct for a nested IIF statement:

    Customer Type:
    IIf(Left([CPARTY],1)="1","banks",IIf(Left([CPARTY],1)="2","customers","unknown"))

    Note: This version uses the Left function instead of Left$ that I used in my
    first example, just in case the CPARTY field is null.

    You can use up to 10 levels of nesting with IIF, but I certainly do not
    recommend ever attempting to do so. The resulting field expression will be
    such a mess that it will be very difficult to troubleshoot in the future.
    When you exceed about three possibilities, such as "banks", "customers" and
    "unknown", it is time to write a custom function that includes a SELECT CASE
    ..... END SELECT. This is *much* easier to maintain.


    Tom Wickerath
    Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________


    "Kernow Girl" wrote:

    > Hi Rony -
    > It would go ----
    >
    > IIF(left([cparty] = 1 ,"Banks, IIf(left([cparty]=2,"Customers "," " ))
    >
    > Structure is ==== IIf( test, true, false) You can 'embed' IIF statements
    > in IIF statements (can't remeber how many, look in the HELP screens.) In
    > yours, the FALSE is the next IIF.
    >
    > Hope this is what you need ---
    >
    > Yours - Dika
    >
    > "Rony" wrote:
    >
    > > Hi
    > > In my table structure i have field "CPARTY" Text field
    > > and the values are 12367867
    > > 23467898
    > >
    > > Based on the First Character of the value I want to classify it as a Bank
    > > or a customer, something like this
    > >
    > > IIF (CPARTY,char(1))=1, banks
    > > IIF (CPARTY,char(1))=2,customers
    > >
    > > How can i contruct iif statement.
    > >
    > > Thanks
    > > -
    > > Ron
     
  6. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Tom Wickerath wrote:
    > When you exceed about three possibilities, such as "banks", "customers" and
    > "unknown", it is time to write a custom function that includes a SELECT CASE
    > .... END SELECT. This is *much* easier to maintain.


    Agreed. Another idea to consider is the Switch() function.

    --
    Steve Schapel, Microsoft Access MVP
     
  7. Kernow Girl

    Kernow Girl
    Expand Collapse
    Guest

    Hi Tom - yes, you're absolutely correct - sorry for the Monday morning lapse!
    I also agree with not nesting too many levels and I realise that CASE is
    certainly easier and cleaner, but keep in mind some of us don't have the time
    to do what you can. You can so easily say 'it's time to write a custom
    function', or you can even send it to a user, but not everyone has the
    knowledge and/or the time to use the knowledge and get it to work. I read
    some of the answers to very simple questions and sometimes wonder what the
    asker made of them. Ah well - 1 more month and I'm out done. Ta - Dika

    "Tom Wickerath" wrote:

    > Hi Dika,
    >
    > I believe this would be more correct for a nested IIF statement:
    >
    > Customer Type:
    > IIf(Left([CPARTY],1)="1","banks",IIf(Left([CPARTY],1)="2","customers","unknown"))
    >
    > Note: This version uses the Left function instead of Left$ that I used in my
    > first example, just in case the CPARTY field is null.
    >
    > You can use up to 10 levels of nesting with IIF, but I certainly do not
    > recommend ever attempting to do so. The resulting field expression will be
    > such a mess that it will be very difficult to troubleshoot in the future.
    > When you exceed about three possibilities, such as "banks", "customers" and
    > "unknown", it is time to write a custom function that includes a SELECT CASE
    > .... END SELECT. This is *much* easier to maintain.
    >
    >
    > Tom Wickerath
    > Microsoft Access MVP
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > http://www.access.qbuilt.com/html/search.html
    > __________________________________________
    >
    >
    > "Kernow Girl" wrote:
    >
    > > Hi Rony -
    > > It would go ----
    > >
    > > IIF(left([cparty] = 1 ,"Banks, IIf(left([cparty]=2,"Customers "," " ))
    > >
    > > Structure is ==== IIf( test, true, false) You can 'embed' IIF statements
    > > in IIF statements (can't remeber how many, look in the HELP screens.) In
    > > yours, the FALSE is the next IIF.
    > >
    > > Hope this is what you need ---
    > >
    > > Yours - Dika
    > >
    > > "Rony" wrote:
    > >
    > > > Hi
    > > > In my table structure i have field "CPARTY" Text field
    > > > and the values are 12367867
    > > > 23467898
    > > >
    > > > Based on the First Character of the value I want to classify it as a Bank
    > > > or a customer, something like this
    > > >
    > > > IIF (CPARTY,char(1))=1, banks
    > > > IIF (CPARTY,char(1))=2,customers
    > > >
    > > > How can i contruct iif statement.
    > > >
    > > > Thanks
    > > > -
    > > > Ron
     
  8. Rony

    Rony
    Expand Collapse
    Guest

    Thanks all of you, one more query
    I have another field in the same table [tnum] text field, data underneath
    0
    1
    FW

    I NEED TO CHECK TWO CONDITIONS

    IIF (CPARTY,char(1))=1,AND [TNUM]0 OR 1 "BANKSPOT"
    IIF (CPARTY,char(1))=1,AND [TNUM]FW "BANKFORWARD"


    IIF (CPARTY,char(1))=2,AND [TNUM]0 OR 1 "CUSTOMERSPOT"
    IIF (CPARTY,char(1))=2,AND [TNUM]FW "CUSTOMERFORWARD"

    PLEASE GUIDE ME

    THANKS IN ADVANCE

    RONY

    --
    Ron


    "Steve Schapel" wrote:

    >
    > Tom Wickerath wrote:
    > > When you exceed about three possibilities, such as "banks", "customers" and
    > > "unknown", it is time to write a custom function that includes a SELECT CASE
    > > .... END SELECT. This is *much* easier to maintain.

    >
    > Agreed. Another idea to consider is the Switch() function.
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
     
  9. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Ron,

    I think, at this point, that it would be easier if you can settle for the
    word "SPOT" or "FORWARD", based on the first character of your TNUM field. Do
    this in a manner similar to your CPARTY field, using the Left function.

    If you really want the result to be based on the values of both fields, so
    that you get one of four possible return values, then it's time to write a
    custom VBA procedure. Are you comfortable attempting to do this? I can write
    up a quick "how-to" later on tonight, but not right now, since I need to get
    to work.


    Tom Wickerath
    Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "Rony" wrote:

    > Thanks all of you, one more query
    > I have another field in the same table [tnum] text field, data underneath
    > 0
    > 1
    > FW
    >
    > I NEED TO CHECK TWO CONDITIONS
    >
    > IIF (CPARTY,char(1))=1,AND [TNUM]0 OR 1 "BANKSPOT"
    > IIF (CPARTY,char(1))=1,AND [TNUM]FW "BANKFORWARD"
    >
    >
    > IIF (CPARTY,char(1))=2,AND [TNUM]0 OR 1 "CUSTOMERSPOT"
    > IIF (CPARTY,char(1))=2,AND [TNUM]FW "CUSTOMERFORWARD"
    >
    > PLEASE GUIDE ME
    >
    > THANKS IN ADVANCE
    >
    > RONY
    >
    > --
    > Ron
     
  10. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Dika,

    > but not everyone has the knowledge and/or the time to
    > use the knowledge and get it to work.


    I understand that, however, if they are willing to invest the time, then we
    can guide them through the process. It will likely take the person much less
    time versus attempting to debug some nasty nested IIF statement with five or
    more levels of nesting.

    > Ah well - 1 more month and I'm out done.

    ??


    Tom Wickerath
    Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "Kernow Girl" wrote:

    > Hi Tom - yes, you're absolutely correct - sorry for the Monday morning lapse!
    > I also agree with not nesting too many levels and I realise that CASE is
    > certainly easier and cleaner, but keep in mind some of us don't have the time
    > to do what you can. You can so easily say 'it's time to write a custom
    > function', or you can even send it to a user, but not everyone has the
    > knowledge and/or the time to use the knowledge and get it to work. I read
    > some of the answers to very simple questions and sometimes wonder what the
    > asker made of them. Ah well - 1 more month and I'm out done. Ta - Dika
     
  11. Rony

    Rony
    Expand Collapse
    Guest

    Hi Tom
    it is very nice of to guide me via VB, thanks a lot.
    Actuall i get the data download from a live system and i trying to classify
    through query and no input is involved . Any how when u find time if you can
    guide me through VB it can help me to incorporate the VB proc in some other
    programme.

    Thanks

    --
    Ron


    "Tom Wickerath" wrote:

    > Hi Ron,
    >
    > I think, at this point, that it would be easier if you can settle for the
    > word "SPOT" or "FORWARD", based on the first character of your TNUM field. Do
    > this in a manner similar to your CPARTY field, using the Left function.
    >
    > If you really want the result to be based on the values of both fields, so
    > that you get one of four possible return values, then it's time to write a
    > custom VBA procedure. Are you comfortable attempting to do this? I can write
    > up a quick "how-to" later on tonight, but not right now, since I need to get
    > to work.
    >
    >
    > Tom Wickerath
    > Microsoft Access MVP
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > http://www.access.qbuilt.com/html/search.html
    > __________________________________________
    >
    > "Rony" wrote:
    >
    > > Thanks all of you, one more query
    > > I have another field in the same table [tnum] text field, data underneath
    > > 0
    > > 1
    > > FW
    > >
    > > I NEED TO CHECK TWO CONDITIONS
    > >
    > > IIF (CPARTY,char(1))=1,AND [TNUM]0 OR 1 "BANKSPOT"
    > > IIF (CPARTY,char(1))=1,AND [TNUM]FW "BANKFORWARD"
    > >
    > >
    > > IIF (CPARTY,char(1))=2,AND [TNUM]0 OR 1 "CUSTOMERSPOT"
    > > IIF (CPARTY,char(1))=2,AND [TNUM]FW "CUSTOMERFORWARD"
    > >
    > > PLEASE GUIDE ME
    > >
    > > THANKS IN ADVANCE
    > >
    > > RONY
    > >
    > > --
    > > Ron
     
  12. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Ron,

    Okay, start by creating a new module in your database. Click on the Modules
    tab, and then click on the New button. You should see a new code module
    opened up, with two lines of code:

    Option Compare Database
    Option Explicit

    If you do not see Option Explicit, then add this line of code manually. Then
    click on Tools > Options while in the VBA Editor and place a check in
    "Require Variable Declaration", so that you will get these two *very
    important* words inserted into all new modules. For more information, please
    see this link:

    Always Use Option Explicit
    http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

    Now copy and paste the following function shown below into your new module.
    Save the module as basDetermineCustType. Then click on Debug > Compile
    ProjectName. Hopefully, you will not get any compile errors (you may uncover
    compile errors in other code modules, however, if you are not compiling any
    new/edited code regularly). Add the following to the Field row of your
    query, to call your new function:

    Customer Type: DetermineCustType([CPARTY],[TNUM])

    Here is the function. Note that the URL shown as a reference will be
    wrapped. You'll need to make this URL on one line:

    '**************Begin Code**************************

    ' Written just for Ron by Tom Wickerath, 7/24/2006
    ' http://www.microsoft.com/office/community/en-us/default.mspx?
    dg=microsoft.public.access&mid=99251f5c-3aa4-4481-b345-d017b4d29cff

    Function DetermineCustType _
    (CPARTY As Variant, TNUM As Variant) As String

    If IsNull(CPARTY + TNUM) Then
    DetermineCustType = "Unknown"
    Exit Function
    End If

    Select Case Left$(CPARTY, 1)
    Case "1"
    Select Case (TNUM)
    Case "0", "1"
    DetermineCustType = "BANKSPOT"
    Case "FW"
    DetermineCustType = "BANKFORWARD"
    Case Else
    DetermineCustType = "Unknown"
    End Select

    Case "2"
    Select Case (TNUM)
    Case "0", "1"
    DetermineCustType = "CUSTOMERSPOT"
    Case "FW"
    DetermineCustType = "CUSTOMERFORWARD"
    Case Else
    DetermineCustType = "Unknown"
    End Select

    Case Else

    DetermineCustType = "Unknown"

    End Select


    End Function

    '**************End Code**************************


    Tom Wickerath
    Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "Rony" wrote:

    > Hi Tom
    > it is very nice of to guide me via VB, thanks a lot.
    > Actuall i get the data download from a live system and i trying to classify
    > through query and no input is involved . Any how when u find time if you can
    > guide me through VB it can help me to incorporate the VB proc in some other
    > programme.
    >
    > Thanks
    >
    > --
    > Ron
     
  13. Rony

    Rony
    Expand Collapse
    Guest

    Hi Tom
    Thanks for your time. Something new lesson to me.
    --
    Ron


    "Tom Wickerath" wrote:

    > Hi Ron,
    >
    > Okay, start by creating a new module in your database. Click on the Modules
    > tab, and then click on the New button. You should see a new code module
    > opened up, with two lines of code:
    >
    > Option Compare Database
    > Option Explicit
    >
    > If you do not see Option Explicit, then add this line of code manually. Then
    > click on Tools > Options while in the VBA Editor and place a check in
    > "Require Variable Declaration", so that you will get these two *very
    > important* words inserted into all new modules. For more information, please
    > see this link:
    >
    > Always Use Option Explicit
    > http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions
    >
    > Now copy and paste the following function shown below into your new module.
    > Save the module as basDetermineCustType. Then click on Debug > Compile
    > ProjectName. Hopefully, you will not get any compile errors (you may uncover
    > compile errors in other code modules, however, if you are not compiling any
    > new/edited code regularly). Add the following to the Field row of your
    > query, to call your new function:
    >
    > Customer Type: DetermineCustType([CPARTY],[TNUM])
    >
    > Here is the function. Note that the URL shown as a reference will be
    > wrapped. You'll need to make this URL on one line:
    >
    > '**************Begin Code**************************
    >
    > ' Written just for Ron by Tom Wickerath, 7/24/2006
    > ' http://www.microsoft.com/office/community/en-us/default.mspx?
    > dg=microsoft.public.access&mid=99251f5c-3aa4-4481-b345-d017b4d29cff
    >
    > Function DetermineCustType _
    > (CPARTY As Variant, TNUM As Variant) As String
    >
    > If IsNull(CPARTY + TNUM) Then
    > DetermineCustType = "Unknown"
    > Exit Function
    > End If
    >
    > Select Case Left$(CPARTY, 1)
    > Case "1"
    > Select Case (TNUM)
    > Case "0", "1"
    > DetermineCustType = "BANKSPOT"
    > Case "FW"
    > DetermineCustType = "BANKFORWARD"
    > Case Else
    > DetermineCustType = "Unknown"
    > End Select
    >
    > Case "2"
    > Select Case (TNUM)
    > Case "0", "1"
    > DetermineCustType = "CUSTOMERSPOT"
    > Case "FW"
    > DetermineCustType = "CUSTOMERFORWARD"
    > Case Else
    > DetermineCustType = "Unknown"
    > End Select
    >
    > Case Else
    >
    > DetermineCustType = "Unknown"
    >
    > End Select
    >
    >
    > End Function
    >
    > '**************End Code**************************
    >
    >
    > Tom Wickerath
    > Microsoft Access MVP
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > http://www.access.qbuilt.com/html/search.html
    > __________________________________________
    >
    > "Rony" wrote:
    >
    > > Hi Tom
    > > it is very nice of to guide me via VB, thanks a lot.
    > > Actuall i get the data download from a live system and i trying to classify
    > > through query and no input is involved . Any how when u find time if you can
    > > guide me through VB it can help me to incorporate the VB proc in some other
    > > programme.
    > >
    > > Thanks
    > >
    > > --
    > > Ron
     

Share This Page