Combo box - "Select All" option

[mdweezer]

I have a combo box that I want to populate data with from a table. I'm using this form as criteria for a report.

So if the table contains "Option A and Option B" they can select either and get reports dealing with either option A or option B.

However I would like a "Both options" selection in the drop down so the report would show records dealing with either or.

I currently have some that do that, that I didn't write using Union queries but I don't quite understand them. Are they the way I want to go and if there any other way?

Thanks

 

[KenReay]

Hi

UNION query is the way to do it

A union query is simply a means to concatinate the results from two or more SELECT queries

Certain rules apply eg each select must return the same number of columns, and of same type

So you need somthing like

SELECT Option, Description FROM tblOPtions
UNION
SELECT "*" As Option, "<All>" As Description FROM tblOPtions
ORDER BY Description

as the rowsource of the combo, and use LIKE as your criteria in the query



Regards

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

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

UK

 

[mdweezer]

I have a union query that displays the results fine in the combo box.

SELECT TBL_Org.OrgNumber, TBL_Org.OrgName
FROM TBL_Org
UNION select 0, "All Orgs"
from TBL_Org
ORDER BY 2;

and then to assign the value of the combo box to a variable I use

OrgValue = Nz(cboOrg.Value, 0)

It works if I select a value from the combo box, but if I leave it alone because I do not want to use it as criteria for my report I got a "invalid use of the . (dot)....." error on the right side of that code (Nz(cboOrg.Value,0).

 

[KenReay]

Hi

I have already answered this in another post:

SELECT TBL_Org.OrgNumber, TBL_Org.OrgName
FROM TBL_Org
UNION select 0 As OrgNumber, "All Orgs" As OrgName
from TBL_Org
ORDER BY Orgname;

Have you tried that?

What was the result?




Regards

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

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

UK

 

[mdweezer]

Your code produces the same result.

If I leave the field alone and select nothing at all I get the .(dot) error, but if I select an option from the field it works...

So leaving it blank is the problem

 

[KenReay]

Hi

Why not make the combo box default to the all condition, so it is never blank, say

If cboOrg.ListItems > 0 Then
cboOrg = cboOrg.Column(0,0)
End If

Personally I would have used SELECT "*" As for the All condition and a Like in the criteria rather than an =

Regards

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

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

UK