 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / March 2005Storing and recalling stock phrases
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 > 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 [quoted text clipped - 13 lines] > to proceed. If each phrase is a separate record I can't think how I would > refer to it. 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. > 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 [quoted text clipped - 36 lines] > > to proceed. If each phrase is a separate record I can't think how I would > > refer to it. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.