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
1285867 2012-07-06 08:03:00 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 ...
office.microsoft.com
... shows an example in Office 2003

This page ...
www.functionx.com
... 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 (11583)
1285868 2012-07-06 11:36:00 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 		MistyCat (11583)
1285869 2012-07-08 07:25:00 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 		WarNox (8772)
1285870 2012-07-08 08:57:00 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? 		MistyCat (11583)
1285871 2012-07-09 01:23:00 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? WarNox (8772)
1285872 2012-07-09 02:43:00 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... 		MistyCat (11583)
1285873 2012-07-09 03:15:00 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. 		WarNox (8772)
1285874 2012-07-09 04:17:00 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? 		MistyCat (11583)
1285875 2012-07-09 06:36:00 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. WarNox (8772)
1285876 2012-07-09 07:56:00 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. :-) 		MistyCat (11583)
1 2 3