Printing data from another table in report footer

[piginapen]

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

 

[Duane Hookom]

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.

 

[fredg]

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

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

 

[Marshall Barton]

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.

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

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

 

[piginapen]

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 - 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

 

[piginapen]

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 - 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")