Subreport vs. DLookUp

[alvintse]

Hi,
I have a report that include many fields (around 40) that is not easy to join them and put in the report as desire.
So, some of the fields I use subreport and some of the fields I use DLookUp. I understand either way will slow down the performance. My question is should I use subreport or DLookUp if I couldn't aviod it?

Thanks.

 

[SeeThru]

Personally I tend to avoid Dlookup as it is fiddly and easy to break, especially if you may have quotes in any of the arguments.

Subreports give you more options, and you can use cangro and canshrink to make empty subreports vanish.

 

[Pampers]

cant you not make one query with the desired fields in it?


Pampers.

You're never too young to learn

 

[alvintse]

Yes, I can make a query to include all the desire fields. The problem is for that subreport, I have to put 5 check box there. Here is the sample data return from the query.

ID reason
1 2
1 4
1 5

In the subreport, the layout is:

checkbox1 description
checkbox2 description
checkbox3 description
checkbox4 description
checkbox5 description

In the report, I have to display all 5 checkbox whether they are checked or not.
Based on the sample data, checkbox 2,4,5 will checked while 1 and 3 is unchecked.

If I simply put the fields in detail section, only 2,4,5 will display.

If I group by ID and put the checkbox at either groupheader or groupfooter, only the first or last checkbox will display.

I also try created a self join query like:

SELECT ID,yrID, IIf(ID In (select id from sameTable where reason=1 and YrId=2005),-1,0) AS check1,............as check2,.........

So, the data return like this:

ID check1 check2 check3 check4 check5
1 0 1 0 1 1
1 0 1 0 1 1
1 0 1 0 1 1

Then, this is OK for me to put all 5 checkbox in the groupheader or footer and assign the data source for each checkbox associate with the those fields (ie check1, check2 ...). Since all the record with the same ID will be the same, it doesn't matter which record to read.

The query returns the correct data and the report is working properly. But some of the buttons not working any more after I created the report. That is because when I deleted the report, everything working again.

That is what I have tried and finally I decided to use the DLookup().

 

[Pampers]

If it works, stick to it!


Pampers.

You're never too young to learn