Mr. Rost,

Experience: Beginner plus. Used to program in RPG on A/S 400 (7 years ago).

I have a form with a command button that runs a query. On the same form is a combobox with a values list.  The combobox value selected by the user is passed to the same query as a criteria to limit the records returned in the query.

In the query there are 6 fields that are selected from a single table. One of these fields need to be evaluated for the data it contains for each record returned by the query.  I would like to create a user defined 7th field to house the output of the following:

Field_one selected from the table is a text field 10 wide to be evaluated for what it contains

I have about 20 unique possible values of data in this field.  As an example in field_one is the value "35alwexxxx" of record one. Record two as an example is "85qrtyxxxx"

I want to figure out the best way to add a user defined field to the query and have it populated for each record using the following criteria:

When left(field_one),2 = "35","Inventory_exceeded" populated in field 7(user defined)
when left(field_one),2 = "85","Inventory_sufficient" populated in field 7(user defined)

There are 20 different outcomes of data that will be in field 7, each value based on the test of field_one

I think I can use an iif statement but with 20 different tests of the first two values in field_one, each to return a different value in my user defined field 7, I am hoping for an easier way to perhaps use a case statement or some other function that is embedded in a module, or is somewhere else in the Db and will be called record by record when the query is executed and have the data I want populated into the user defined field 7.

Can you please tell me the best way to do this and to help me understand how to create user defined functions, and where they are stored/called from.

Thanks in advance,

Jayh

---

Answer from Richard Rost:

You're right in that a user-defined function would be the best way to go. I cover them in depth in my Access tutorials, but in a nutshell, here's what you do:

First, go to the MODULES section and create a new module. In here, you'll create a global function, something like this:

Public Function MyFunction (X as Integer) As String
   If X = 1 then
      MyFunction = "Hi There"
   Else
      MyFunction = "No Way!"
   End If
End Function

This function will simply return the string value based on the integer value X you send it. A 1 will yield "Hi There" and anything else "No Way!"

Easy enough?

Now you can call this global function from anywhere else in your database (queries, forms, reports, etc.) In a query you would say:

NewField: MyFunction(SomeValue)

In a form field you would say:

=MyFunction(SomeValue)

Got it? Again, it's not HARD, but you have to know the syntax. In your case, you can just put all of your 20 combinations as an If/Then statement in your VBA code.

Again, I cover this in greater depth in my advanced Access VBA programming tutorials, but hopefully this will get you started.