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
1285857 2012-07-04 04:02:00 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. 		WarNox (8772)
1285858 2012-07-04 04:18:00 It'll be something like
WHERE (((tbl_Main.Date>=[Forms]![frmTest]![txtStart]![txtFrom] And (tbl_Main.Date)[Forms]![frmTest]![txtEnd])) 		donread (6401)
1285859 2012-07-04 04:32:00 But I need that IF statement to check the check box... WarNox (8772)
1285860 2012-07-04 09:59:00 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. 		Paul.Cov (425)
1285861 2012-07-04 11:07:00 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 (8772)
1285862 2012-07-05 02:03:00 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 		WarNox (8772)
1285863 2012-07-05 10:08:00 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. 		Paul.Cov (425)
1285864 2012-07-05 22:25:00 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. 		WarNox (8772)
1285865 2012-07-06 03:10:00 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. 		MistyCat (11583)
1285866 2012-07-06 07:42:00 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 		donread (6401)
1 2 3