NitrO

Hi all,

I might just be being dense, but does anyone know how to suppress duplicate data within the detail section of a data report.

Example:
I have a recordset which contains -
CustName, Cust ID, PurchDate, Item1, Item2, Item3, Item4
CustName, Cust ID, PurchDate, Item1, Item2, Item3, Item4
---- " ------
etc, etc....

I need to suppress CustName & ID, so I only have 1 occurance showing.

Any help or suggestions greatly appreciated.

NitrO

FrogMan

Hi

Do you mean your report needs to look like something this

CustName Cust ID

PurchDate,Item1,Item2,Item3,Item4

or are you actually trying supress duplicate data ?

NitrO

FrogMan,

Yeah, with CustName & ID only appearing once, with all other data listing underneath.

NitrO

FrogMan

Are you using DataEnvironment ?

NitrO

No but I can if it makes it easier.

I should also point out that I am using an sql query, which uses a variable populated from a combo box on a form.

FrogMan

Yep, DataEnvironment will make it easier. As for using a variable SQL query, it won't make any difference to the layout of the report.

Ok, the best way I have found (which doesn't mean it's the only way) is to set up a query with a child query in the dataenvironment.

Set the first sql query to just pass CUST NAME and CUSTID then from this query set a child query and select all fields.

From there you can set the DataReports datasource to the dataenvironment and set the datamember to the SQL query.

Then in the datareport right click the grey square in the top left hand corner and select retreive structure.

This will set up relevent sections for your report based on the query you have set up. You can then put CUSTNAME and CUSTID in the group header section and the rest of the information in the detail section.

Hope this helps

NitrO

Thanks, worked a treat, however how do I feed in the query's parameter?

i.e. WHERE CustName = ' " & theName & " '

I've tried the parameters tab of the commands properties but it is all disabled and will not allow me to add anything?

NitrO

FrogMan

Hi

Ok, it's best to do it at run time.

Firstly I would get the structure of the SQL command. The reason for this is so you can copy it into your programme. I do something like this.

With DE1
Open "c:\temp\sql.txt" For Output As #1
Print #1, .Commands("sqlCheckDates").CommandText
Close #1
End With

This creates a text file with your sql in. You will then see that heirarchical queries are created using SHAPE.

You can then discard this code.

Then you can store the sql in a variable and add your parameters where you like.

You then set the CommandText to your variable. Something like this :
strSQL = YourSQL
With DE1
.Commands("sqlCheckDates").CommandType = adCmdText
.Commands("sqlCheckDates").CommandText = strSQL
.Commands("sqlCheckDates").Execute
End With

Hope this helps

NitrO

Thanks, for the help Frogman

It worked a treat.

Cheers,

NitrO