Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Mar 2008
    Posts
    3

    Unanswered: How can I insert an IF statement here?

    Hello,
    As an Access Noob, I was wondering if anyone could help me in a small problem...

    I have a query which looks up the second character of a "program code" - which looks like this: RACA005

    In design view, it is done like so: ChannelType: Mid([programcode],2,1)

    Problem is... I now have these two new program codes not following the same logic. I need to match all three characters like so: SVEG048. These will always be either "VEG", or "VEH" ...

    What can I do to include this additional requirement? I am thinking of adding an IF statement - IF you see "V", then include the next two characters to create the ChannelType...

    I would really appreciate any help on this!!!

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    iif if and only if

    iif(check,true,false)

    iif(check,iif(check,true,false),iif(check,true,fal se)) and so on.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You don't need any help.

    Quote Originally Posted by bluepenguin
    I am thinking of adding an IF statement - IF you see "V", then include the next two characters to create the ChannelType
    Looks spot on to me
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Mar 2008
    Posts
    3
    Quote Originally Posted by StarTrekker
    You don't need any help.


    Looks spot on to me
    Yikes, I need to tell you though, I'm a total programming dummy. What syntax would I use?

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Myle gave you the syntax you needed, but I must admit I had to read it twice to get his point.

    Here ya go... try:

    ChannelType: IIf(Mid([programcode],2,1)="V",Mid([programcode],2,3),Mid([programcode],2,1))

    Cheers

    ST
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id think very carefully about this.. it may make more sense to dump this code to another approach.. the reason.. you had a single character loookup, you've now got 3 cases, at this rate some bright spark may well attempt to make more cases.

    If be minded to put this into a function.. that way round you willl no longer be using the IIF statement, which can be a bit of a pain to debug, if you have more than say 2 or 3 statements

    if you need this channel code anywhere else you only change the function code once.. it can become a significant maintenance task if you embed the same code in multpile queries / forms / reports. if its in a function then you just modify it the once (each time).. test the function and jobs a goodun.

    but if you only have the 2 conditions (ie channel type is either A or Vxx) then the IIf is a perfectly sensible approach (for now)

    just my tuppeny haporth

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed with that. It is better to use a function now, but it's not too late to do that afterwards, it's just harder/more work.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Or use a table to store the data
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Dec 2007
    Posts
    70
    Quote Originally Posted by pootle flump
    It is safest to assume that everything above this line is founded on ignorance compounded by assumptions mixed in with a cavalier disregard of the basic tenants of best practice
    Sorry to completely change the subject! But that is the best sentence I've ever had the privilage of reading!!!!!!

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by OB1
    Sorry to completely change the subject! But that is the best sentence I've ever had the privilage of reading!!!!!!

    purlease................ dont encourage him

    its like the signs you see at the zoo.. "dont feed the animals", well round here its "don't encourage the pootleflump"

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thank you OB1 - that is very kind

    And don't listen to that nasty Healdem. It is his constant put downs and criticisms that have made me the shy & insecure wreck you see before you today
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Mar 2008
    Posts
    3
    Thanks so much guys!!! I will try that IF statement. Believe me, I know of the risk if we had more of these program codes pop up, however, I was assured this case is an "exception".

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by bluepenguin
    I will try that IF statement. Believe me, I know of the risk if we had more of these program codes pop up, however, I was assured this case is an "exception".
    He he - sounds like you are experienced enough to pop quotes around that but not yet sufficiently jaded to assume it will defo not be an exception!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    poots
    should i take offense at cavalier disregard of the basic tenants of best practice in your signature?

    izy (izyrider being the name of one of my late lamented hounds - a Cavalier King Charles spaniel determinedly devoted to Not(best practice) in dogistics)
    currently using SS 2008R2

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Izy - I hope not. The only people on this forum that I actively try my hardest to offend are George and Mark. Any other offense caused should be considered purely accidental and swiftly followed by my sincerest apologies
    <sincere smile></ sincere smile>
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •