How to display related fields from tables on report? 2

[sonso]

Coming from Paradox, I'm pretty confused about how to place a field on a report from a related table. I set up the links in the relationship screen, placed an unbound text box on the form, and then set the data source (using the elipsis) from a table+field in the list. It's a simple many->1 lookup (basicly looking up an account number description that relates to the account number in the underlying source table). I suppose I need a different source query, but now that the report is designed, can you change that?

 

[JoeEskimo]

Write a query with the apropriate tables and fields.

Open the Old report in design View


Double click in the top left corner of the report to open properties


Change RECORD SOURCE in properties to your new query using pulldown.

WaLa Done

 

[sonso]

Thanks, that did the trick. I can never understand why HumongoHard makes it so difficult for simple things.

 

[randysmid]

Hi,
There is an alternate way without having to make a new query. This is done using the DLookup function. It is a quick and easy way to add something to a report where you have a specific record to retrieve in a table. For instance, you may have a state code of "CA" appearing on the report, but you would really like to have the name "California" appear instead. Let's assume you have a table called "States", with "fldStatesCode" and "fldStatesName". The state code field on the report is called StateCode.

Here's what the DLookup will look like as the Control Source for your text box field:
=DLookup("[fldStatesName]", "States", "[fldStatesCode] = '" & [StateCode] & "'&quot

The StateCode needed to be encased by single quote marks because it is a string datatype. If this tip has been helpful, be sure to click on the "Mark this post as a helpful/expert post!".

Randy Smith
California Teachers Association