report - field content options

[mark kubicki]

I have a report where the content of several of the fields is dependant upon
options entered by the user on a form.
for example some options might be...
_abbreviate description?
_include location?
_include notes?
_include contact info?

the information for the print options is stored in table: tblPrintOptions
the information for the records (all of the info) comes from a table:
tblData
the fields in tblData are named similar to the print options...

so the field might read [sic: plz ignore the code typos]:
if (dlookup(tblPrintOptions!abbreviate description),
left(description,50)+"",description) & _
if (dlookup(tblPrintOptions!include location), ("Location: " +
location), "") & _
if (dlookup(tblPrintOptions!include notes), (Notes: " + notes), "") &
_
if (dlookup(tblPrintOptions!include contact info), (Contact: " + contact
info), "")

there are seldom more than 100 records in the report, however, there are
(24+/-) print options in total

----> my question is: What would be the best approach?
....keep all the Dlookups? ...store the print options as variables on report
open?
....I can't combine the 2 tables in a query as there are no relationships
between the 2

any direction, no matter how general would be a HUGH help,
thanks in advance,
mark

 

[John Spencer]

What is the structure of tblPrintOptions? Is it a one record table with
multiple fields?

If so, you can add the table to your query with NO join. Then each row produced
would have all the options in the row and you could build your query with

SELECT SomeField,
, SomeOtherField
, IIF(tblPrintOptions.[Abbreviate Description], Left(tblData.[Description],50))
as Description
, IIF(tblPrintOptions.[Include Location], "Location:" & tblData.[Location],Null)
as Location
, IIF(tblPrintOptions.[Include Notes], "Notes:" & tblData.[Notes]) as Notes
, ...
FROM tblData, tblPrintOptions

This should be faster than doing all those lookups.

 

[Mark J Kubicki]

it does... thanks

What is the structure of tblPrintOptions? Is it a one record table with
multiple fields?

If so, you can add the table to your query with NO join. Then each row
produced
would have all the options in the row and you could build your query with

SELECT SomeField,
, SomeOtherField
, IIF(tblPrintOptions.[Abbreviate Description],
Left(tblData.[Description],50))
as Description
, IIF(tblPrintOptions.[Include Location], "Location:" &
tblData.[Location],Null)
as Location
, IIF(tblPrintOptions.[Include Notes], "Notes:" & tblData.[Notes]) as
Notes
, ...
FROM tblData, tblPrintOptions

This should be faster than doing all those lookups.

I have a report where the content of several of the fields is dependant
upon
options entered by the user on a form.
for example some options might be...
_abbreviate description?
_include location?
_include notes?
_include contact info?

the information for the print options is stored in table: tblPrintOptions
the information for the records (all of the info) comes from a table:
tblData
the fields in tblData are named similar to the print options...

so the field might read [sic: plz ignore the code typos]:
if (dlookup(tblPrintOptions!abbreviate description),
left(description,50)+"",description) & _
if (dlookup(tblPrintOptions!include location), ("Location: " +
location), "") & _
if (dlookup(tblPrintOptions!include notes), (Notes: " + notes), "")
&
_
if (dlookup(tblPrintOptions!include contact info), (Contact: " +
contact
info), "")

there are seldom more than 100 records in the report, however, there are
(24+/-) print options in total

----> my question is: What would be the best approach?
...keep all the Dlookups? ...store the print options as variables on
report
open?
...I can't combine the 2 tables in a query as there are no relationships
between the 2

any direction, no matter how general would be a HUGH help,
thanks in advance,
mark