Adding All to a combobox

[swb147]

I have a form that contains 2 combo boxes, one that lists the Wards in the Area and the other contains the types of groups. I can generate a report listing a specific group in the Ward but need to be able to list a specific group in every Ward. I have added "All" to the Ward combo box along with the code in the row query

SELECT [tblWards].[WardID], [tblWards].[Ward] FROM [tblWards] UNION Select 0 ,"All Wards" From [tblWards] ORDER BY [Ward];

When select all Wards from the Combo box and run the report I get no results.

Can anyone throw any light on why this is the case?
:banghead:

 

[CJ_London]

Is your report filter or criteria set to bring all records through if the value of your combo is 0?

e.g.WHERE fldWard=forms.myformname.combo or forms.myformname.combo =0

 

[swb147]

The short answer to that is no. the next question is where should this go?

 

[CJ_London]

That depends on how you are doing it at the moment.

You said 'I can generate a report listing a specific group in the Ward' so it will go in the same place - it will either be the recordsource to your report or if you are using filters for docmd.openreport - it will go there

 

[swb147]

Well that seems to be the problem. I am not doing it at the moment, the record source for the Report says qryGroupType only

 

[CJ_London]

can you post the sql to your report recordsource

 

[swb147]

Sorry I am being really thick on this how do I do that?

 

[pr2-eugin]

Select the Report in Design mode and then on the Properties window you should have a Tab called Data, in which you will have a property Recordsource.. Just copy it and paste it here..

 

[swb147]

qryGroupType

That is what is in the Record Source under the data tab

wanted to post a screen shot but unable to

link to screenshot:
tlal dot webs dot com/screenshot

 

[pr2-eugin]

That says the Report gets its recordsource from a Precompiled Query in your DB.. Check under the Query category, there should be a Query named.. "qryGroupType".. Open the Query in Design view.. And get the SQL of the Query..

 

[swb147]

SELECT qryGroupType1.[Community Group], qryGroupType1.[Ward ID], tblWards.Ward, qryGroupType1.Type, qryGroupType1.TypeID
FROM tblWards INNER JOIN qryGroupType1 ON tblWards.WardID = qryGroupType1.[Ward ID];

 

[swb147]

I have 2 queries to run this report qryGroupType1 and qryGroupType. GroupType1 is the first query and GroupType is the 2nd here is the SQL code to GroupType1


SELECT tblGroups.[Ward ID], tblType.TypeID, tblType.Type, tblGroups.[Community Group]
FROM tblGroups INNER JOIN tblType ON tblGroups.TypeID = tblType.TypeID
WHERE (((tblGroups.[Ward ID])=Forms!frmComboWard!CboTypeWard) And ((tblType.TypeID)=Forms!frmComboWard!CboType));

 

[CJ_London]

It looks like this is where you need to modify -in the grouptype1 query

change this line

WHERE (((tblGroups.[Ward ID])=Forms!frmComboWard!CboTypeWard) And ((tblType.TypeID)=Forms!frmComboWard!CboType));

to this

WHERE (((tblGroups.[Ward ID])=Forms!frmComboWard!CboTypeWard OR Forms!frmComboWard!CboTypeWard =0) And ((tblType.TypeID)=Forms!frmComboWard!CboType));

 

[swb147]

Sorted!

Many Thanks CJ_London

That is fantastic your help and patience has been great.

 

[CJ_London]

Happy to help!