Need help with criteria for a query - want all records or selection from combo box

Col

I have a query with criteria that comes from a combo box on a form. I'd
like to set the query up so the user can either limit the criteria to
the selection in the combo box or the user can pull all records. Can't
figure out a way for the query to pull all records. Currently uses the
criteria [forms]![frmreport]![combo10]. I've tried a couple of things
to allow for the option of all records, but without any luck. Any
suggestions?

Jul 5 '06 #1

Subscribe Reply

1828

Allen Browne

Switch the query to SQL View (View menu, in query design.)

Locate the WHERE clause.
It will be something like this:
WHERE ((Table1.Field1 ) = [forms]![frmreport]![combo10])

Change it to this kind of thing:
WHERE (([forms]![frmreport]![combo10] Is Null)
OR (Table1.Field1 = [forms]![frmreport]![combo10]))

If you have several of these, it gets messy to maintain.
For an alternative, see how the Search Form works here:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Col" <cm****@hotmail .comwrote in message
news:11******** **************@ p79g2000cwp.goo glegroups.com.. .

>I have a query with criteria that comes from a combo box on a form. I'd
like to set the query up so the user can either limit the criteria to
the selection in the combo box or the user can pull all records. Can't
figure out a way for the query to pull all records. Currently uses the
criteria [forms]![frmreport]![combo10]. I've tried a couple of things
to allow for the option of all records, but without any luck. Any
suggestions?

Jul 5 '06 #2

Another way to skin the cat
Put this in the criteria of the field that is based on the combo box

Like IIf(IsNull([Forms]![test]![Combo0]),"*",[Forms]![test]![Combo0])

AP

Jul 5 '06 #3

Col

This seems to be working fine. Thanks so much!!

AP wrote:

Another way to skin the cat
Put this in the criteria of the field that is based on the combo box

Like IIf(IsNull([Forms]![test]![Combo0]),"*",[Forms]![test]![Combo0])

AP

Jul 5 '06 #4

Allen Browne

Actually, this one fails to return the records where the field is Null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"AP" <me**********@g reenixsolutions .comwrote in message
news:11******** **************@ m79g2000cwm.goo glegroups.com.. .

Another way to skin the cat
Put this in the criteria of the field that is based on the combo box

Like IIf(IsNull([Forms]![test]![Combo0]),"*",[Forms]![test]![Combo0])

Jul 5 '06 #5

Interesting,
Thanks for pointing that out Allen. I have always used that method on
required fields, so it never bit me.

Nice to know though. Thanks
AP
Allen Browne wrote:

Actually, this one fails to return the records where the field is Null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"AP" <me**********@g reenixsolutions .comwrote in message
news:11******** **************@ m79g2000cwm.goo glegroups.com.. .
Another way to skin the cat
Put this in the criteria of the field that is based on the combo box

Like IIf(IsNull([Forms]![test]![Combo0]),"*",[Forms]![test]![Combo0])

Jul 5 '06 #6

Similar topics

1036

Warning. Min/Max use on fields with criteria

by: Salad | last post by:

This may occur to you in the future. You want to get the min or max of a field that you set criteria on...maybe for a combo box. Let's say you wanted to get the minimum of all date fields where the date field is in the future. If you use the query builder and don't modify the results you end up with zero records. Ex. SELECT Min(DateField) AS MinDate FROM Table HAVING Min(DateField) > Date()+1

Microsoft Access / VBA

2055

Performance on access queries with conditions

by: bhbgroup | last post by:

I have a query on one large table. I only add one condition, i.e. a date (the SQL reads like 'where date > parameterdate'. This query is rather quick if 'parameterdate' is either explicitly written into the query or if it is a 'normal' access parameter value that is entered during the query. If I however create a separate parameter table that contains nothing but the date I want to use in the query and then refer to this table (the query...

Microsoft Access / VBA

2344

Help with (il)logic in option group/combo box/query

by: Steve | last post by:

Access 97. I have a form where there is an option group with two buttons, and a combo box. The combo box Row Source is a query. The option group has two options a) include a subset of the query where all records with the field OPEN set to Yes, or return all records in the query (OPEN = Yes or No). If I "manually" set the criteria to 1 (yes) or 1 Or 2, I get the correct records returned. However, I want to use the option group

Microsoft Access / VBA

1251

Trouble with my SQL

by: Giordano | last post by:

I am trying to construct a report menu that will allow the user to select any combination of 3 variables from 3 combo boxes (Select Subject/SelectCategory/Date Range). There are 2 possible options for each combo ie <Allor a specific value . That means there are 8 possible outcomes from the user selection process. The SQL for the report is constructed 'on the fly' when the report is opened, using a series of "if" calculations in the...

Microsoft Access / VBA

1934

Need Help on a query

by: shorti | last post by:

db2 v 8.2 on AIX 5.3 I will try to explain as brief as I can what it is I need. I am building a function that will be called multiple times where I will need to return x amount of records each time for the same basic query. I will be basically get passed in where I had left off the last call but it is two fields that make up this unique index. The main rule is that I cannot hold open a cursor on the record I last read because it...

DB2 Database

2286

Help needed with Query criteria and combo boxes

by: ericargent | last post by:

Hi I'm using Acces 2003 I have Query where the several parameters for the criteria are supplied from a form. One parameter source is a combo box. What I am trying to do is if: An item is selected on the combo box then that value is used as the criteria If user does not want the contents of the combo box used, then Query should ignore that field. I have tried to do this with an iif statement in the query criteria and a control box on...

Microsoft Access / VBA

3971

Need to create a search facility

by: suek | last post by:

I have a table with over 4000 records to search upon, and the users don't like a combo box. So what I have been trying to do for the last twelve hours is do some code to get a text box to search. I am learning fast, but I really don't know how to do it, and would appreciate some help. So far I have got to this: Private Sub btnSearch_Click() Dim strFindLocation As String

Microsoft Access / VBA

5601

Making a report with only records matching certain criteria

by: Doug | last post by:

What is the simplest way to make a report where only the records where a field matches a certain date are included, and the user first selects that date (from form or popup)? (I can write the SQL to select the records I want...)

Microsoft Access / VBA

2504

How to use a listbox to specify criteria for a query

by: Christoph | last post by:

I have an <S access databse and try to select the the customer ID to print a statemet report based on the customer ID but am unable to do so Can anybody help Thank you Christoph Access 2003

Microsoft Access / VBA

9684

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9530

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10459

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10236

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

10017

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

7552

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5577

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4120

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2928

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General