Storing and recalling stock phrases

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to send faxes and other reports to our vendors. A number of phrases
appear in several different reports. The choice of phrases is dependent on
things like whether an expiration date had passed or not, so I have
constructed a query with a calculated field containing a nested IIf
statement, along the lines of:
IIf([ProductType] = "Supply" And [ExpDate] < Date(),"Your survey will be due
soon",IIf([ProductType] = Supply and [ExpDate] > Date(),"Your survey is past
due","Please sign below and return"))
It's more complex than that, with longer phrases that I have shown and with
fields such as the actual expiration date interspersed throughout the
message, but that is the idea. So far it works, but I just had to add
another condition, and seem to have maxed out on the length of the IIf
expression. I would like maybe to have some standard phrases stored in a
table so that I can just refer to something like [ExpDateSoon] in the IIf
statement instead of "Your survey will be due soon", but am unsure how best
to proceed. If each phrase is a separate record I can't think how I would
refer to it.
 
If you do not need to retain these phrases within your database - you could
set up a series of conditional fields within the report itself, and set the
visible properties of these fields to 'no'...

eg.

TextA IIf([ProductType] = "Supply" And [ExpDate] < Date(),"your
phrase","")
TextB IIf([ProductType] = Supply and [ExpDate] > Date(),"your phrase","")
etc..

and then set the control of the relevant field (where you want to display
the result) as something like
=[TextA] & [TextB] & ...etc...

HTH
 
Thanks for replying, and for your suggestion. I should have mentioned that I
do not need to store the phrases. I followed your suggestion, except that I
used calculated fields in the report's record source query instead of hidden
text boxes, but the idea is the same. I had done that sort of thing in the
past on a
limited basis, but this was a bit more complex, so I wondered if there was a
better way. I also experimented with a table with one record and as many
memo
fields as I needed phrases. By adding that table to the query I could simply
reference the phrases in the IIf statement (and other functions) as needed.
The advantage of that approach would be that I do not need to add the fields
individually to multiple queries. The disadvantage is that I cannot see the
actual phrases in query design view. I have named the calculated fields in
the query the same as the fields in the table phrases. If I decide the table
approach is preferable I can remove the calculated fields from the query and
put the table into the query. The IIf statements will contain the same field
references in either case.


Ted said:
If you do not need to retain these phrases within your database - you could
set up a series of conditional fields within the report itself, and set the
visible properties of these fields to 'no'...

eg.

TextA IIf([ProductType] = "Supply" And [ExpDate] < Date(),"your
phrase","")
TextB IIf([ProductType] = Supply and [ExpDate] > Date(),"your phrase","")
etc..

and then set the control of the relevant field (where you want to display
the result) as something like
=[TextA] & [TextB] & ...etc...

HTH







BruceM said:
I need to send faxes and other reports to our vendors. A number of phrases
appear in several different reports. The choice of phrases is dependent on
things like whether an expiration date had passed or not, so I have
constructed a query with a calculated field containing a nested IIf
statement, along the lines of:
IIf([ProductType] = "Supply" And [ExpDate] < Date(),"Your survey will be due
soon",IIf([ProductType] = Supply and [ExpDate] > Date(),"Your survey is past
due","Please sign below and return"))
It's more complex than that, with longer phrases that I have shown and with
fields such as the actual expiration date interspersed throughout the
message, but that is the idea. So far it works, but I just had to add
another condition, and seem to have maxed out on the length of the IIf
expression. I would like maybe to have some standard phrases stored in a
table so that I can just refer to something like [ExpDateSoon] in the IIf
statement instead of "Your survey will be due soon", but am unsure how best
to proceed. If each phrase is a separate record I can't think how I would
refer to it.
Click to expand...
Click to expand...
 
Back
Top