Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    San Diego
    Posts
    45

    Unanswered: Switch statement vs. IIf statement problems

    I am trying to get a certain date in a field within my query. It is giving me strange results with an IIf statement, so I tried converting it to a switch statement. It is saying I have a syntax error with the switch statement, and I just don't see it.

    Here is my SQL for the IIf statement. The fields for the "7"s and "6"s aren't showing up when I run this. Does anyone see why this would be? Am I asking too much of the IIf?
    Code:
    Decide: 
    IIf((Mid([qryParentData.wp],8,1))="2",
       [mtblMSTR]![SOC2START],
          IIf((Mid([qryParentData.wp],8,1))="3",
          IIf((Mid([qryParentData.wp],4,1))="5",
              [mtblMSTR]![SOC3START],[mtblMSTR]![SOC3OFFAB]),
            IIf((Mid([qryParentData.wp],8,1))="5",
                [mtblMSTR]![SOC5OFFAB],
             IIf((Mid([qryParentData.wp],8,1))="6",
             IIf([mtblMSTR]![ERECTION_S]="",
                 [qryGB_Mstr]![ERECTION_S],
              IIf((Mid([qryParentData.wp],8,1))="7",
                  [mtblMSTR]![SOC3START]))))))
    Now, here is the switch statement. I would rather use this, but it is not running:
    Code:
    Decide:           
    Switch( 
    Mid([qryParentData.wp],8,1)="2", 
    [mtblMSTR]![SOC2START], 
    
    Mid([qryParentData.wp],8,1)="3" 
      AND Mid([qryParentData.wp],4,1)="5", 
    [mtblMSTR]![SOC3START], 
    
    Mid([qryParentData.wp],8,1)="3", 
    [mtblMSTR]![SOC3OFFAB]), 
    
    Mid([qryParentData.wp],8,1)="5", 
    [mtblMSTR]![SOC5OFFAB], 
    
    Mid([qryParentData.wp],8,1)="6" 
      AND ([mtblMSTR]![ERECTION_S]="" 
      OR [mtblMSTR]![ERECTION_S] Is Null), 
    [qryGB_Mstr]![ERECTION_S],
    
    Mid([qryParentData.wp],8,1)="6", 
    [mtblMSTR]![ERECTION_S],
    
    Mid([qryParentData.wp],8,1)="7", 
    [mtblMSTR]![SOC3START]
    )




    Does anybody see why this isn't working?
    Any comments and help are much appreciated. Thanks!

    LL

  2. #2
    Join Date
    Jan 2004
    Posts
    24

    My god that's spaghetti

    The main reason why you can't find the problem is because the code is too complex.

    Break it down, simplify it as much as possible, even if it makes 10x the number of lines.

    The bottom line is, when you can't find the syntax errors in your code, you need to rethink how you're doing it.

  3. #3
    Join Date
    Mar 2004
    Location
    San Diego
    Posts
    45

    Red face

    Quote Originally Posted by mrwiggley23
    The main reason why you can't find the problem is because the code is too complex..
    The sad part is that this IS simplified. The data I am working with is a mess, and I have to somehow make it clear in a huge report. Yuck. I have almost everything working, but this just won't budge. I will continue trying to break it down. Thanks for your quick reply!

    Spaghetti code indeed---

  4. #4
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    Code:
    Switch
    (
    Month([dob])="2",
    "Two",
    
    Month([DOB])="3" And Mid([Membership ID],11,1)="1",
    "Principal Three",
    
    Month([DOB])="3",
    "Three",
    
    Month([DOB])="5",
    "Five",
    
    Month([DOB])="6" And Mid([Membership ID],11,1)="1" Or Mid([Membership_ID],11,1)="2",
    "Secondary Six",
    
    Month([DOB])="6",
    "Six",
    
    Month([DOB])="7",
    "Seven"
    )
    I converted your code so that I could use it, and the above sample worked fine. I did notice that when I first entered it, access put [] around switch, and then said it was an invalid function, but if you delete these, it works fine.

  5. #5
    Join Date
    Jan 2004
    Posts
    24

    Try...

    Using multiple append table queries to build a table that's based on the original data. You can use an append query in place of each of the switch blocks, and each query can do whatever transformations on the data that are necessary, and then base your report on the data in the new table.

    For data that's messy like that, that's about all you can do. Also, if you're going to be working on this database/report long term it might pay off to spend the extra time redoing the orignal tables and forms to force the data to be cleaner.

    Quote Originally Posted by LaurelLee
    The sad part is that this IS simplified. The data I am working with is a mess, and I have to somehow make it clear in a huge report. Yuck. I have almost everything working, but this just won't budge. I will continue trying to break it down. Thanks for your quick reply!

    Spaghetti code indeed---

  6. #6
    Join Date
    Mar 2004
    Location
    San Diego
    Posts
    45

    Talking

    Well, I got the switch working. I was going the route of appending, but I think that the switch is going to do the trick for now. Of course, the bossman just threw in a couple more criteria, so that may blow my whole switch right out of the water.

    Ahh, yes...such is life!!

    Thanks you both for your responses. Happy querying!

Posting Permissions

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