IIf statement in query criteria 12-08-09

I am having a problem with the iif statement when I use it in the criteria 
for a query for a combo box (Combo2) that is based on the selection of 
another combo box (Combo1)

Essentially, what I am trying to do is show all choices in combo2 if there 
has not been a selection in Combo1; And show a filtered selection in Combo2 
if there is a selection in Combo1.

I am trying to do this using the iif statement when setting the criteria in 
the Combo2 query.

iif (isnull(combo1), No Criteria is Set, Criteria is set)

I am having trouble with the "No Criteria is Set" part of the expression. 
Leaving it blank, inserting Null or "" (zero length string" doesn't work.

Why am I doing this? Not only do I want to be able to make a selection in 
Combo1 then a selection in Combo2 based on Combo1's selection but I want to 
be able to do it the other way round. ie make a selection in Combo2 (so I 
need all choices displayed) which will automatically make a selection in 
Combo1.

I hope all this makes sense.

Is there a way? Or am I going about this the wrong way.

Any help would be greatly appreciated.

Thanks
0
Utf
12/8/2009 8:04:09 PM
access.queries 6343 articles. 1 followers. Follow

5 Replies
1072 Views

Similar Articles

[PageSpeed] 29

It would help if you showed the query you are trying to build and told us a 
little bit about the type of fields you are applying the criteria against.

SELECT Field1
FROM SomeTable
WHERE Field2 = Forms![NameOfForm]![NameOfComboSource]

If Field2 always has a value and is always text you can use:
SELECT Field1
FROM SomeTable
WHERE Field2 LIKE Nz(Forms![NameOfForm]![NameOfComboSource],"*")

Otherwise you can use
SELECT Field1
FROM SomeTable
WHERE Field2 = Forms![NameOfForm]![NameOfComboSource]
OR Forms![NameOfForm]![NameOfComboSource] Is Null


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Proko wrote:
> I am having a problem with the iif statement when I use it in the criteria 
> for a query for a combo box (Combo2) that is based on the selection of 
> another combo box (Combo1)
> 
> Essentially, what I am trying to do is show all choices in combo2 if there 
> has not been a selection in Combo1; And show a filtered selection in Combo2 
> if there is a selection in Combo1.
> 
> I am trying to do this using the iif statement when setting the criteria in 
> the Combo2 query.
> 
> iif (isnull(combo1), No Criteria is Set, Criteria is set)
> 
> I am having trouble with the "No Criteria is Set" part of the expression. 
> Leaving it blank, inserting Null or "" (zero length string" doesn't work.
> 
> Why am I doing this? Not only do I want to be able to make a selection in 
> Combo1 then a selection in Combo2 based on Combo1's selection but I want to 
> be able to do it the other way round. ie make a selection in Combo2 (so I 
> need all choices displayed) which will automatically make a selection in 
> Combo1.
> 
> I hope all this makes sense.
> 
> Is there a way? Or am I going about this the wrong way.
> 
> Any help would be greatly appreciated.
> 
> Thanks
0
John
12/8/2009 8:13:38 PM
>>I am trying to do this using the iif statement when setting the criteria in 
the Combo2 query.
Try this --
    Like iif (isnull([combo1]), "*", [combo1])

>>but I want to be able to do it the other way round. ie make a selection in Combo2 (so I need all choices displayed) which will automatically make a selection in Combo1.
I do not see how making a selection in Combo2 will ever control Combo1.

-- 
Build a little, test a little.


"Proko" wrote:

> I am having a problem with the iif statement when I use it in the criteria 
> for a query for a combo box (Combo2) that is based on the selection of 
> another combo box (Combo1)
> 
> Essentially, what I am trying to do is show all choices in combo2 if there 
> has not been a selection in Combo1; And show a filtered selection in Combo2 
> if there is a selection in Combo1.
> 
> I am trying to do this using the iif statement when setting the criteria in 
> the Combo2 query.
> 
> iif (isnull(combo1), No Criteria is Set, Criteria is set)
> 
> I am having trouble with the "No Criteria is Set" part of the expression. 
> Leaving it blank, inserting Null or "" (zero length string" doesn't work.
> 
> Why am I doing this? Not only do I want to be able to make a selection in 
> Combo1 then a selection in Combo2 based on Combo1's selection but I want to 
> be able to do it the other way round. ie make a selection in Combo2 (so I 
> need all choices displayed) which will automatically make a selection in 
> Combo1.
> 
> I hope all this makes sense.
> 
> Is there a way? Or am I going about this the wrong way.
> 
> Any help would be greatly appreciated.
> 
> Thanks
0
Utf
12/8/2009 9:08:01 PM
I have done a similiar thing a while back, but no programming expert.  Think 
I did it all with tables and queries.  Do something along the lines of having 
three tables.  table 1 and table 2 for combo1 and table 3 for combo2.  Table1 
has static data, table 3 and 2 get populated by update queries based on what 
gets dirty on the form.  That way you can update from either combo.  This is 
not exact and doing this from memory but it did work for me , just have a 
play.  I remember that me.refresh came in handy also.
-- 
blindman


"Proko" wrote:

> I am having a problem with the iif statement when I use it in the criteria 
> for a query for a combo box (Combo2) that is based on the selection of 
> another combo box (Combo1)
> 
> Essentially, what I am trying to do is show all choices in combo2 if there 
> has not been a selection in Combo1; And show a filtered selection in Combo2 
> if there is a selection in Combo1.
> 
> I am trying to do this using the iif statement when setting the criteria in 
> the Combo2 query.
> 
> iif (isnull(combo1), No Criteria is Set, Criteria is set)
> 
> I am having trouble with the "No Criteria is Set" part of the expression. 
> Leaving it blank, inserting Null or "" (zero length string" doesn't work.
> 
> Why am I doing this? Not only do I want to be able to make a selection in 
> Combo1 then a selection in Combo2 based on Combo1's selection but I want to 
> be able to do it the other way round. ie make a selection in Combo2 (so I 
> need all choices displayed) which will automatically make a selection in 
> Combo1.
> 
> I hope all this makes sense.
> 
> Is there a way? Or am I going about this the wrong way.
> 
> Any help would be greatly appreciated.
> 
> Thanks
0
Utf
12/8/2009 9:25:01 PM
Thanks Karl,

I'll give it a try when I get to work. Makes sense though!

Proko


"KARL DEWEY" wrote:

> >>I am trying to do this using the iif statement when setting the criteria in 
> the Combo2 query.
> Try this --
>     Like iif (isnull([combo1]), "*", [combo1])
> 
> >>but I want to be able to do it the other way round. ie make a selection in Combo2 (so I need all choices displayed) which will automatically make a selection in Combo1.
> I do not see how making a selection in Combo2 will ever control Combo1.
> 
> -- 
> Build a little, test a little.
> 
> 
> "Proko" wrote:
> 
> > I am having a problem with the iif statement when I use it in the criteria 
> > for a query for a combo box (Combo2) that is based on the selection of 
> > another combo box (Combo1)
> > 
> > Essentially, what I am trying to do is show all choices in combo2 if there 
> > has not been a selection in Combo1; And show a filtered selection in Combo2 
> > if there is a selection in Combo1.
> > 
> > I am trying to do this using the iif statement when setting the criteria in 
> > the Combo2 query.
> > 
> > iif (isnull(combo1), No Criteria is Set, Criteria is set)
> > 
> > I am having trouble with the "No Criteria is Set" part of the expression. 
> > Leaving it blank, inserting Null or "" (zero length string" doesn't work.
> > 
> > Why am I doing this? Not only do I want to be able to make a selection in 
> > Combo1 then a selection in Combo2 based on Combo1's selection but I want to 
> > be able to do it the other way round. ie make a selection in Combo2 (so I 
> > need all choices displayed) which will automatically make a selection in 
> > Combo1.
> > 
> > I hope all this makes sense.
> > 
> > Is there a way? Or am I going about this the wrong way.
> > 
> > Any help would be greatly appreciated.
> > 
> > Thanks
0
Utf
12/8/2009 11:33:01 PM
Thanks,

I'll give your idea some thought.

Proko



"blindman" wrote:

> I have done a similiar thing a while back, but no programming expert.  Think 
> I did it all with tables and queries.  Do something along the lines of having 
> three tables.  table 1 and table 2 for combo1 and table 3 for combo2.  Table1 
> has static data, table 3 and 2 get populated by update queries based on what 
> gets dirty on the form.  That way you can update from either combo.  This is 
> not exact and doing this from memory but it did work for me , just have a 
> play.  I remember that me.refresh came in handy also.
> -- 
> blindman
> 
> 
> "Proko" wrote:
> 
> > I am having a problem with the iif statement when I use it in the criteria 
> > for a query for a combo box (Combo2) that is based on the selection of 
> > another combo box (Combo1)
> > 
> > Essentially, what I am trying to do is show all choices in combo2 if there 
> > has not been a selection in Combo1; And show a filtered selection in Combo2 
> > if there is a selection in Combo1.
> > 
> > I am trying to do this using the iif statement when setting the criteria in 
> > the Combo2 query.
> > 
> > iif (isnull(combo1), No Criteria is Set, Criteria is set)
> > 
> > I am having trouble with the "No Criteria is Set" part of the expression. 
> > Leaving it blank, inserting Null or "" (zero length string" doesn't work.
> > 
> > Why am I doing this? Not only do I want to be able to make a selection in 
> > Combo1 then a selection in Combo2 based on Combo1's selection but I want to 
> > be able to do it the other way round. ie make a selection in Combo2 (so I 
> > need all choices displayed) which will automatically make a selection in 
> > Combo1.
> > 
> > I hope all this makes sense.
> > 
> > Is there a way? Or am I going about this the wrong way.
> > 
> > Any help would be greatly appreciated.
> > 
> > Thanks
0
Utf
12/8/2009 11:36:02 PM
Reply:

Similar Artilces:

hello 02-09-05
i have a problem of sending email from outlook express ...

using count in a query
I have a query that I am using to show all records in a table, based on a couple criteria. One of the fields in the query [MEMBER ID] has data the repeats. I want to leave everything the way it is and add to this query a count of how many times a value in this field shows up in the table. I am able to do this if I only put [MEMBER ID] in a query twice, make it a totals query, and leave one on group by and put the other on count. When I try to just add [MEMBER ID] to my original query a second time and change one of them to count in the same way, the value for the count field will on...

Exchange router problems 08-30-04
I have installed the Exchange router and set up a queue as described in the installation guide, but when I send a mail to the address it doesn't show up in the queue ? Is there any way to monitor/configure/troubleshoot the exchange router ? Can I see that is running and is it anywhere in the Exchange System Manager. By default is there any other account on sbs 2003 that has the external account enabled, because I haven't searched all users if this should be why it doesn't work ? Thx Jack Jack, Are you sending the email from within your Domain? If so, it won't appear as...

question on tables and queries
I've imported 2 tables from excel and ran a query against those two tables to determined the difference. This is something I would like to do each month. I would like to keep the same settings in my database, is there a way I can easily import/update the data in the new table without creating a new one each month? The query would stay the same as I'm comparing the same two files, but the the data will be changing every month. I don't know much about access and would like to know if there is an easier way of doing this besides creating a new table and query each month? I&...

Linq Query and Lambda
Hello, I am getting a list of regions as follows: return _context.Regions.Select(r => new RegionModel { Id = r.Id, Name = r.Name }).OrderBy(r => r.Name).AsQueryable(); However I need to get only the Regions which are related with Centers. So I need to check _context.Centers and get all used Regions Ids from each Center.Region.Id and then get all the Regions with those Ids ... Is this possible to do with a lambda expression ... I think it is possible but I am a little bit confused on this. Thank You, Miguel shapper wrote: > Hello...

3 queries into 1
Hello, I am going to try this again starting fresh from my other posts. I am trying to develop an all inclusive query on our inventory. Each fical term we start with new tables. I would like to take our Ending Inventory from our prior fiscal term and combine it with our current inventory balances. In years past I always took our ending inventory and posted them in our purchase order table to have our beginning balances. I thought it would be less time consuming to have a query to combine the 2. Below is a list of data and the query designs. PART # PRODUCT DESCRIPTION END B...

Query in Excell
I would like to create bolean statements in one or more of the columns that result from a query. Basically, I want to test a column for a factor and return a simple 'true' or 'false'. Using this I, hopefully, will eliminate the need to pass the data to Excel and perform the function there. ...

Lookup query value from Form
Hello. I have a form that I would like to lookup values in several taxt boxes. The values I want displayed are from several different Sum Queries. Example: I want to pull Total Working Blance from a query and monthly rent revenue from another query. The values all come from different tables. Is this possible? Thanks for the help. you can use a DLookup() function to retrieve the queries' values. read up on the function in Access Help, so you'll understand how it works. then add an expression to each textbox control's ControlSource property, as =DLookup("MyField", ...

Passing an argument from a query to a command
I am trying to send a report using an email address in a DB. I wrote the following code for it. Private Sub Reminder_Letter_Command_Click() On Error GoTo Err_Reminder_Letter_Command_Click Dim stDocName As String Dim SendTO As String stDocName = "BG Reminder Letter" RunQuery MsgBox "This is After RunQuery " & SendTO SendTO = [Queries]![BG email only from email from ae code].EmailAddress MsgBox "This is SendTO 2 Value ==== " & SendTO DoCmd.OpenReport stDocName, acViewNormal DoCmd.SendObject acSend...

querying similar databases plan
I'm working with hideously unnormalized databases, most of which have similar structures. (Of course, if they had the same structures, it would be too easy.) I work at a company that does cancer research, so they collect information about their patients over a series of visits. The questions they ask (queries) for parts of the final report are standard across all databases, and then some are specific to a the type of study they're doing. What is the best way to approach this, given that I have to summarize like 20 databases in maybe two weeks, and the column names are not necessari...

Double Results in Query
New to Access I am trying to create a query that returns data from 2 tables. one table can have multiple entries on one day per person and the other table will only have one entry per person. 2 tables are DataEntry - will have multiple entries per day per person CallLog - one entry per person per day SELECT DataEntry.OrderTakenBy, DataEntry.Company, DataEntry.Date, DataEntry. Category, DataEntry.ShortCode, DataEntry.ContactName, DataEntry.PositionTitle, DataEntry.PostDateExt, DataEntry.FirmPreview, DataEntry.AllorProgramCodes, DataEntry.Units, DataEntry.value, DataEntry.Mark...

Query Needed 01-04-10
A sample of my data table looks as so: Underlying ID StrikePrice AAPL APVA10C155.00 155 AAPL APVM10P155.00 155 AAPL APVA10C160.00 160 AAPL APVM10P160.00 160 AAPL APVA10C165.00 165 AAPL APVM10P165.00 165 AAPL APVD10C160.00 160 AAPL APVP10P160.00 160 AAPL APVD10C165.00 165 AAPL APVP10P165.00 165 AAPL APVD10C155.00 155 AAPL APVP10P155.00 155 AAPL APVB10C155.00 155 AAPL APVN10P155.00 155 AAPL APVB10C160.00 160 AAPL APVN10P160.00 160 AAPL APVB10C165.00 165 AAPL APVN10P165.00 165 GS GPYA10C155.00 155 GS GPYM10P155.00 155 GS GPYA10C160.00 160 GS GPYM10P160.00 160 ...

Sendkeys Statement
Hello all! I relized with acc2003 when i use the statement "SendKeys "{Esc}", True" the systmem locks up for a second or two and the NumLock key turns of and on again. Is there a way to avoid this? Thank in advance, abe Sure, don't use "SendKeys "{Esc}", True"! Sorry, but this is a well known bug with SendKeys, and not using it really is the only way to avoid the problem! Exactly what are you trying to accomplish? Perhaps someone here can offer an alternative! Linq -- There's ALWAYS more than one way to skin a cat! Answers/posts based...

multiple postcode criteria selection
I want to be able to select multiple postcodes for a selection criteria. These are British 'main' postcodes. eg: SW3, SW5, NW2, NW7, N1, N2 and so on... Ideally I would like to be able to input as many as I need to (with sensible limits), they will most likely be limited to a maximum of 6. this is my exisiting SQL: (which omly allows for 1 postcode criteria) SELECT tbl_Points.Point_ID, tbl_Points.Run_point_Venue, tbl_Points.Run_point_Address, tbl_Points.Run_Point_Postcode, tbl_Points.Run_Point_Postcode AS pcode1, tbl_Points.LeaveBy1, tbl_Points.LeaveBy2, tbl_Points...

Crystal Reports 05-12-04
I'm having an issue where every few weeks the default reports need to be republished. I've got the Crystal Reports toolkit and am able to get the reports working again by republishing them. It's just aggrivating! Has anyone else seen this issue? >-----Original Message----- >I'm having an issue where every few weeks the default >reports need to be republished. I've got the Crystal >Reports toolkit and am able to get the reports working >again by republishing them. It's just aggrivating! Has >anyone else seen this issue? >. > It seems th...

Querying results for two recent dates
I have a table "DETAILS" in which fields are Name Id TestDate Grade (all fields can be duplicated) Now I want a query which should return me 'Name' 'Id' ' TestDate' 'Grade in 2nd Last Test' 'Grade in Latest Test' It should be noted that every person appears the test many times and i just want the recent two results. The query should return names of only those people who have appeared in either or both of the last two tests I was trying to do this by running a query on a query but the results were ...

SUMIF with only one criteria cell within range
I need to use SUMIF to calculate a subtotal of column B based on the criteria of column A. The problem is, the column A has names of group in every 10th cell or so, and column B has many repeated numbers which could vary month to month. Example) Col A Col B group A a b c group B a b c d I need subtotals for group A & B, even when group A has "d" listed in Col. B in other months. Please help. Thanks. Stan Hi it would be much easier if you could fill...

Arabic ODBC query
I am trying to read and write arabic data into Access2000 using MFC ODBC (CRecordset) , but I can only see '?' marks. after doing a research on the news groups , I understand that a tweak needs to be made on RFX_Text can anyone guide me what needs to be done. Appreciate a response. thnx P.Ekkoratha ...

SQL query for "capping threshold"
Suppose I want to add all the numbers in one column, with thefollowing caveat:All values should be capped at some high threshold level.As an example, suppose the threshold is 10000 and the numbers in thecolumn are 7500, 8500, 9500, 10500, 11500 and 12500. The query shouldreturn 555000 (which is 7500 + 8500 + 9500 + 10000 + 10000 + 10000).Any help will be appreciated.Thanks,Bhat SELECT Sum(IIf([YourField]>10000,10000,[YourField])) AS TheValueFROM tblCapped;Insert the right field and table names. Also I think that your calculation below has one too many zeros in it.-- Jerry Whittle, Microsof...

Conditional formatting query #2
Hi, I thought I knew how to do this, but it turns out I don't. If I have names in column A (eg Alan, Bob, Cathy), and values in column B (see below), is there any way I can use conditional formatting to change a cell in A, based on a value in B? A B 1 Alan 4 2 Bob 1 3 Cathy 3 Essentially what I'd like is to highlight the name, if the value is 3 or over. I know how to change the cells in B using conditional formatting, but can't figure out how to change A cells. Thanks, Fiona Select all the cells you want Conditionally formatted. Try this with Formul...

chart #12
Hi!, I remember 2 years back i have done this type of chart from Scenarios or goal seek not excatly rem, but now i forget.. i want to do the chart in excel 2007.. Here is the story.. I had data for 3 different regions i.e east, west, central on different tab. On the 1st sheet i want to display one single chart (rest of the 3 tabs data) with the radio button 1 - East, 2-West, 3-Central. When i click on 1st radio button i.e east the chart should be display with the details from east, when i click on the radio button for West - the chart should be displayed for West and the same should be...

boolean find criteria in Excel
Is it possible to set up the Excel Find and replace to find a or b or c in a cell of content a,b,c, etc? Thanks! Trent I wouldn't think so. -- Regards, Tom Ogilvy "davista00" <davista00@discussions.microsoft.com> wrote in message news:A20AE105-09AB-4681-AD60-60E08F0345BF@microsoft.com... > Is it possible to set up the Excel Find and replace to find a or b or c in a > cell of content a,b,c, etc? > > Thanks! > Trent ...

Multiple Item query
I've read a few posts on this query and it looks like creating a table where I input the item numbers in it and linking it to the query seems to be a good way to get a lot of items in a query. Now what If I have hundreds of numbers I want to look up? do I input all 100 numbers into the table? Or what if it's like 10 numbers but I want create seperate queries for different groups of numbers, should I have 1 table for one group of numbers? What would be the easiest way for a novice to do this? It is easier to fill the table with just the values you need for the query you want to run, t...

display ratios as written in web query
I am using a web query to display data about screen contrasts, all written with the formatting of "700:1" or "3000:1" It displays corectly in the edit web query window, but everything I try to make it display as delivered fails. I tried text, custom formats with @ etc, but it seems to insist on doing the calculation before turning it back to text, or as a ratio it does the division. (The text formatting for this works OK if I type directly into the spread sheet) Is there a way to make a web query display as written? Any help appreciated. Keith ...

Append Query Not Working 05-03-10
My Append query is not working and i am not sure why... Here is my SQL INSERT INTO tblContractPOTracking ( TrackingID ) SELECT tblDocTracking.TrackingID FROM tblDocTracking WHERE (((tblDocTracking.DocumentNumber)="5" Or (tblDocTracking.DocumentNumber)="6" Or (tblDocTracking.DocumentNumber)="7")); Please help! "not working" is a bit vague ... If you want more specific suggestions, please provide more specific description... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and s...