PDA

View Full Version : MS Access IIF Statement Conditions



WarNox
04-07-2012, 04:02 PM
Hi all,

I'm having a play around in Access 2010 and want a query to run based on a few parameters specified in a form. Basically if a check box is ticked it should use two text boxes for the date range otherwise show all values. Everything seems to be working except the 'between' statement as a condition of the IIF statement.


SELECT...WHERE (((tbl_Main.Date)=IIf([Forms]![frmTest]![chkDates]=True,Between [Forms]![frmTest]![txtStart] And [Forms]![frmTest]![txtEnd],Date)));

I've tried putting the 'between' statement in [], "", '', `` but nothing works. Is this not possible the way I'm trying to do it or am I missing something? The between statement on it's own (without IIF) works fine.

Thanks for any help.

donread
04-07-2012, 04:18 PM
It'll be something like
WHERE (((tbl_Main.Date>=[Forms]![frmTest]![txtStart]![txtFrom] And (tbl_Main.Date)[Forms]![frmTest]![txtEnd]))

WarNox
04-07-2012, 04:32 PM
But I need that IF statement to check the check box...

Paul.Cov
04-07-2012, 09:59 PM
The way I'm interpreting it, you've got an issue after the first comma in your IIF.

IIF goes like this: IIF [condition which equates to true or false],[Do True part],[Do false part]

To the best of my experience, IIF is used for output only of conditional text. You seem to be using it as a switch to run a command that should be in a macro.

If I've not quite understood things, then maybe nested IIFs will do the trick, something like:

SELECT...WHERE (((tbl_Main.Date)=IIf([Forms]![frmTest]![chkDates]=True, IIF( [tbl_Main.Date] Between ([Forms]![frmTest]![txtStart] And [Forms]![frmTest]![txtEnd]),{nested true action},"")))

Also spotted that your Between isn't using () to enclose the conditions, which might also be the weak point.

WarNox
04-07-2012, 11:07 PM
Hi Paul,

Yes, you have understood things correctly. I guess you can't run a function (eg Between) as a condition of an IIF statement so I don't think a nested IIF statement would make any difference.

I think I have to look more into this :)

WarNox
05-07-2012, 02:03 PM
I did some looking around and I'm sure this has to be possible. I tried putting in brackets etc but keep getting a syntax error. If I put the whole BETWEEN statement in "" it stops the syntax error but an expression error comes up.

When I click 'ok' on the error below the BETWEEN word gets highlighted.

3972

Paul.Cov
05-07-2012, 10:08 PM
Yeah, I checked on the Between statement after I'd replied, and (to my surprise) it doesn't use brackets. It treats the fields as alpha-numeric, so it shouldn't matter with numeric values whether they're inside quotes or not. In fact, better without quotes or you might get problems with months 10,11,12 being evaluated as earlier than months 2,3,4...

Since you're building all this into a query, why not use extra condition lines in the query to encode your Between requirements?
I can't talk you through this with a Select... style of quesry, but if you go into the GUI style of query design it'll be in a state that's more familiar to my use, and to my mind, easier to debug. The query builder can automatically interchange between SQL and GUI query styles.

Your Between statement as you are using it will only give an output of either True (1) or False (0). It isn't going to return the values that are in the frmTest form.
Consequently the only output from your IIF wil be either 1 or 0 if the original condition is True, or the date if it's false.
If Date isn't a variable, then you may want to be using Date() for todays date.

WarNox
06-07-2012, 10:25 AM
Sorry, I' not sure how to make change this to make it fit into multiple condition lines?

3975

Date is a variable so that part works fine.
The Between command does get the information from frmTest if run by itself, but not in the IIF statement.

MistyCat
06-07-2012, 03:10 PM
You may have to create a function to get what you want. I've just done a quick test on the sample NorthWind database, using the ID field.
Firstly I said, "Show me numbers 3,4,5,6."


SELECT Employees.EmployeeID
FROM Employees
WHERE (((Employees.EmployeeID) Between 3 And 6));

That returned the expected four rows.

Then I said "If my condition is False, show me 3,4,5,6, otherwise show row 8."


SELECT Employees.EmployeeID
FROM Employees
WHERE (((Employees.EmployeeID)=IIf(False,(Employees.Empl oyeeID) Between 3 And 6,8)));

That returned the single row 8, as expected.

I then changed the condition from False to True, expecting 4 rows.


SELECT Employees.EmployeeID
FROM Employees
WHERE (((Employees.EmployeeID)=IIf(True,(Employees.Emplo yeeID) Between 3 And 6,8)));

Bugger. No rows.

Conclusion (pending research): Access doesn't like getting a "between" parameter from an Immediate If statement.

I'm going to ask some of my fellow Access-obsessed nuts for a definitive "No, you can't get there from here." answer, and I'll follow up here with more info.

Unless I get drunk and forget.

donread
06-07-2012, 07:42 PM
Again,it'll be something like
=IIf([Forms]![frmTest]![chkDates]=True,(tbl_Main.Date>=[Forms]![frmTest]![txtStart]![txtFrom] And (tbl_Main.Date)<=[Forms]![frmTest]![txtEnd],otherwise whatever)
To my knowledge there is no 'Between' function

MistyCat
06-07-2012, 08:03 PM
donread, look at the first bit of SQL I posted, where I say that...

(Employees.EmployeeID) Between 3 And 6

... returned the expected four rows.

This page...
http://office.microsoft.com/en-us/access-help/between-and-operator-HP001032212.aspx
... shows an example in Office 2003

This page...
http://www.functionx.com/access/Lesson28.htm
... shows an example in Office 2010

This SQL...
UPDATE donread SET donread.Knowledge = "Between Keyword" WHERE (((donread.Knowledge)="IsMissing"));
... should work perfectly. (HUGE GRIN)

MistyCat
06-07-2012, 11:36 PM
WarNox, I have a solution to my own example. When I created my query from the GUI, the SQL was:


SELECT Employees.EmployeeID
FROM Employees
WHERE (((Employees.EmployeeID)=IIf(True,([Employees].[EmployeeID]) Between 3 And 6,([Employees].[EmployeeID])=8)));
... which didn't work.

However, if I create the SQL directly, omit the "=" and specify each actual criterion for "Case if True" and "Case if False", the query works as expected.


SELECT Employees.EmployeeID
FROM Employees
WHERE IIf(True,[Employees].[EmployeeID] Between 3 And 6, [Employees].[EmployeeID]=8);

So, there's a really good chance that this will work for you.

WHERE IIf([Forms]![frmTest]![chkDates]=True,[tbl_Main].[Date] Between [Forms]![frmTest]![txtStart] And [Forms]![frmTest]![txtEnd],Date);

Cheers,
Alan

WarNox
08-07-2012, 07:25 PM
Hi Alan,

Thanks for the responses. Been a bit of a crazy weekend but finally had the chance to test this out and yes, it works as expected :)

I'm not quite sure, because I get the same results either way, but does it matter what I have for the 'False' condition? In the example above right at the end of the SQL statement you have 'Date'. This does work but I also get the same result if I put [tbl_Main].[Date], [Category], "bob" or any other value.

Cheers,

Gregor

MistyCat
08-07-2012, 08:57 PM
Warnox, I was working from the code in your original post, which I took to mean, "If chkDates is ticked, use the date range between txtStart and txtEnd, otherwise use today's date." Now I go back and read your text, I see you've asked, "... otherwise show all values."

So, where you had "Date" put [tbl_Main].[Date]
That will show all fields unfiltered.

Is that what you're after?

WarNox
09-07-2012, 01:23 PM
Yes, that is was I was after but regardless of what I put as the False condition it worked fine :) I was just wondering why?

MistyCat
09-07-2012, 02:43 PM
I don't quite follow. Are you saying that:
If chkDates is not checked (=False) then no matter what you type in the second condition, you get all records?

Even if that actually is what you want, there would be evil lurking in the heart of the statement, just waiting for a bum to bite...

WarNox
09-07-2012, 03:15 PM
WHERE IIf([Forms]![frmTest]![chkDates]=True,[tbl_Main].[Date] Between [Forms]![frmTest]![txtStart] And [Forms]![frmTest]![txtEnd],Date);

WHERE IIf([Forms]![frmTest]![chkDates]=True,[tbl_Main].[Date] Between [Forms]![frmTest]![txtStart] And [Forms]![frmTest]![txtEnd],tbl_Main.Date);

WHERE IIf([Forms]![frmTest]![chkDates]=True,[tbl_Main].[Date] Between [Forms]![frmTest]![txtStart] And [Forms]![frmTest]![txtEnd],"Random");

WHERE IIf([Forms]![frmTest]![chkDates]=True,[tbl_Main].[Date] Between [Forms]![frmTest]![txtStart] And [Forms]![frmTest]![txtEnd],MistyCat);

All of these work correctly :)

So what I'm saying is that regardless of what is in the false condition it doesn't seem to matter. Because if the box is NOT ticked and the false condition is evaluated all of the records will be displayed. This is what I want to happen but I'm not entirely sure why it is working.

MistyCat
09-07-2012, 04:17 PM
That's interesting. (Translation: "Bugger.")

I'm not at a computer with Access at the moment, but I notice that Access hasn't added quotes to the "MistyCat" parameter but it hasn't thrown a DataType Error.

I think it may be evaluating the False part as though it's a named variable.
If you use an unquoted 0 as your "Condition if False," do you get a Datasheet with no rows returned?

WarNox
09-07-2012, 06:36 PM
Ok, I lied. If I put MistyCar without quote marks it just prompts for the value. If I put '0' it does give a blank table.

MistyCat
09-07-2012, 07:56 PM
I think I'll just back slowly away from this one. In my own test sample Access (2003) is changing the SQL from what I type to what it thinks I meant to type; e.g. where I typed "True" within the conditional the Query Grid displays "<> False" as the entire parameter.

If it works, don't fix it. :-)

WarNox
09-07-2012, 10:43 PM
Cool, I'm happy with that :) Yep, mine does the '<> False' this as well. I'm using 2010.

Thanks for all the help!!!!

Tony
10-07-2012, 01:13 PM
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

WarNox
10-07-2012, 02:57 PM
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.

Tony
10-07-2012, 02:59 PM
no worries - I don't have to maintain it! :D

MistyCat
10-07-2012, 07:51 PM
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... :-)

Tony
10-07-2012, 10:56 PM
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.

WarNox
10-07-2012, 11:31 PM
But wouldn't there be an IIF statement either way?

MistyCat
11-07-2012, 12:19 AM
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.

WarNox
11-07-2012, 12:20 PM
Oh I see what you mean. Might have a play with that but was trying to stay away from VB :)

Tony
11-07-2012, 12:24 PM
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.