Dlookup question

[GaryPanic]

Guys , - first of all read the book - didn't help me (A2003 bible) now its probably me being the usla halfwit but..

i have a report (based on a qry) works fine - now i want to tweak my report

Outline
report
contract
Name
Amountdue
tax
total
section1
section2
section3
section4
section5
etc
this works no problem

now i want to split this down further
contract has suppliers to each

so contract A
Section1 Brown 75%
section1 Red 25%
Section2 Brown 50%
section2 Pink 25%
Section2 blue 25%
etc
and any mix
now i have the table set up to record contract section supplier and percentage

i had thought a dlook up would work on the first report
so run the report
critia Contract A
gets my report running - now i wanted to ethier put a sub report in that would capture the suppliers and times the various sectiosn by the percentages sot hat i get a total next to each line ..(pivot table )

any pointers

 

[nIGHTmAYOR]

first of all your question is missplaced , you should have asked that in Reports forum , i personnaly solve issues like yours using subqueries and joins in constructing the record source of the report for two main reasons :
1 - dlookup runs slow on reports and would render a beafy report dead
2 - dlookup looks up 1 value only .. not records ... not 1 record ... just 1 value
yet if you want to try it out running here is an example :
in an input box's / properties / control sourse type the following

<code>
Text
=Nz(Dlookup("[fieldnamehere]";"tablenamehere";"[yourwhereconditionfield]='" & Me![fieldnamepresentinreport].Text & "'");"")

Or
Numerics
=Nz(Dlookup("[fieldnamehere]";"tablenamehere";"[yourwhereconditionfield]=" & Me![fieldnamepresentinreport].Value);"")
</code>

also certain sql advanced functions can be used like :
"Count([fieldnamehere])"
Or
"Sum([fieldnamehere])"
Or
"Max([fieldnamehere])"
etc.

Best Regards,
nIGHTmAYOR

 

[GaryPanic]

thanks - re-reading the book and i have come to the same conculsion

i was trying to try and be clever - i am looking at cross queries now , but i think i am going to get the same problem
apoligies for wrong posting ....