| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 125529 | 2012-07-04 04:02:00 | MS Access IIF Statement Conditions | WarNox (8772) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 1285877 | 2012-07-09 10:43:00 | Cool, I'm happy with that :) Yep, mine does the '<> False' this as well. I'm using 2010. Thanks for all the help!!!! |
WarNox (8772) | ||
| 1285878 | 2012-07-10 01:13:00 | I must confess that I haven't read all the posts and sample code in detail, so may well have missed something, but I'm a big fan of KISS. Why not just have two select statements which you choose with a simple IF: (pseudocode follows) IF (checkbox ticked) then SELECT based on the between dates criteria else SELECT to select everything ENDIF This seems far more understandable than a long complex IIF. (Got my ex program maintenance person hat on here :)) Feel free to tell me I've totally misunderstood |
Tony (4941) | ||
| 1285879 | 2012-07-10 02:57:00 | Hi Tony, Yes, I think that would work too but got it functioning the other way so I'll stick with that for now. Cheers for the input. |
WarNox (8772) | ||
| 1285880 | 2012-07-10 02:59:00 | no worries - I don't have to maintain it! :D | Tony (4941) | ||
| 1285881 | 2012-07-10 07:51:00 | Just a few comments in support of Tony. There's a performance hit on Immediate If. On a large Recordset it's quite noticeable. For what it's worth I never use IIF. Never. As well as that, each time you compact your database the Jet Engine will optimize saved queries for performance. If the QueryDef contains an IIF statement, it won't be optimized. (That won't matter after (I think) 2014, when Microsoft will (probably) drop the Jet Engine.) As you noticed, Access changes (that kind of) SQL when you display it in the Query Grid. I don't like that. That won't happen with two separate QueryDefs, and as Tony implies, it's almost always worth a little time now to save a lot of time later. Anyway, I'm still backing slowly away... :-) |
MistyCat (11583) | ||
| 1285882 | 2012-07-10 10:56:00 | Thanks MistyCat, Good to see I'm not alone... Although you are of course quite correct about the performance issues, I would suspect that unless we are talking about really big datasets the efficiency loss would hardly be noticeable - but once again feel free to tell me I'm mistaken, as I've never really done any measurements. I was looking at it purely from an understandability/maintainability viewpoint. |
Tony (4941) | ||
| 1285883 | 2012-07-10 11:31:00 | But wouldn't there be an IIF statement either way? | WarNox (8772) | ||
| 1285884 | 2012-07-10 12:19:00 | The IIF currently sits in your query. Instead, in the code behind your Form, you can use IF. If you're opening your original query from a button on your form, then just create and save one query for each condition,then put something like this in the button's code. Dim qryName as String If chkDates = True qryName = "NameOfQueryWithThe...Between...thing" Else qryName = "NameOfQueryWithAllRecords" EndIf DoCmd.OpenQuery qryName, acNormal, acEdit I'd guess that the button-creating Wizard in V 2010 would step you through that. In my 2003 version, I'd: 1. Create a Command Button from the Wizard. 2. Select Miscellaneous from Categories. 3. Select Run Query from Actions. 4. Choose either of your new queries from "Choose which one..." 5. Let Access create the code and error-handling. 6. Edit the code as above. I've probably left something out. :-) If you need more control over the display, just create a Form from either of the queries, then in your opening code just switch the RecordSource of the form to whichever query you want. |
MistyCat (11583) | ||
| 1285885 | 2012-07-11 00:20:00 | Oh I see what you mean. Might have a play with that but was trying to stay away from VB :) | WarNox (8772) | ||
| 1285886 | 2012-07-11 00:24:00 | Oh I see what you mean. Might have a play with that but was trying to stay away from VB :)I wondered if that was what was going on. | Tony (4941) | ||
| 1 2 3 | |||||