Creating a "Select All" option in a Combo box that filters a report 3

[ErinB]

I am trying to create a "Select All" option in one of 9 Combo Boxes used in a form to create reports specific to what the user chooses. I have gotten the Union query made to do this using the code
SELECT [combo field] FROM

UNION SELECT "*" FROM

I can't connect the Union query with the report that is generated by the form. I'm not sure where to link the two. Any suggestions?

 

[JoeMiller]

I don't think I understand what you're trying to accomplish and why you need a union query to do it.. Could you explain a little more? Table/form/query/report/control names and examples are all helpful.

Joe Miller
joe.miller@flotech.net

 

[ErinB]

I have a combo box of category codes and when a code is chosen from this combo box it filters a report generated by this form by the selected code. I want to be able to select All of the category codes so that a report is generated that displays all of the category codes instead of filtering it. I was told i needed a Union query to connect the query behind the combo box with the query behind the report. Is there a better way? Thanks!!

 

[JoeMiller]

Ahhh.. no you need a UNION query to give you the * in the combo box. I assume that you have a * followed by all your items in the combo box now.

Now in the query that feeds the report, set the criteria for the category code field to this:

Like [Forms]![MyFormName]![MyComboBoxName]

Then when your users select a value they'll get the appropriate category and if they select * they'll get ALL the categories.

HTH Joe Miller
joe.miller@flotech.net

 

[ErinB]

I've tried adding the Like statement into the query behind the report, and I've tried a few other ways, but I still can't get this to work properly. When I select * from the combo box, it doesn't recognize it and says it doesn't find any records with * as the category code. When you run the query it works and will give you all of the records, but it won't work when you create the report. Do you know where the problem may be here?

 

[JoeMiller]

The query runs fine and shows you all the records? Yet the report does not? Something ain't right with that. Are you using the same query that works for the report?

Joe Miller
joe.miller@flotech.net

 

[troyries]

I've been looking for a good solution to this problem as well. All the solutions I've seen only partially solve the problem. Like "*" returns all records where there is a value in the queried field. It does not return records where the queried field is null. If a user chooses "Select All" I want them to see all records not just records with a value in one specific field. Any suggestions?

 

[KenReay]

Hi

troyries, if that is what you want then you need a criteria of something like:

Like Forms!cboMyCombo OR (IsNull(CriteriaField) AND Forms!cboMyCombo = "*&quot Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[stickers]

Hi,

I'm trying to do a similar thing - but I'm not as advanced as everyone else just yet! How do I get a * to appear at the top of the combo box? I've tried putting this (below) into the row source of the combo box, but it returns an error - I think it's trying to pull all of the fields through. Whereas I want * to represent 'all of the Projects'

SELECT ProjID, ProjName FROM tblProjects
UNION SELECT "*" FROM tblProjects

My combo box selects ProjectID and ProjectName from tblProjects. It stores the ID number but displays the names. I'm passing the value of the combo box to a separate query - is there a way of getting this query to not use any criteria for this field if a * has been selected, but to use the ProjectID to restrict the query if a particular project has been selected?

Can anyone help??

 

[KenReay]

Hi

I would normally do this by having a two column combo box with column width of first column set to zero, so user does not see it, then your SQL becomes

SELECT ProjID, ProjName FROM tblProjects
UNION
SELECT &quot;*&quot; As Projid, &quot;<All>&quot; As ProjName FROM tblProjects
ORDER BY ProjName


note < will sort before most other characters, so will come first, or you could make it &quot; <All> &quot; to be sure
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[dchMPower]

Hello,
Need a little further help with the combo box that is based on 2 columns. The code you posted is:

SELECT ProjID, ProjName FROM tblProjects
UNION
SELECT &quot;*&quot; As Projid, &quot;<All>&quot; As ProjName FROM tblProjects
ORDER BY ProjName

Question:
If I have multiple ProjName for each ProjID, how can I make the combo box show only DISTINCT ProjName?

Thanks
Derrick

 

[KenReay]

Hi

You could Group BY on ProjName,

BUT

I think you miss the point, the assumption is that ProjId is a unique key, so you would not have multiple occurrences

or if I missunderstand you and what you are saying is you have multiple occurrences of ProjId, ProgName you could use the DISTINCTROW qualifier to return only one of them

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[danvlas]

I usually do it like this:

SELECT ProjID, ProjName FROM tblProjects
UNION
SELECT 0[/b] As Projid, &quot;(All)&quot; As ProjName FROM tblProjects
ORDER BY ProjName

And when creating the filter, just check if it's 0:

Dim fltr As String
If nz(Combo,0) <> 0 Then
fltr = &quot;ProjID = &quot; & Combo
End If
DoCmd.OpenReport &quot;RepName&quot;, acViewPreview,, fltr

But you have to make sure ProjID is never 0...

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[davemsteel]

I have had to solve a very similar problem to this.

My users needed to see some or all data in a given table on demand.

My fix was a simple form called at report time with all the fields that they required in combo boxes with the default value set to &quot;*&quot; This form is not linked in any way to the source table. The combo box looks at the the required fields in the source table.

All reporting queries are simply select queries using like([Form_Name]![Form_Combo_Box_Name])in the criteria field for each field to filter, or not, as the case may be.

This works well for my users.

I hope it is of some help

Dave Steel

david@steel.name.uk

 

[danvlas]

The problem with Like is that it doesn't return records with null values. And saying (All) includes such records.
In this case the filter should be one of the following:

Field Like &quot;*&quot; Or Field Is Null

(Field Is Null Or Field Is Not Null)


Or...no filter at all.

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html