Printing data from another table in report footer

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

piginapen - 29 Sep 2006 19:17 GMT

I have several reports that print a message in the report footer. The
message is the same for all reports. Part of the message needs to change
based on certain events. Now I edit each report and change the message text
before generating the reports. I would like to put the variable parts in a
table and pull from the table to insert into the message.

For example the message is "as of VN #32"

I would like to put the "32" in a table so I would only have to change it in
one place instead of in each report. The "32" is not part of any query or
parameter in the data used to generate the reports.

How can I accomplish this?

Thanks

Reply to this Message

Duane Hookom - 29 Sep 2006 19:55 GMT

If your "in a table" contains only one record, you can simply add the table
to your report's record source. If the table contains more than a single
record you can still do this only add a criteria to include only a single
record from the table.

Alternatives are to use DLookup() or a subreport.

Signature

Duane Hookom
MS Access MVP

>I have several reports that print a message in the report footer. The
> message is the same for all reports. Part of the message needs to change
[quoted text clipped - 14 lines]
>
> Thanks

Reply to this Message

fredg - 29 Sep 2006 19:58 GMT

> I have several reports that print a message in the report footer. The
> message is the same for all reports. Part of the message needs to change
[quoted text clipped - 11 lines]
>
> Thanks

You're going to change the table value each time you run the reports?

If the table has just one record, in an unbound control on the report:
="as of VN #" & DLookUp("[FieldnName]","TableName")

If the table has more than one record:
="as of VN #" & DLookUp("[FieldnName]","Tablename","[SomeField] = some
number criteria")

See VBA help
DLookUp + Where clause + restrict data to a subset of records

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

piginapen - 29 Sep 2006 20:31 GMT

Thanks......works like a charm......

>> I have several reports that print a message in the report footer. The
>> message is the same for all reports. Part of the message needs to change
[quoted text clipped - 13 lines]
>See VBA help
>DLookUp + Where clause + restrict data to a subset of records

Reply to this Message

Marshall Barton - 29 Sep 2006 20:04 GMT

>I have several reports that print a message in the report footer. The
>message is the same for all reports. Part of the message needs to change
[quoted text clipped - 7 lines]
>one place instead of in each report. The "32" is not part of any query or
>parameter in the data used to generate the reports.

Use a text box for the message. The text box's expression
might be as simple as:

="as of VN #" & DLookup("yourfield", "your table")

Signature

Marsh
MVP [MS Access]

Reply to this Message

piginapen - 29 Sep 2006 20:32 GMT

y'all are great - it is amazing how things work when you use the right syntax.

thanks

>>I have several reports that print a message in the report footer. The
>>message is the same for all reports. Part of the message needs to change
[quoted text clipped - 6 lines]
>
> ="as of VN #" & DLookup("yourfield", "your table")

Reply to this Message