Queries in Report 1

[lars7]

Hi,
I would like to add a field from a second query to a Report. I tried through the expression builder but I keep getting an error message.

 

[Remou]

What have you got so far, even if it does not work?

 

[lars7]

Hi Remou,

From the expression builder I put:

=[qrycost1_2]![CountOfTrust]

In the text box.

Before the Report opens there is a message box:

"Enter the Parameter Value"
qrycost1_2

But when it opens there is only "error" is the text box.

 

[Remou]

How about:
=DlookUp("[CountOfTrust]","[qrycost1_2]")

 

[lars7]

The report is opening ok but there is only 0 in all the rows instead of:

CountOfTrust
0
0
654
277
162

 

[Remou]

If there is one particular answer you want, you can use:
[tt]=DlookUp("[CountOfTrust]","[qrycost1_2]","[Field Name]='Text'")[/tt]
(You do not need the single quotes for a numeric field. By-the-by there is a FAQ on DlookUp.)

If you want all the rows from the query, you probably need a subform.

 

[lars7]

Hi again Remou,
I'm not sure what to put in the [field name] but i will go to the faq on Dlookup like you suggested thanks.

 

[Remou]

Say that CountOfTrust is grouped on Area.

[tt]Area CountOfTrust
10 654
20 277[/tt]
Then, for a numeric field:
=DlookUp("[CountOfTrust]","[qrycost1_2]","[Area]=20")

[tt]Area CountOfTrust
NE 654
SW 277[/tt]
Or for a text field:
=DlookUp("[CountOfTrust]","[qrycost1_2]","[Area]='SW'")

 

[lars7]

I'm sorry about that but i understand now:

=DLookUp("[CountOfTrust]","[qrycost1_2]","[trust]='Text'")

this is the way it looks now.

But there is nothing showing in the text box.

 

[lars7]

Sorry i really do get you this time.

=DLookUp("[CountOfTrust]","[qrycost1_2]","[trust]='Yorkhill'")

but it is showing the total for Yorkhill next to the other Areas which is not what i need.

 

[Remou]

Originally you mentioned a field, then you posted a list, so I am getting confused, I'm afraid. What does the result of the query look like? A few lines would be good. And what do you want to show in your report? I don't quite get
showing the total next to the other areas

 

[lars7]

Hi Ramou,
I was trying something to solve a problem I have but I don't think it's possible. I've tried to write it down to explain it to you but it's very long winded and probably not very clear. Thanks for the Dlookup information this is the first time I’ve seen this and can think of a few things that I might be able to use it for in the future. Once again thanks for your time and patience with this matter.

Lars7.

 

[Remou]

You are welcome. Good luck with your project.

 

[lars7]

Hi Remou,
I got close enough to what I wanted by using the dlookup in the report footer and a better understanding of the function by reading the Faq's. Thanks again for your help with this.