iif Statement in an Access Query

[ShawnR]

I have a combo box (forms!report!SearchS) with 3 choices.

0 = All (Created by a Union Query in the combo box)
1 = Open
2 = Closed

I have the IIF statement in the criteria of the query that looks at the combo box. The problem arises is the values in the database only includes 1 or 2. I want to use the zero in the combo box to include both statuses. I think I need to use a IIF statemnt as part of the crietia, however I have not been able to get the "all" i.e. "0" selection to work. Selecting 1 or 2 int eh combo box returns correct results. Here is the IIF statment in the criteria:

The field is report.status from report.

iif(Forms!Report!SearchS = 0, Null, Forms!Report!SearchS)

also tried

iif(Forms!Report!SearchS = 0, "", Forms!Report!SearchS)

also tried

iif(Forms!Report!SearchS = 0, 1 and 2 , Forms!Report!SearchS)

When the value is zero the query does nothing and return no results.

Bascially I'm looking for the creteria to be null so it will pass all values (1 and 2) instead of a 1 or a 2.

Your help is greatly appreciated. Let me know if you have any questions.

 

[JeremyNYC]

Post the rest of your code and this will be easy to nail. When you say that chosing 0 does a union query, it sounds an awful lot like you ahve one table for stuff with value 1, and another with stuff for value 2. If so, put those together into one table, with a field used to tell the difference between the two data sets. Then post the code.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[williamu]

Hi,

What you need here may be something like this:

dim strType as String
strType = iif(Forms!Report!SearchS = 0, "UNION" , iif(Forms!Report!SearchS = 1, "OPEN" , "CLOSED&quot, "DEFAULT&quot

I don't actually program using Access Forms but the principle is sound enough. You'd need to replace the "UNION", "OPEN", "CLOSED" with the appropriate SQL.

E.G.
"OPEN" = "WHERE Status = 1"
"CLOSED" = "WHERE STATUS = 2"
"UNION" = "WHERE STATUS"

And default being the default action of OPEN, CLOSED, etc.

HTH
--

William
Software Engineer
ICQ No. 56047340

 

[ShawnR]

Thanks for the quick replies. The post from william won't work. Here are additional details. Below it the SQL form the Query I am building. I'm trying to build it in teh criteria field instead of creating a seperate query file for each scenario for each of the 6 combo boxes.

SELECT Report.ReportID AS ID, Report.InSiteReportID AS [InSite RID], Report.Title AS [Report Title], Status.StatusType AS Status, Type.TypeName AS Type, View.ViewType AS [View], ReportIs.ReportIsName AS [Report Is], ReportFor.ReportForName AS [Report For]
FROM LimitClient LEFT JOIN (ReportFor RIGHT JOIN (ReportIs RIGHT JOIN ([View] RIGHT JOIN (Type RIGHT JOIN (Status RIGHT JOIN Report ON Status.StatusID = Report.StatusID) ON Type.TypeID = Report.TypeID) ON View.ViewID = Report.ViewID) ON ReportIs.ReportIsID = Report.ReportIsID) ON ReportFor.ReportForID = Report.ReportForID) ON LimitClient.InSiteReportID = Report.InSiteReportID
WHERE (((Report.StatusID)=[forms]![report]![SearchS]) AND ((Report.TypeID)=[forms]![report]![SearchT]) AND ((Report.ViewID)=[forms]![report]![SearchV]) AND ((Report.ReportIsID)=[forms]![report]![SearchRI]) AND ((Report.ReportForID)=[forms]![report]![SearchRF]))
ORDER BY Report.InSiteReportID;

Basically I'm trying to change the criteria set above to be blank so that it will then pass all values. I have 6 combo boxes where I have this union query. Set to "all" i.e. 0 value. I just have various fields where I want to pass all values or the specific values based upon the combo boxes. Here is one combo box values. Many of the combo boxes have many values.:

So if you remove the criteria form the StatusID field it would return all values.

Status Table
0 = All
1 = Open
2 = Closed

Thanks for your help!

 

[williamu]

Shawn,

Going back to what I said why can't you use:

"WHERE (((Report.StatusID) " & iif ([forms]![report]![SearchS] = 0, " NOT = 0", IIF([forms]![report]![SearchS] = 1, " = 1", "= 2&quot, "NOT = 0&quot " AND (...)

Try it, granted it's a long way for a short cut there are easier ways (In C++ and VB) but I know this approach works.

HTH
-- William
Software Engineer
ICQ No. 56047340

 

[JeremyNYC]

Shawn,

Your aliases are confusing to me. Why are you aliasing every field? You can make the labels on your report or form be whatever you want. It's best to keep the name of the field as is, without the space, in the query and then make the labels more descriptive and easier to read. As it is, I am having a hard time reading this sql statement. If you make a version of this query without the aliases I'll take another look.

Also, please include your tables and the fields in them, as there are a lot of tables here, and I'm having a hard time picturing the data structure.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[ShawnR]

I will be happy to send you the database. Its has only 3 records right now. If you email me I will send it to you.

srodgers@fieldglass.com

thanx for your help

 

[JeremyNYC]

Shawn,

Sorry. I'd rather not have you send it to me, as then I'd be left doing the work of clearing out your aliases, which I could do from the text you posted above. Someone else may be willing to do that work, but I'm not. On the other hand, if you strip out those aliases, I _will_ look at this. And I don't think it will be that hard to come up with an answer.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[ShawnR]

Ok let me add the details of a new simplified SQL query.

SELECT Report.ReportID, Report.Title
FROM Status RIGHT JOIN Report ON Status.StatusID = Report.StatusID
WHERE (((Report.StatusID)=[forms]![report]![SearchS]));

Report Table
ReportID (would have unique values,primary key)
Title (text field
Status (relationship to the Status table on StatusID)

Status Table
0 = All (Done via the combo box with a union query)
i.e. SELECT StatusID, StatusType FROM Status UNION SELECT 0, 'All' FROM Status ORDER BY StatusType;

1 = Open
2 = Closed

All i want it the criteria to be null or blank so it returns all records open and closed and if a value exists then look at the combo box "SearchS".

I hope this helps!

If you need more details please let me know. Keep in mind i have 5 other fileds i need to do this on the same query as I have stated in the above earlier SQL.

 

[JeremyNYC]

Ahhh. That's a breath of fresh air! It's much easier to read it like this. The complication comes in because you're still including the criterion, and if you want to include all, you should either not include the criterion or do
...Like '*'
which means no = sign.

Is this a canned query ro are you generating the sql in code? If it's in code, it's quite easy, just use a select case structure, something like this (just a snippet, and it's not tested)

dim intStatus as integer
dim strSql as string

intstatus = me!cmbStatus
select case intstatus
case 1
strSql = "WHERE Report.StatusID = 'Open'"
case 2
strSql = "WHERE Report.StatusID = 'Closed'"
end select

strSql = "SELECT Report.ReportID, Report.Title " _
& " FROM Status RIGHT JOIN Report ON Status.StatusID = Report.StatusID " _
& strsql

If it's not in code, how you deal with it will depend on what you're doing with it. Are you opening a form? If so, you can replace the last line (strSql = ...) with
Call DoCmd.OpenForm("frmYadda", , , strsql)
assuming the form already has the SELECT and FROM clauses in it, and all you have to do is give it the WHERE clause.

If these don't work for what you're doing, let me know a bit more about the process.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[ShawnR]

Hi Jeremy:

Thanx for the info. Yes I'm trying to do in as part of the criteria. If i only had one combo i would create a unique SQL strings. However I have 6 combo boxes and there could be 100's of unique strings. I guess I could eliminate some "all" selections to lower the number of combinations but it would limit the functionality of what I'm trying to accomplish.

I'm using the 6 combo boxes to pass filters to a list box based upon the combo box selections.

I'm trying to code this out without have to create a huge amount of strings or queries.

I'm not opening a form. I'm using this to filter out records on a list box.

 

[JeremyNYC]

Shawn,

Cool. If you're filtering a listbox, you can simply build the whole sql in code and assign that value to the rowsource. I know it sound like a lot of code, but it's really not that bad.

Make a function to assign the rowsource. You can call the from the afterUpdate of each combo box or from a sing command button. That function will look something like the stuff in my last post, but you will have to deal with the possiblity of no options being chosen, which means that you should only tack on the " WHERE " if there is already something in the string after you've tested for what's in each of the combo boxes.

It will be a pain to do this the first time around, and you'll probably get frustrated dealing with ', ", and #, but by the end of it, you'll be quite happy with the result, and you'll have learned a good bit.

If you want, put it together using the full sql and just test for two combo boxes and post that. I still suggest that you get rid of the aliases, as they will make it that much harder for you to read, but that, of course, is up to you.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.