Using the IIF function in MS Access

[wishbone]

I want to use the IIF function, to check for the current day, and if the condition is true, then I would like to then run a specific select statement, if false then I would want to run a different select statement. Is this possible to do with iif and have the result returned in on form with a subreport?

IE..

IIF(WEEKDAY(DATE(),2),<ON TRUE RUN SELECT QUERY>, <ON FALSE RUN A DIFFERENT SELECT QUERY>)

[NeoPa]

OK. I guess you're trying. Let me tell you why it's hard to help with a question like this. You leave out too much important information. We have to assume too much.

Firstly, I assume you are talking about SQL code. IIf() can be used in VBA too, and your question makes almost as much sense (from some perspectives more even) if considered as a VBA question than as a SQL one.

Next, I could simply say no of course, as strictly it's not able to work that way, but if I knew the context that you were working within I may be able to suggest a more appropriate alternative for you to use. Everything I want to help you with depends on your asking the question properly in the first place though - hence when you asked it first time I deleted the question and gave you the opportunity to give it more and better consideration before posting it again.

This will have to do for now, but if you take these points on board you will certainly get much better value from this site (Bytes.com), and other forum sites like it in the future.

Actually, I can add that the IIf() function is a function. Sounds obvious, but if you remember that functions return values then you would see that there is no scope for executing the value unless Jet SQL provided a facility for calling itself. SQL is normally executed from VBA code or from within an Access object (like the record source of a form for instance). Even in MS SQL's T-SQL though, which has the EXEC command for such a purpose, it isn't called from within a query's SQL. I hope that helps you to understand why the answer is no.

[patjones]

As NeoPa says, the answer really depends upon context, and you haven't given any context. I can imagine a scenario in VBA whereby you want to pick out one SQL string in one instance, and another SQL string in another instance. Notice that I'm saying string here. When you execute SQL from within VBA, the SQL string has to be attached to a valid Access object, like QueryDef or the RowSource method of a list box or combo box control.

I can also imagine the converse situation, where you might want to write a SQL statement in Access' query design window and embed an IIf( ) within the SQL. Without knowing more about you're trying to do we can only provide general answers.

Pat

[wishbone]

I'm sorry I'm new to this site so I'm not sure how to post questions.. Yes, I'm trying to do this in a SQL query within Access. Here is the sql view window in design view of my code.

SELECT IIf(Weekday(Date(),2),"SELECT Count([ASP Operations Task Request List].ID) AS Expr1
FROM [ASP Operations Task Request List]
WHERE ((([ASP Operations Task Request List].Status)='Completed') AND (([ASP Operations Task Request List].Modified) Between Date()-1 And Date()+1));",'') AS Expr1
FROM [ASP Operations Task Request List]
GROUP BY IIf(Weekday(Date(),2),"SELECT Count([ASP Operations Task Request List].ID) AS Expr1
FROM [ASP Operations Task Request List]
WHERE ((([ASP Operations Task Request List].Status)='Completed') AND (([ASP Operations Task Request List].Modified) Between Date()-1 And Date()+1));",'');

[patjones]

To be honest, this SQL makes no sense. It is not readable and I cannot discern what you're trying to do because I can't tell where one thing ends and another begins.

At any rate, it looks like you're trying to embed one SQL statement as a string within the containing SQL. It is possible to nest SQL queries (we call them sub-queries) - but this is not how it is accomplished.

I think what you need to do is tell us in plain English what you are trying to accomplish, and then we can help you build the SQL from that. For instance, "I want to obtain the COUNT of this field in this table when this condition is met, otherwise I want the COUNT of this other field when some other condition is met".

[NeoPa]

I can see where you're getting confused - and why this is such a confusion for you. Let's see where we can take you.

Firstly, the Weekday() function will always (in all circumstances) return a value between 1 and 7. As such your test will always be found to be non-False. That's a side-issue though.

It seems, if I read your SQL aright (and I have to try to reverse engineer your thinking here so it's a little complicated), that you would like to show the number of records found in the table for the days from yesterday till tomorrow which have a [Status] of 'Completed' as long as today is a Monday, but otherwise show nothing at all (and at some level which is still unshared cancel the process). Would that be a correct assessment?

If so, the IIf() part, as mentioned earlier, cannot be used that way at all. It is a function and returns only values. Not anything in the way of process control.

The counting SQL can be done though :

SELECT Count(*) AS Expr1
FROM   [ASP Operations Task Request List]
WHERE  ([Status]='Completed') 
  AND  ([Modified] Between Date()-1 And Date()+1)