Need report group header code alterantive to this:

Hi all,

I have the following in a report's group header control (textbox) in the
control source property:

=IIf([HasData],[CategoryType],"No Matching Group")

It does exactly what it is suppose to do, but it requires me to add a second
field (CategoryType....a text datatype field)from the lookup table
'categories' to the query, and I'd rather not. It's a query that I also use
to populate my main form and I hate having to load it up with tables and
fields from those tables just used in reports. It's not just this one
instance, there are several reports and many of them implement this same
solution requiring many fields like this one to be included in the query, and
several additional tables.

I guess I could just copy the query and use one copy for the reports and one
for the form, but I'd like to think this could be solved using just one query.

Any help would be appreciated.

Thanks,
CW

What is [HasData]?
Post the SQL of the query that returns [CategoryType]

--
Dave Hargis, Microsoft Access MVP


"Cheese_whiz" wrote:

> Hi all,
>
> I have the following in a report's group header control (textbox) in the
> control source property:
>
> =IIf([HasData],[CategoryType],"No Matching Group")
>
> It does exactly what it is suppose to do, but it requires me to add a second
> field (CategoryType....a text datatype field)from the lookup table
> 'categories' to the query, and I'd rather not. It's a query that I also use
> to populate my main form and I hate having to load it up with tables and
> fields from those tables just used in reports. It's not just this one
> instance, there are several reports and many of them implement this same
> solution requiring many fields like this one to be included in the query, and
> several additional tables.
>
> I guess I could just copy the query and use one copy for the reports and one
> for the form, but I'd like to think this could be solved using just one query.
>
> Any help would be appreciated.
>
> Thanks,
> CW

Hi Dave,

Thanks for the reply. Sorry I got side-tracked. I hope you still see this.

Here's the sql from the query in question:

SELECT Issues.ID, Issues.FileTypeID, Issues.NamedParty,
IIf(Len([Entities_NamedParty].[FirstName])>0,Trim([Entities_NamedParty].[LastName]
& ", " & [Entities_NamedParty].[FirstName] & " " &
[Entities_NamedParty].[MiddleInitial]),[Entities_NamedParty.LastName]) AS
NamedPartyName, Entities_NamedParty.IsEntity, Issues.AGCaseNo, Issues.CaseNo,
Issues.Title, Issues.OpenedDate, Issues.OpenedTime, Issues.OpenedBy,
Issues.RelatedEntity,
IIf(Len([EntitiesRelatedEntity].[Firstname])>0,Trim([EntitiesRelatedEntity].[LastName]
& ", " & [EntitiesRelatedEntity].[FirstName] & " " &
[EntitiesRelatedEntity].[MiddleInitial]),[EntitiesRelatedEntity].[LastName])
AS RelatedEntityName, Issues.Status, Status.StatusType, Issues.Category,
Categories.CategoryType, Issues.SubCategory, SubCategories.SubCatName,
Issues.Priority, Priority.PriorityLevel, Issues.DueDate, Issues.DueTime,
Issues.txtLastMod, Issues.ClosedDate, Issues.txtLastModBy
FROM Entities AS Entities_NamedParty RIGHT JOIN (Priority RIGHT JOIN (Status
RIGHT JOIN (Categories RIGHT JOIN (SubCategories RIGHT JOIN (Entities AS
EntitiesRelatedEntity RIGHT JOIN Issues ON EntitiesRelatedEntity.EntityID =
Issues.RelatedEntity) ON SubCategories.SubCatID = Issues.SubCategory) ON
Categories.CategoryID = Issues.Category) ON Status.StatusID = Issues.Status)
ON Priority.PriorityID = Issues.Priority) ON Entities_NamedParty.EntityID =
Issues.NamedParty;

Basically, the query includes several joins to tables that are lookup
tables, where combo boxes on my form allow users to choose a text name but
then the records store the associated pk integer. I don't need those tables
joined in the query, though, since I am using a select query to populate the
combo box(es) on my main form. So, instead of having 'category type' dragged
down from the category table which is joined to my main table in the query, I
want to just eliminate the category table from the query. For the main form,
that's fine. No problems. However, for the reporting, it messes up that
code I put in the first post.

What is [HasData]? I am not really sure. The original code is something
someone on this board suggested that would, in the event there was no value
in a particular field, I could provide an alternate display (as opposed to
just leaving it blank which doesn't work well when you are talking about
group headers). I just always interpreted it to mean that if there was data,
use it, if there wasn't, use the alternative provided in the second part of
the IIf.

I do appreciate your help. The problem, stated again, is that without those
lookup tables in the query, I don't have a single text-based field that I can
use as the first part of the IIf statement in my original post.

CW



"Klatuu" wrote:

> What is [HasData]?
> Post the SQL of the query that returns [CategoryType]
>
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Cheese_whiz" wrote:
>
> > Hi all,
> >
> > I have the following in a report's group header control (textbox) in the
> > control source property:
> >
> > =IIf([HasData],[CategoryType],"No Matching Group")
> >
> > It does exactly what it is suppose to do, but it requires me to add a second
> > field (CategoryType....a text datatype field)from the lookup table
> > 'categories' to the query, and I'd rather not. It's a query that I also use
> > to populate my main form and I hate having to load it up with tables and
> > fields from those tables just used in reports. It's not just this one
> > instance, there are several reports and many of them implement this same
> > solution requiring many fields like this one to be included in the query, and
> > several additional tables.
> >
> > I guess I could just copy the query and use one copy for the reports and one
> > for the form, but I'd like to think this could be solved using just one query.
> >
> > Any help would be appreciated.
> >
> > Thanks,
> > CW

Hi Dave,

After looking at this again, I think I've found the solution. The bottom
line is I just confused myself by thinking about it. Changing the control on
the report to a combo box solved the issue.

Thanks again,
CW

"Klatuu" wrote:

> What is [HasData]?
> Post the SQL of the query that returns [CategoryType]
>
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Cheese_whiz" wrote:
>
> > Hi all,
> >
> > I have the following in a report's group header control (textbox) in the
> > control source property:
> >
> > =IIf([HasData],[CategoryType],"No Matching Group")
> >
> > It does exactly what it is suppose to do, but it requires me to add a second
> > field (CategoryType....a text datatype field)from the lookup table
> > 'categories' to the query, and I'd rather not. It's a query that I also use
> > to populate my main form and I hate having to load it up with tables and
> > fields from those tables just used in reports. It's not just this one
> > instance, there are several reports and many of them implement this same
> > solution requiring many fields like this one to be included in the query, and
> > several additional tables.
> >
> > I guess I could just copy the query and use one copy for the reports and one
> > for the form, but I'd like to think this could be solved using just one query.
> >
> > Any help would be appreciated.
> >
> > Thanks,
> > CW