I stumbled across this newsgroup having a similar problem and wanted
to share my approach. The situation was I needed to conditionally
populate a text box on a detail section of a report with an indicator
(in this case two asterisks), to notate that certain detail records
stood out from others. Specifically, the goal was to examine a
company's country where they did business and provide a roster of
employees. Employees residing outside of the company's home country
of business where to be highlighted on the report. For instance:
Company Name and Location: ABC Company, New York, NY USA
Name Date of Birth City Country
** John M. 3/17/1946 Rome Italy
Joe T. 2/10/1965 Topeka USA
** Indicates member resides outside USA (footer notation)
1) Create an unbound text box on the report to hold the asterisks or
hold an empty string. I called this text box txtCountryIndic.
2) Within the detail section of the report, create a second text box
who's control source is set to a function call. The function call
assigned to the text box's control source looked like this:
You can call this 2nd text box whatever you like since its only
purpose is to hold the code in its control source to pass to the
function. I use this indirect method since if I tried to do this in
txtCountryIndic, it would become a bound text box and I wouldn't be
ab;e to assign values to it.
3) Then I wrote a simple function that was:
Function CountryIndicator(strCompany As String, strEmployee as String)
On Error GoTo CountryIndicator_Error
If strCompany = strEmployee Then
[Reports]![REPORTXXX]![txtCountryIndic] = ""
[Reports]![REPORTSXXX]![txtCountryIndic] = "**"
On Error GoTo 0
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure CountryIndicator of Module UDF"
Hope someone can get some mileage out of this. I am sure there is a
more elegant way of doing this, but this works for me.
- Dave Ferguson
I'm working on something similar... What if you wanted to call the values of strCompany or strEmployee based upon if one of them were null.
Don't exactly expect an answer through here but figured I'd give it a shot.