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 7494 articles. 0 followers. Follow

3 Replies
1092 Views

Similar Articles

[PageSpeed] 58

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:

List box
I am trying to use the list box function from the control toolbar. I am not sure how to get the listings I want in the list box. Is there certain VB coding that is needed? Any help will be appreciated. Thanks. Todd No VBA code needed. In Design mode, click on your list box and select Properties from the Control toolbox (or right-click your list box and select Properties), then scroll down to the ListFillRange Property and indicate the cell range address that has the items you want to appear in the box (i.e., A1:A10). Then exit out of design mode and test your list box. MRO "Tod...

Automatically set the data type as date for a Make Table Query
I have a make table query that I run daily. One of the fields of the query is a date field. The source table for the query treats the information as a text string in a date format that is not recognized by access. I have created formula that parses the string to put it into standard "mm/dd/yy" format. However, when I run the make table query it treats the date field as text. This is a problem because I use a form to hold the criteria for a query that I run on the new table. In order to get the query to work, I have to manually set the data type to short date. This will ...

Compare values in columns
How can I compare a master part numbers list in one Excel column or worksheet against actual values in a second Excel column or worksheet, and display the missing part numbers that were not in the second column in a new column or worksheet? For Example: Part Number Master Part List Missing Parts 12A221315 12A221315 12A221332 12A221316 12A221316 12A221333 12A221317 12A221317 12A221318 12A221318 12A221319 12A221319 12A221320 12A221320 12A221321 12A221321 12A221322 12A221322 12A221323 12A221323 12A221324 12A221324 12A221325 12A221325 12A221326 12A221326 12A221327 12A22132...

how do i view different mail boxes
I have set up different email accounts in outlook 2000. How do I choose which mail box I view when I go to outlook? btoonhoule <btoonhoule@discussions.microsoft.com> wrote: > I have set up different email accounts in outlook 2000. How do I > choose which mail box I view when I go to outlook? Using IMO or C/W mode? -- Brian Tillman ...

Concatenate Multi-Select List Box Items
I need to use the chosen items in a multi-select List Box as concatenated text in another control on a subform. This text will be part of a large amount of concatenated text. How do I do this? I did find info here about using such items in a query but it was way over my head and I couldn't figure out how to convert that idea to this issue so please don't just refer me to that w/o some other explanation for this novice. For example, if the user selects: Frt Bumper, Grille, & Headlamp in the List Box, I want to then somehow concatenate those selections into a sente...

Filtering a combo box using a combo box
All, I am trying to filter the records from a table displayed in a combo box using a combo box on the same form. I built a query and set the criteria as Me![FormName]![Combo_box]. This allowed me to filter the list, but it did not do exactly what I wanted. When the first combo box is blank, the second one is also blank. Is there anyway I can get the second combo box to display all of the records when the first box is blank? Thank you for your help. Regards, John Try a criteria in the second combo of: Like Forms!YourFormName!Combo1Name & "*" -- Arvin Meyer, MCP, MVP http:/...

colour change of dated box
How can I change the colour of a dated box of excel after the date is over. I want the programme to read date automatically from pc and change colour. try Conditional Formatting In 2003: 1. Select the cells you want to format 2. Choose Format, Conditional Formatting 3. Choose Cell Value Is from the first drop down 3. Choose Formula is from the first drop down 4. Choose less than 5. In the third box, enter =TODAY() 6. Click the Format button 7. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. -- Hope this is helpful Appreciate that you provide your feedbac...

Linking Values in Worksheets
Is there a simple method of linking a column of values in one worksheet to another worksheet? I can do it manually, one at a time but is there a way of copying the link down the column? (The columns are identical in length) Registers\[Stock Control-Sigma-SMTech.xls]Sheet1'!$C$1369 Registers\[Stock Control-Sigma-SMTech.xls]Sheet1'!$C$1370 Registers\[Stock Control-Sigma-SMTech.xls]Sheet1'!$C$1371 etc, etc Many thanks david Hi David try ='C:\Registers\[Stock Control-Sigma-SMTech.xls]Sheet1'!$C1369 and copy down Frank DavidM wrote: > Is there a simple method of linki...

Display Formula Values
I have a number of formulas that are displaying the result using the value that is displayed in a referenced cell rather than what was actually input. For example, if my formula in cell A1 is B1*C1 and my value in C1 is .075 and the cell is formatted to display a percentage with no decimal places my formula in B1 uses 8% instead of 7.5%. How can I address this across the entire workbook rather than having to change the format in each of the necessary cells. Thanks for your help. What formula do you have in B1? -- Regards, RD --------------------------------------------------------...

Combo Box on Forms
Hi, I am using a combo box on a form that has several items w/ one being Other. If the user selects other I want them to be able to input what they want so that it will go into the table instead of just the "Other". I just am not really sure on how to do that. Can you help? Thanks, Jaime In design view click on VIEW - Properties and the the combo. Then change Limit To List to No. -- KARL DEWEY Build a little - Test a little "jseger22@yahoo.com" wrote: > Hi, > > I am using a combo box on a form that has several items w/ one being > Other. If the use...

sent box
how do i get the emails in the sent box to show sent to instead of sent from? -- deb deb wrote: > how do i get the emails in the sent box to show sent to instead of sent from? Add/remove whatever columns you want. Right-click on the header row and select Field Chooser. i may have not explained myself too well i dont want to sort by the "to" column i want the header on the email to show "to" not "from" i think the problem might be that this is not the default sent file, its just a folder named "sent" its for my boss, he ha...

Combo Boxes
Someone sent me an Excel document where each of the cells in I1:I312 are combo boxes. When you click off of those cells, the down arrow disappears. The list for the combo boxes are cells AA1 and AB1 (AA1 is New and AB1 is Existing). Please explain how this was done? Also, can you please tell me if it is possible to make New and Existing autocorrect entries. So even though the choices are New and Existing, if you hit N, New appears, and if you hit E, Existing appears. Thanks!! It sounds like Data|Validation. Take a look at Debra Dalgleish's site: http://www.contextures.com/xlDataVa...

area chart doesn't show values
hi all, in excel 2003 i have created a area chart, based on values per day. If i point the mouse on a data point, a flag will show the data and date of this value. after open in excel 2007, this will only work, if i change the chart type from area to line-graph, so do everybody know, how to show the values in an area chart? thanks and greetings from switzerland Dieter ...

Edit box should support languages like japanese,chineese etc
Hi wht im trying to ask is.. 1. In my application, all the labels r in english only. 2.If i change the content of one text box in japanese or chineese language , [Text box value is the title for another dialog] 4.After i changed the content, i want the title to be displayed in japanese or chineese language. Note: I want only that text box to be changed ,,not the entire application.. so How can i able to support multibyte characters in MFC controls CEdit --text boxes.. Plz reply They do. You have to be in a Unicode app, and you have to have selected a Unicode font that has Chin...

Separator in combo box
hi, Is it possible to have a line separator in a combo box? I mean something like a separator in menus? thanks, Behzad Try :- http://www.codeproject.com/combobox/zsepcmb.asp http://www.codeproject.com/combobox/customcombo.asp -- Regards, Nish [VC++ MVP] http://www.voidnish.com /* MVP tips tricks and essays web site */ http://blog.voidnish.com /* My blog on C++/CLI, MFC, Whidbey, CLR... */ "behzad" <b@b.com> wrote in message news:%23fVB3em5EHA.3368@TK2MSFTNGP10.phx.gbl... > hi, > Is it possible to have a line separator in a combo box? I mean something > like...

Query Help 03-14-08
Hi, I have a table with two field: LOGNumber and VINumber I would like to see all the records where the VINumber is duplicated for a particular LOGNumber. Can anyone help me please. Thanks in advance. Try something like: SELECT LOGNumber, VINumber, Count(*) FROM YourTableName GROUP BY LOGNumber, VINumber HAVING Count(*) > 1 To build this query through the graphical query builder, create a new query and add your table to it. Drag to two fields into the grid, then type the LOGNumber field into the grid a second time. Change the query into a Totals query (there's an icon with a...

FindFirstUrlCacheEntry() returning NULL Query?
Hi All In my Dialog based Application when the user clicks the button<Delete cache>, i want to delete all the contents of the folder "Temporary internet Files". For this i m trying to use the functions "FindFirstUrlCacheEntry","FindNextUrlCacheEntry" & "DeleteUrlCacheEntry". To start with when i use FindFirstUrlCacheEntry() i m receiving NULL as the return value.this is how i m using it: -------------------------- void CQwDlg::OnButtonDeleteCache() { HANDLE h1 = NULL; INTERNET_CACHE_ENTRY_INFO l1; DWORD dwSize = sizeof(INTERNET_C...

How to display query criteria in my Report?
Hi, I have searched but found no answers, so I guess this is standard :P Anyhow, how do I get a certain fields query criteria written in the report? (For example, my data source is a query that selects all customers (cust_id) with annual turnover gretater than 10,000 (criteria is >10000). Now, sometimes I change the criteria so it would be nice to have it dynamically turn up on the report (rather than static text). But what's the "call function" for this? Kindly, Mikael Mikael Lindqvist wrote: >I have searched but found no answers, so I guess this is standard :P &g...

Text boxes on graphs
How do you make a text box hide the grid lines behind it on a graph? The grid lines are still visible and run through the text box, cluttering it up. Thanks right-click text box choose -- 'Format Text Box' from shortcut menu colors and lines tab change fill color to white Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day :) * Drew wrote: > How do you make a text box hide the grid lines behind it on a graph? The > grid line...

#N/A Values : Returned by Formulas vs Entered Manually
Hello; When some cells of the data series have #N/A values returned by formulas, the corresponding chart fails. But if the #N/A values are manually entered into those same cells, the chart works fine !! To my understanding, Excel Charts treat cells with #N/A values as empty cells, so one may select the relevant chart option to "leave gaps", which is perfect. With this apparent different interpretation (by Excel Chart) of the same #N/A values in the data series, how can I make the #N/A values returned by formulas acceptable by the chart ?? Thank you for your help. The #N/A ...

access compare values and select higher of two
In Access database I want to compare the values in two fields in a form and then select the higher value, insert it into another field and then use in a formula. E.g. Value 1 = 500 Value 2 = 600, 600 to be inserted into another field and then be multiplied. On 11 apr, 21:26, Captain Turtle <Captain Tur...@discussions.microsoft.com> wrote: > In Access database I want to compare the values in two fields in a form a= nd > then select the higher value, insert it into another field and then use i= n a > formula. > > E.g. Value 1 =3D 500 =A0 Value 2 =3D 60...

Multiple Combo Boxes Highlighted
Hi, I have a problem with something in VB6. I have 4 combo boxes with individual names (not an array) located on an SSTab object. When I select another tab and then return to the tab these boxes are located on, each of them appears to be highlighted in blue. I add values to them only when loading the form so I'm at a loss as to why this happens ? Any ideas are greatly appreciated. Thanks, Jen. "Jennifer Ward" <jward@comcast.net> wrote in message news:eq5HrcBrKHA.6064@TK2MSFTNGP02.phx.gbl... > Hi, > > I have a problem with someth...

HELP !! Combo box to feed other combo boxes
Hi, I have a form with a stock list. One field is [Dealer Allocated] so stock can be allocated to a specific dealer - this is a combo box (combo1) for single allocations. I now also want to be able to update this combo box for several records in bulk. I thought I would do this by having a second combo box in the footer of the form which the user selects the dealer from and then clicks a button which looks for first record without dealer allocated and updates the value of combo1 to the value of combo2. This would then be enclosed in a Do Loop for specified number of records. I can...

Logical Filtering based on Value
I've been using this formula to sort some values and return results if( or( and( b9<>"", isnumber( find( Left(b9,1 ,"AWFUY") ) ), isnumber( find("SHORT",g9 ) ), sum( countif(c9,{"BOG","BLM","CMO"}) ) ) ) ) ,i9-0.01,j9 ) --I used ALT-ENTER between each formula so as to see thing clearly Column A values can be "B01" Column B values can be "BRN" Columns I & J are values, one being lowest the other being highest allowed. On another sheet I...

list mails sorted on date, independent of subfolder structure of INBOX
I hav e a lot of mail rules that sort incoming mails in subfolders of the INBOX. But now I want to see a list of all mails, sorted on date, independent of the folder structure of the INBOX. Possible ? how ? If you're using Outlook 2003, set up a search folder. If not, use = Advanced Find.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =20 <Osiris>...