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)
My solution:
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:
=CountryIndicator([txtCompanyHomeCountry],[txtEmployeeHomeCountry])
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)
As String
On Error GoTo CountryIndicator_Error
If strCompany = strEmployee Then
[Reports]![REPORTXXX]![txtCountryIndic] = ""
Else
[Reports]![REPORTSXXX]![txtCountryIndic] = "**"
End If
On Error GoTo 0
Exit Function
CountryIndicator_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure CountryIndicator of Module UDF"
End Function
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
Washington, DC
