How to call a Function from a Query? 1

[ITProg]

What is the proper syntax for a function in a module to return a value to a query? I would like the function to perform a series of nested IIf statements that are too numerous to perform in a sql statement (Access returns an error). The function needs to reference fields from the table being used in the query.

query1 has a field with expression- clinphys: clinic()
which calls the function clinic()

The function in the module is declared as below([physexam] would be a field in the table that I would like to compare):

public function clinic() as string
if [query1].[physexam]=-1 then
clinic="yes"
else
clinic = "no"
end if

When I use this function, I do not get an error message, but I do not get any values returned.

Has anyone done anything like this before? Thank you.

 

[dhookom]

Your query should accept an argument of the field:
Public Function Clinic(pBooPE as boolean) as String
'your code here
If pBooPE = -1 Then
'yada yada
End

Your query expression would be like
ClinicValue: Clinic([PhysExam])

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]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[ITProg]

Can I pass a table over, rather than one field? I would like to compare multiple fields in my function. If so, what datatype would I define it as?

 

[dhookom]

You can pass multiple fields. You could also pass a table name.

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]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[ITProg]

The function works great passing one field. When I put the table name as the argument to pass to the function, then run the query, I get the parameter box - it is looking for some value. I tried typing the table name in a variety of ways, but it does not seem to work. What am I missing?

 

[PHV]

Passing the table name (as string) to an UDF (user defined function) has, in my opinion, a meaning provided you pass the PK (primary key) value too, letting the function get the current row fields by opening a recordset.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

ITProg,
What do you want to do with the table inside the function? Is the "table to pass" not a table that is in your original query?

Maybe you need to tell us what you are attempting to accomplish and what you are working with.

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]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[ITProg]

The query is based on 3 tables. tblReviews, tblStaff, tblLocation. The query is checking compliance on criteria being prepared for a report. I would like to create a function because I have 17 fields that relate to eachother to compare in an IIf statement. I tried nesting the fields in an IIf in an expression as a field in the query, but received an error message that it was too complex. I would like to pass the table tblReviews over to the function Clinic() to perform the IIf statement comparisons on the 17 fields, then pass back a value of compliant or not. tblReviews is linked to tblStaff by an id number. tblStaff is linked to tblLocation by a location code. The query asks for a location parameter and begin/end date parameter to narrow the data selection.

 

[dhookom]

Is there a reason why you don't pass the 17 fields to the function? This would be much more efficient than creating a temporary table or query recordset in your code.

I think you may have an issue with normalization. The red flags for me are "comparisons on the 17 fields" and a field named "physexam".

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]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[ITProg]

I was hoping to save some typing by passing the table over. I did as you suggested and passed the 17 fields over to the function and it worked fine.

Thank you for all the help!