calling a function from a report 1

[jmstarbuck]

I need to write a fuction to determine how to present homeowner names on a report. There is a possibility of one or two homeowners and one of them may be deceased. So the possibilities are:

John Smith & Jane Smith
John Smith (d) & Jane Smith
John Smith & Jane Smith (d)
John Smith
Jane Smith

I am able to write that code, but I need to access the logic for the homeowner names from about 30 pre-existing reports. Is there a way that I can write this function just once and call it from the reports?

Any guidance would be greatly appreciated.

Janice

 

[dhookom]

We don't know what your data looks like or how you want the results to display.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jmstarbuck]

I'm new to this so please bear with me.

My data is HOLastName1, HOFirstName1, HODeceased1, HOLastName2, HOFirstName2, HODeceased2

I would like to write a function that takes all of these fields as inputs and outputs 1 text field: Homeowners formatted as the possibilities above.

My real problems are:
1. I don't know where to put the function
2. I don't know how to reference the function from a report field.

Thanks for your reply.

 

[dhookom]

Is it possible the name field might be Null? Is HOLastName1 always valued and the "2" fields just sometimes valued? What is the data type of HODeceased? Is it a yes/no or date or text or what?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jmstarbuck]

No, the name field should never be null. HOLastName1 always should have a value. The 2 fields can be null. Both homeowners cannot be deceased. The deceased fields are True/False.

I have a similar public function that I am able to use from a form, but I can't figure out how to call it from a report that is run while the form is active.

On a form text field I set the Control Source =HONames([LastName1],[FirstName1],[DeceasedHO1],[LastName2],[FirstName2],[DeceasedHO2])

Here's the code I have for HONames

Public Function HONames(LastName1, FirstName1, DeceasedHO1, LastName2, FirstName2, DeceasedHO2) As String
Dim Names As String
Names = ""
If Not IsNull(LastName1) And Not DeceasedHO1 Then
    Names = FirstName1 & " " & LastName1
End If
If Not IsNull(LastName2) And Not DeceasedHO2 Then
  If Not IsNull(Names) And Names <> "" Then
     Names = Names & ", "
  End If
  Names = Names & FirstName2 & " " & LastName2
End If
HONames = Names
End Function

 

[dhookom]

Since you seem to understand how to create a function, all you need to do is set the control source of a text box in the report to the function like:
=HONames(HOLastName1, HOFirstName1, HODeceased1, HOLastName2, HOFirstName2, HODeceased2)

You would need to modify the function to add the (d) if deceased and make a couple other small changes.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jmstarbuck]

I have tried to do just that in a report and I get a popup for HONames when I try to run the report.

I've also tried to reference the form specifically

=[Forms]![Application_Form].[HONames]([LastName1],[FirstName1],[DeceasedHO1],[LastName2],[FirstName2],[DeceasedHO2])

and I end up getting #Name? on the report.

 

[dhookom]

Do you have all the fields in your report's record source? Did you create a function in a standard module?

How did your field names change? Your earlier post stated "My data is HOLastName1, HOFirstName1, HODeceased1, HOLastName2, HOFirstName2, HODeceased2" and more recently you used "[LastName1],[FirstName1],[DeceasedHO1],[LastName2],[FirstName2],[DeceasedHO2]".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jmstarbuck]

I do have all the fields in the report's record source.
I hadn't created the function in a standard module, but I have since moved it to one - Module2. I still get the HONames popup. I really thought that change was going to do it. The function continues to work from my form.

I apologize about the variable names. I was being general at first and got more specific to answer questions.

 

[dhookom]

What is your exact control source as well as your exact function?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jmstarbuck]

Control Source
=[HONames]([LastName1],[FirstName1],[DeceasedHO1],[LastName2],[FirstName2],[DeceasedHO2])

 Public Function HONames(LastName1, FirstName1, DeceasedHO1, LastName2, FirstName2, DeceasedHO2) As String
Dim Names As String
Names = ""
If Not IsNull(LastName1) And Not DeceasedHO1 Then
    Names = FirstName1 & " " & LastName1
End If
If Not IsNull(LastName2) And Not DeceasedHO2 Then
  If Not IsNull(Names) And Names <> "" Then
     Names = Names & ", "
  End If
  Names = Names & FirstName2 & " " & LastName2
End If
HONames = Names
End Function

 

[jebry]

Hi!

Create a query using your function and base the report on the query.



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[dhookom]

You don't place brackets around a function name:
=HONames([LastName1],[FirstName1],[DeceasedHO1],[LastName2],[FirstName2],[DeceasedHO2])


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jmstarbuck]

Thank you, Duane!

The brackets were being automatically added, but when I removed them it worked. Thank you so much.