Do I need a 'Function' in my query?

  • Thread starter Thread starter Malcolm Potter
  • Start date Start date
M

Malcolm Potter

Hi all

One of the queries within my database contains seven fields, all derived
from one table. I would like to add an eighth field to this query which
returns one of three strings ("Above", "Equal To" or "Below") dependant upon
the values of the numbers appearing in two of the other fields. Will I need
to construct a 'Function' to do this, using something like an
'If...Then...Else' routine and, if so, how do I call up this function within
the query?

I am using Access 2002.

Regards

Malcolm
 
Dear Malcolm:

You probably don't need a function to do this. There is some readily
available syntax within the query language that will handle it just as
easily.

Let's say the two columns to compare are named ColumnA and ColumnB.

You can write the new column as:

NewColumn: IIf([ColumnA] < [ColumnB], "Below", IIf([ColumnA] =
[ColumnB], "Equal To", "Above"))

If this wraps you should paste it into an editor and remove the
return, then paste it into the design grid for the new column.

Please let me know how this worked for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Apologies.



I fear that in generalising and over simplifying I might have misled the
group in my original posting. I think that I will need a block of code
(function/procedure?) to enable me to achieve the required results, since
ultimately this code will have to do a number of things. To be a little more
specific regarding one of the requirements for this code:



Five of the seven fields in the query are 'Player', 'PlayersBand', Partner',
PartnersBand' and 'Points'. A player or partner will be banded either 'A',
'B' or 'V' if he/she is a Visitor to our club and the results for players
with these bands must be processed separately. For example, for both 'A' and
'B' band players their best six results (points) out of nine will be
counted, however 'A' band players must play at least four of their best six
results with 'B' band players, whereas 'B' band players can play their
matches with players of any band.



If I do need a function or procedure of some description to give me the
required answers, is it possible to run it from within my query and if so
how?



I hope the above is a little more helpful.



Regards



Malcolm
 
Back
Top