Combo Box Value List/Query Criteria

Hi Folks - I have a combo box with a value list of Option1, Option2 and ALL. 
I pass this combo box info into a query. Here's a sample of the query 
criteria:

IIf([Forms]![frmSwitchboard]![cboCaseType]="all",Is 
Null,[Forms]![frmSwitchboard]![cboCaseType])

In other words, if the combo box selection is ALL, then display all records, 
otherwise use selected option. The above criteria does not work. Any 
suggestions? Thanks.

Michael



0
Michael
1/19/2008 2:57:48 PM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
1678 Views

Similar Articles

[PageSpeed] 59

Michael,
    Try... (IsNull is an operator, not a value)

IIf([Forms]![frmSwitchboard]![cboCaseType]="All",
Null,[Forms]![frmSwitchboard]![cboCaseType])

    You didn't indicate the name of the calculated field on the form that 
contains the IIF statement, so I'll use [ResultFromIIF]
    The CaseType field in your query should have a criteria of...
        Like Forms!frmSwitchboard!ResultFromIIF & "*"

    I think the Null will work, but I usually use "".  I think either should 
work, but didn't test.
-- 
    hth
    Al Campagna
    Microsoft Access MVP
    http://home.comcast.net/~cccsolutions/index.html

    "Find a job that you love... and you'll never work a day in your life."

"Michael" <info@homekeyinc.com> wrote in message 
news:Gdokj.13262$OC1.5840@newsfe20.lga...
> Hi Folks - I have a combo box with a value list of Option1, Option2 and 
> ALL. I pass this combo box info into a query. Here's a sample of the query 
> criteria:
>
> IIf([Forms]![frmSwitchboard]![cboCaseType]="all",Is 
> Null,[Forms]![frmSwitchboard]![cboCaseType])
>
> In other words, if the combo box selection is ALL, then display all 
> records, otherwise use selected option. The above criteria does not work. 
> Any suggestions? Thanks.
>
> Michael
>
>
> 


0
Al
1/19/2008 3:11:39 PM
Hi Al - There is no calculated field on the form. The form has a combo box. 
The combo box has 3 choices: Option1, Option2 and ALL. If they choose all, I 
want the query to return all records.

The value of the combo box is passed into a query's criteria. I tried your 
suggestion with NULL and "". Neither option worked. I don't think your Like 
method will work with "al"l. That would just append * to ALL resulting in 
criteria of ALL*. Make sense?

Note: I could instruct the user to leave the combo box blank, in which case 
I could use the Like operator. But, I'd rather use ALL. Any ideas?

Michael





"Al Campagna" <newsgroups@comcast.net> wrote in message 
news:%23UA$X2qWIHA.4476@TK2MSFTNGP06.phx.gbl...
> Michael,
>    Try... (IsNull is an operator, not a value)
>
> IIf([Forms]![frmSwitchboard]![cboCaseType]="All",
> Null,[Forms]![frmSwitchboard]![cboCaseType])
>
>    You didn't indicate the name of the calculated field on the form that 
> contains the IIF statement, so I'll use [ResultFromIIF]
>    The CaseType field in your query should have a criteria of...
>        Like Forms!frmSwitchboard!ResultFromIIF & "*"
>
>    I think the Null will work, but I usually use "".  I think either 
> should work, but didn't test.
> -- 
>    hth
>    Al Campagna
>    Microsoft Access MVP
>    http://home.comcast.net/~cccsolutions/index.html
>
>    "Find a job that you love... and you'll never work a day in your life."
>
> "Michael" <info@homekeyinc.com> wrote in message 
> news:Gdokj.13262$OC1.5840@newsfe20.lga...
>> Hi Folks - I have a combo box with a value list of Option1, Option2 and 
>> ALL. I pass this combo box info into a query. Here's a sample of the 
>> query criteria:
>>
>> IIf([Forms]![frmSwitchboard]![cboCaseType]="all",Is 
>> Null,[Forms]![frmSwitchboard]![cboCaseType])
>>
>> In other words, if the combo box selection is ALL, then display all 
>> records, otherwise use selected option. The above criteria does not work. 
>> Any suggestions? Thanks.
>>
>> Michael
>>
>>
>>
>
> 


0
Michael
1/19/2008 3:36:31 PM
On Sat, 19 Jan 2008 09:57:48 -0500, Michael wrote:

> Hi Folks - I have a combo box with a value list of Option1, Option2 and ALL. 
> I pass this combo box info into a query. Here's a sample of the query 
> criteria:
> 
> IIf([Forms]![frmSwitchboard]![cboCaseType]="all",Is 
> Null,[Forms]![frmSwitchboard]![cboCaseType])
> 
> In other words, if the combo box selection is ALL, then display all records, 
> otherwise use selected option. The above criteria does not work. Any 
> suggestions? Thanks.
> 
> Michael

Why IsNull as the False part of the IIf() expression?

Try:
Like IIf([Forms]![frmSwitchboard]![cboCaseType]="all","*",
[Forms]![frmSwitchboard]![cboCaseType])
-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
1/19/2008 5:45:46 PM
Reply:

Similar Artilces:

the criteria pane and table panes have disappered.
In my excel file I can't see the criteria and table panes and the functions will not reappear. ...

caption, text box, and frame
I inserted a picture (a bitmap), cropped and sized it to fill the page from side to side, and positioned it 1.5" from the top "margin" so that it would be at the top of the text area. I then clicked on Insert Caption and got the usual panel offering "Figure 1" as its label. Instead of simply inserting a paragraph in Caption format as usual, it inserted a text box with the caption label in it. When I typed the content of the caption, the following body text reduced to a single line. Dragging the picture's anchor to a different paragraph made whole paragrap...

Combo Box problem 05-07-07
I have a combo control on a form that has when I select a value the form reverts to the first record it doesn't stay with the current record. I have a NotinList procedure that opens another form to enable the user to add to the list of values in the combo control and when I close this second form the new value is recorded but again the form goes to the first record and doesn't stay with the current one. Anyone any ideas where I can start looking for the reason why this happens? Thanks Tony Tony Williams wrote: > I have a combo control on a form that has when I select a val...

Junk E-Mail Lists Error in Outlook 2003 with Exchange 2003
I have seen several people post about this error message that comes up in Outlook, but so far I haven't seen anyone respond. I am running an Exchange 2003 server with SP1 and my client is Outlook 2003. I installed IMF a few weeks ago, and it appears that this message started soon afterwards. I have now uninstalled IMF, but am still receiving the message. My end users are getting frustrated, as am I. There also appears to be an error in the IMF deployment guide about adding a registry key to increase the list size in Exchange. It states to "right click System", but there is n...

Need to set up a slide with 4 text boxes on same page.
Want to end up with 4 "bulleted" boxes that I can to show 4 strategies and associated task on same page. Also, if possible have each one drop in individually to allow flow conrol for the presentation Are you asking a question about how to do that or having trouble with part of that? If the former, just create four separate text boxes with bulleted text, you are not limited to only one text box per slide. Use the Custom Animation, Effects Options, Text Animation settings to control the entrance of the bullet points. Since you didn't say what version of PowerPoi...

How to convert to absolute value?
Hello! How do I convert numbers to absolute value? Thanks Edit - Copy. Edit - PasteSpecial - Values - OK. Or do you want a code solution ? HTH. Best wishes Harald "Betsy Marlow" <cmarlow22@bellsouth.net> skrev i melding news:5WmHg.12873$j8.11911@bignews7.bellsouth.net... > Hello! > > How do I convert numbers to absolute value? > > Thanks > > Print abs(-12) 12 On Thu, 24 Aug 2006 15:16:31 -0400, "Betsy Marlow" <cmarlow22@bellsouth.net> wrote: >Hello! > >How do I convert numbers to absolute value? > >Thanks >...

Don't plot zero values
I am currently using Excel XP. I have a scatter chart and would only like to plot non zero values. These are not null values. Is there a chart setting I can do to skip plotting zero values or do I need to somehow filter/sort the data first and then plot? TIA George Hey George - If there are true zeros in the data, perhaps the easiest way to exclude them from the chart is to use an autofilter on the data that hides the rows with zeros. Alternatively, you could insert a column to hide the zeros. Say the range with zeros is in B1:B10. Select C1:C10, and enter this formula into C1: =IF...

diable command button based on field value
I have a command button which I would like to disable based on the following rules: If A = 1 then button is disabled If B = 2 then button is enabled Where you put the code depends on how the form works. If this needs to be checked for each record, use the form current event: If A = 1 Then Me.Button.Enabled = False ElseIf B = 2 Then Me.Button.Enabled = True End If But, what if A is not 1 and B is not 2? -- Dave Hargis, Microsoft Access MVP "barrynichols@gmail.com" wrote: > I have a command button which I would like to disable based on the >...

displaying negative values
how do I hide the display of negative numbers? =if(A1<0,"",A1) HTH Regards from Brazil Marcelo "cfuller" escreveu: > how do I hide the display of negative numbers? Hi, That will work if cell A1 has a formula in it. If negative numbers are just typed in, you can use conditional formatting. Highlight the range of cells you want to format. Go to Format > Conditional Formatting... Enter cell value is less than 0. Click the Format... button and select Color: and then click the white icon. HTH -- Ken Hudson "Marcelo" wrote: > =if(A1<0,"&qu...

get a list of file in a directory
Hi. what would be the best way to return a list of files in a directory. I know in VB you could use the dir function, but what should i use in MFC. Doen anyone know of a class available where I could say return all the file in a certain directory and its sub directories Regards Dylan Take a look at the CFileFind MFC class. -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "Dylan Franklin" <dyl2000@lineone.net> wrote in message news:eHHBr0MxDHA.2436@TK2MSFTNGP09.phx.gbl... > Hi. > > what would be the best way to return a list of files i...

MS Query #4
First, does someone know of a document that provides details on MS Query functions and their syntax. For example, I know I can use left, right, etc. What other functions exist? I want to do some parsing on some data fields and need to determine the length of the field and the place where certain characters are located within that field. Thanks. Hi ODBC query syntax is determined by driver, not by Excel. Usually it's like the syntax, used in database system, from where you get data. P.e. the syntax for FoxPro/VisualFox ODBC query is much like to syntax, you use with queries in F...

Visio File Summary Dialog Box is showing wrong file name and size
Hi all, I am using Visio 2003 ActiveX Control in my application using VB.NET 2005. I am calling "visCmdFileSummaryInfoDlg" Command to open File Summary Dialog Box. Dialog Box is open successfully but it always showing Template file size instead of open file size in General Tab. The information it shows is: Type: Microsoft Visio Drawing Location: Size :(928 bytes) Based on: Template - ValueStreamMap.vst Can any one let me know about any property or way so that it shows correct file size? Thanks Asif Can we assume that the new drawing has been saved to disk before you call th...

customizing the task list print options
I'd like to print my "tasks" list but with the complete Note field included. How does one accomplish that? I'm surprised it isn't a standard form provided in Outlook. -- ------------ Michael Bierman ...

No way to have "event" action for query... right?
Often I create a query and corrosponding Form (in data sheet view) I then create a Double Click Event for one of the fields That opens a traditional form showing all the records fields. For instance a list of Accounts and Names. Double click the Account to open a form to see the entire Account record... or whatever. Is there any way to do this without creating the form? We need the query as we use it to select appropriate data. thanks for any help. Mel You can actually base the form off of an SQL statement, and not need to have a saved query. In the form's data tab, just paste ...

parameter values not valid #3
keep getting a window each time I try and save a new task (Outlook 2000) it says: "Could not complete the operation. One or more parameter values are not valid" I just set up Outllook, and can't figure out why it would do this. ...

How to determine the closed value?
There is a number 14 in cell A1, and there is a list of number under column B, for example, 8, 11, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and 16 should be returned in cell A2. 8, 12, 16, 18, 21 I would like to determine the value within the list, which is closest to 14, and because 14 is between 12 and 16, then I prefer the larger number, and 16 should be returned in cell A2. Does anyone have any suggestions on how to do it in Excel? Thanks in advance for any suggestions Eric Hi Eric, this should do it: =INDEX($B$1:$B$5,L...

Drop down list 03-27-10
Hello, Im trying to create a linked multiple drop down list in excel and im not sure if it can be done, such as in the first list of pipe diameter, second list wall thickness, pipe outer diameter, third list circumference and so on. when the an item from the first list is selected, the following lists are to show the specific value based upon the first selection. help would very much appreciated, thank you I think that www.contextures.com has some assistance to offer. Look at this first: http://www.contextures.com/xlDataVal02.html and if that's not exactly what you need...

Set value of const member variable in Init() function?
Is there a method for setting the value of a const member variable in an Init() function when the value is unknown in the constructor? TIA, Harvey "Harvey" <harveyab@juno.com> wrote in message news:1172562585.493563.230590@h3g2000cwc.googlegroups.com... > Is there a method for setting the value of a const member variable in > an Init() function when the value is unknown in the constructor? I don't believe so. But be careful of the difference between const int x=1; and const char *pszString; pszString = ... The latter is ok as the pointer is not const - th...

How to save the value in the CEdit control to a text file?
Im generating a random number to the CEdit control and now i want t save all of this value to a text file. I dont know to code it in the way of MFC. I hope whose know it can send me the soding cause im running out the time to do my project thanks You can use GetWindowText to get its contents and then use CStdioFile class to write the text. MSDN has enough documentation on this. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "TLee" <anonymous@discussions.microsoft.com> wrote in message news:F730B5D6-85F7-45D9-80D9-C43598FA47B6@microsoft.com... | Im generating a random numbe...

Condensed form of Edit Lists
Hi all! Has anyone modified GP Edit lists to get rid of extra spaces? Please let me know. Thanks. -- Marisol Mortera ...

How to slot cell values into pre-defined ranges
I would like to slot cell values starting from 500 upto 75000 in ranges (bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I have 15 bins. pls help Not sure what you want here. Do you want to count the number of times values within a range occur? -- Ian -- "KDD" <KDD@discussions.microsoft.com> wrote in message news:766017CE-A55E-42FF-AD8D-9E74E48CD568@microsoft.com... >I would like to slot cell values starting from 500 upto 75000 in ranges > (bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I have > 15 > bins. > > pls...

Web query to create historical stock data
Web query is easy to schedule 5 minute refresh, however I want to retain this 5minute data to create a database continuing throughout the trading day to generate 5 minute intraday price/volume data, my question is how can I move the data up or down so as not to remove the previous 5minutes data as the new data is generated. Any suggestions appreciated. thx You will need to (use a macro) move to another sheet. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "ChrisT" <ChrisT@discussions.microsoft.com> wrote in message news:8C721E31-87C5-4C99-8119-F57557BDA5E3@m...

user control of distro lists
I have set up some distro lists in AD. We have all 2003 servers and exchange 2003. I want to allow multiple users (if possible) to add and remove users to and from these lists. I saw a post already detailing this but I get no security tab on the dostro lists I created. I made the lists Universal and Distribution when I set them up. What do I need to do? Thanks. you need to view "advanced features" in your aduc console... -- Susan Conkey [MVP] "MikeB" <MikeB@discussions.microsoft.com> wrote in message news:2346AB31-9C7D-4761-8881-2D43475D68E5@microsoft....

Formula help error of #VALUE!
I am not sure I can explain this clearly but I will give it a shot The cells are formatted to show time Cell A4 you enter in 1 or 2 to have cell A3 show the info from cell A2 (if 1) or B2 (if2) Formula for Cell A3 is =(COUNTIF(A4,"=1")*A2)+(COUNTIF(A4,"=2")*B2) It goes up to COUNTIF(A4,"=8")*H2) Problem is this is a time sheet so cells are formatted for time like 8:00:00 AM. Cell B2 I need to be text (Vacation) so staff will get paid vacation day and not show blank like a day off. When I enter text in cell B2 cellA3 shows #VALUE! How can I set this up so one c...

Formula in text boxes
I have heard you can put string arithmetic formula in text boxes. How do you do this? For example I would like to add the contents of three cells as follows: =B1&DOLLAR(B3,2)&B2 Thanks Will A text box can contain a cell reference -- Enter the formula in a cell on the worksheet Create a text box. Click on the text box to select it Click in the formula bar, and type an equal sign Click on the cell that contains the formula Press the Enter key Will Fleenor wrote: > I have heard you can put string arithmetic formula in text boxes. How do > you do this? For example I woul...