Passing Fields to Function

[Tronsliver]

I'm new to this so I would appreciate any help I can get I have a table with four fields: Comp, Educ, BA, Rank

The overall function of the query is to check and see if a person is eligible for promotion.

The "Rank" field has one of six values: 01, 02, 03, 04, 05, 06

For each of these values there is criteria in each of the other fields that determine if the person is qualified.

For example: O1 must have a value of 7 in the comp field, the &quot;Educ&quot; field must have a &quot;N&quot; or > and the &quot;BA&quot; field must have a value <> &quot;J&quot;

If the above criteria is met then they are eligible for promotion so the result of the query would be &quot;Yes&quot; and if the criteria is not met &quot;No&quot;

Each other value in the &quot;Rank&quot; field would go thru similar checks with the &quot;Yes / No&quot; being displayed in the end.

My thoughts would be to build a function in a module but I don't know how to transfer the the data to the function so it could perform its operation.

I'm also open for other suggestions. Again I'm fairly new to this so thank you.

 

[RickSpr]

First, create a function in a standard module that takes the four columns as arguments and returns a Boolean (equivalent to a Yes/No field). Suppose you make the function declaration look like this:
Function Qualified(Comp, Educ, BA, Rank) As Boolean
(The names of the arguments are arbitrary. I used the field names for clarity.)

Now, in your query grid you need to use a new column (other than any that have fields from the input table). In its Field: row you would enter:
IsQualified: Qualified([Comp],[Educ],[BA],[Rank])
This gives you a &quot;derived column&quot; named IsQualified that contains a value returned by the function call for each record.

The basic idea here is that you can create derived columns in a query by using expressions. You name these columns by preceding the expression with a name and &quot;:&quot;. (If you don't give them a name, Access provides Expr1:, etc. as defaults.) The expression can refer to fields in the input table(s), to built-in VBA functions, or to functions you code in standard modules. Rick Sprague