Data Report Designer and SHAPE command

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Tim (tim_at_mail.com)
Date: 12/30/04 

Date: Thu, 30 Dec 2004 16:49:16 -0600

I am creating a DataReport using the Data Report Designer (VB6) and using
the following SHAPE command to create a hierarchical recordset of
Client/FoodService records:

        mstrSQL = "SHAPE {SELECT * FROM Client} AS Client " & _
                        "APPEND ({SELECT FoodService.FoodServiceID, " & _
                                        "FoodService.ClientID, " & _
                                        "FoodService.FoodServiceDate, " & _
                                        "FoodServiceTypes.FoodServiceTypeDescription,
" & _
                                        "FoodService.NumberOfBags, " & _
                                        "FoodService.Comments FROM
FoodService " & _
                                "LEFT JOIN FoodServiceTypes ON
FoodService.FoodServiceTypeID=FoodServiceTypes.FoodServiceTypeID " & _
                                "WHERE FoodService.FoodServiceDate Between
#" & mstrStartDate & "# And #" & mstrEndDate & "# " & _
                                "ORDER BY FoodService.FoodServiceDate} AS
FoodService " & _
                    "RELATE ClientID TO ClientID) AS FoodService"

Currently, the report prints every client record whether or not there have
been any 'FoodServiceRecords' filed for that client. How can print only
those clients that have at least one FoodServiceRecord on file for the date
range specified? I guess I could loop through the recordset and delete those
client records with no child records, but is there something I can add to
the SHAPE command to do this?

Tim