using Dlookup in a report

[ruthcali]

i am using Access97.

On my form, i have a text box with the following control source:

=DLookUp("[rackloc]","tblOPCInfo","[BuildingSite]=Form.[BuildingSite] and [SystemID]=Form.[SystemID] and [OPCNumber]=Form.[OPCNumber]&quot

that works fine on my form. but, i tried the same thing on my report and had no luck.
on my report, my text boxes's control source is:

=DLookUp("[rackloc]","tblOPCInfo","[BuildingSite]=Forms![frmOPCCNET]![BuildingSite] and [SystemID]=Forms![frmOPCCNET]![SystemID] and [OPCNumber]=Forms![frmOPCCnet]![OPCNumber]&quot

That only works for the first value in my report. Then, that value is repeated for every line in the report! So every line in the report has the same data.

Does anyone know to get fix this?

thanks,
ruth

 

[ruthcali]

Silly me. i figured it out.

On my report, i shouldn't refer to my form, i should refer to my report!!

i changed the control source of my report's text box to:

=DLookUp("[rackloc]","tblOPCInfo","[BuildingSite]=[Reports]![rptCnet]![BuildingSite] and [systemid]=[Reports]![rptCNET]![systemid] and [OPCNumber]=[Reports]![rptCNET]![OPCNumber]&quot

and it works!

But, it's strange that i didn't need to add any formattingex: & and "'&quot

so, i didn't need to type:
"[BuildingSite]='"&[Reports![rptCnet]![BuildingSite] & "'"

it works with just:
"[BuildingSite]=[Reports]![rptCnet]![BuildingSite]

does anyone know why?

 

[Guest_imported]

I still can't get my report to lookup a description from a table. =DLookUp("[res description]","[res codes]","[res code]=[Reports]![RES]![res code]&quot

 

[ruthcali]

Strange, your code looks right.

so res codes is the name of your table?

and in your table, you have a field called res code.

and res code is the text box in your report?

double check your field names. if you would like, you can email me your database and i'll try to help.
ruth.jonkman@wcom.com

 

[jfhewitt]

I forget the specifics, but somewhere I recall that spaces in table and field names is a no-no.