Results 1 to 5 of 5
  1. #1
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142

    Problem Coding this IIF statement

    I have 2 fields. Cal them "test" and "test2"



    Test contains 1 of 3 possible values "CL+", "CL", or "H"

    Test2 contains 1 of 3 possible values "FR+", "FR", or "H"

    I have a new field that I was to contain "CL+", "CL", "FR+", "FR", or "H" depending on the values in test and test2.

    This is what I tried.

    ChartLit: IIf([test]<>“H”,[test],IIf([test2]<>“H”,[test2],”H”))


    The problem is that every time I try to save this query it changes the code to what you see below. It doesn't seem to recognize that the "H" is a literal value in those fields. It's tries to turn into some kind of variable value.

    ChartLit: IIf([test]<>[“H”],[test],IIf([test2]<>[“H”],[test2],[”H”]))



    As part of trying to understand what's going on I tried this. It works fine.

    ChartLit: IIf([test]="H","H",[test])


    Then I tried this, but it I had the same problem as above

    ChartLit: IIf([test]<>”H”,[test],”H”)

    It changes it to:

    ChartLit: IIf([test]<>[”H”],[test],[”H”])


    What am I doing wrong? Could it be a field definition issue? Other?

    Is there a better way to do this?

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    What about a function

    Function category(mystr1 as string, mystr2 as string) as string

    Select case mystr1
    Case = "h"

    If mystr2 = "h" then category= "h"
    Else: category = mystr2
    Endif

    Case else

    Category = mystr1

    End select

    End function

    If this is in a module you can use it in a query using

    Nameyouwant: category (test,test2)



    Sent from my iPhone using Tapatalk

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    ChartLit: IIf([test]<>'H',[test],IIf([test2]<>'H',[test2],'H')) in query design view should work (or at least not mess up your expression).
    But how did you generate the characters that look like double quotes (but are not)?
    Because this also works:
    SELECT IIf([test]<>"H",[test],IIf([test2]<>"H",[test2],"H")) as ChartLit from tblTests;
    but this does not
    IIf([test]<>“H”,[test],IIf([test2]<>“H”,[test2],”H”))

    You've got some leaning left/right around the first 2 H's but on the last one lean they lean the same way. The are ascii 147 and 148, not 34
    Last edited by Micron; 03-19-2017 at 08:10 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    Quote Originally Posted by Micron View Post
    ChartLit: IIf([test]<>'H',[test],IIf([test2]<>'H',[test2],'H')) in query design view should work (or at least not mess up your expression).
    But how did you generate the characters that look like double quotes (but are not)?
    Because this also works:
    SELECT IIf([test]<>"H",[test],IIf([test2]<>"H",[test2],"H")) as ChartLit from tblTests;
    but this does not
    IIf([test]<>“H”,[test],IIf([test2]<>“H”,[test2],”H”))

    You've got some leaning left/right around the first 2 H's but on the last one lean they lean the same way. The are ascii 147 and 148, not 34
    Wow, that whole left leaning and right leaning double quote thing is a mystery to me. I have no idea how that could have happened. I used the same key on the same keyboard, but somehow Access must have kept changing something when I tried to save the Query from design mode.

    Your first solution worked fine. It's also neater. Thanks a lot. I'll keep that left/right quote issue in mind also for the future.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't think Access would do that so much as Windows or you yourself. I sometimes inadvertently swap my keyboard to French (something to do with missing the left shift key and hitting ctl or maybe both) and most of the typing is OK until I get to certain characters.
    Good luck!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Coding problem with Exit button
    By NightWalker in forum Programming
    Replies: 11
    Last Post: 06-06-2016, 01:05 PM
  2. Replies: 11
    Last Post: 04-29-2015, 01:38 PM
  3. Coding problem.
    By cheyanne in forum Forms
    Replies: 3
    Last Post: 05-19-2012, 05:57 AM
  4. IF/THEN statement problem
    By sfgiantsdude in forum Access
    Replies: 5
    Last Post: 01-06-2012, 03:50 PM
  5. Problem With IF Statement
    By MuskokaMad in forum Programming
    Replies: 0
    Last Post: 03-14-2010, 05:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums