Expression Builder Question

[Fran Zablocki]

I am trying to write an expression that converts one set of values to
another. There are two fields, STAGE and ADMIT_TYPE, which are used as
input criteria, and the final converted values are placed in the
APPSTATUS field. The rules for the conversion are as follows:

If STAGE = 600 then APPSTATUS = 03

If STAGE > 300 then APPSTATUS = 01

If STAGE > 300 and ADMIT_TYPE = AF or AFD or AP or APD, then APPSTATUS
= 02

If STAGE < 300 then APPSTATUS = ""
This is the expression I have typed up, but I havn't gotten the right
results:

IIf([STAGE]="600","03",IIf([STAGE]>"300","01",IIf([STAGE]>"300" And
[ADMIT_TYPE]= "AF" or "AFD" or "AP" or
APD","02",IIf([STAGE]<"300",""))))

I have limited experience using most of the code, and though I have
used the IIf statement before, I am not even sure if it is the right
one for this situation. If anyone has a better way to do it or sees
the error in the way I have attempted to do this, please let me know.
Or if this isn't clear enough please let me know and I will try to
explain further. I appreciate any help that you can give!

Fran Zablocki

[Fredg]

1)You have to repeat the field name for each and/or criteria.
2) If the criteria value (i.e. 600 03, etc.) are numbers, not text, do not
enclose them in quotes.
3) You left out a quote in [Admit_Type] = "APD"
4) Place the and/or's within parenthesis to make what you want clear to
Access.
IIf([STAGE]=600,3,IIf([STAGE]>300,1,IIf([STAGE]>300 And
([ADMIT_TYPE]= "AF" or [Admit_Type] = "AFD" or [Admit_Type] = "AP" or
[Admit_Type] = "APD"),2,IIf([STAGE]<300,""))))

5) You don't need to, but I would have moved IIf[Stage]<300 to the first IIf
statement,
or left it out altogether, as if it the other criteria isn't met, nothing
will appear anyway.
IIf([STAGE]=600,3,IIf([STAGE]>300,1,IIf([STAGE]>300 And
([ADMIT_TYPE]= "AF" or [Admit_Type] = "AFD" or [Admit_Type] = "AP" or
[Admit_Type] = "APD"),2,"")))

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.
"Fran Zablocki" <fe*@buffalo.edu> wrote in message
news:66**************************@posting.google.c om...

I am trying to write an expression that converts one set of values to
another. There are two fields, STAGE and ADMIT_TYPE, which are used as
input criteria, and the final converted values are placed in the
APPSTATUS field. The rules for the conversion are as follows:

If STAGE = 600 then APPSTATUS = 03

If STAGE > 300 then APPSTATUS = 01

If STAGE > 300 and ADMIT_TYPE = AF or AFD or AP or APD, then APPSTATUS
= 02

If STAGE < 300 then APPSTATUS = ""
This is the expression I have typed up, but I havn't gotten the right
results:

IIf([STAGE]="600","03",IIf([STAGE]>"300","01",IIf([STAGE]>"300" And
[ADMIT_TYPE]= "AF" or "AFD" or "AP" or
APD","02",IIf([STAGE]<"300",""))))

I have limited experience using most of the code, and though I have
used the IIf statement before, I am not even sure if it is the right
one for this situation. If anyone has a better way to do it or sees
the error in the way I have attempted to do this, please let me know.
Or if this isn't clear enough please let me know and I will try to
explain further. I appreciate any help that you can give!

Fran Zablocki

[Pablo]

fe*@buffalo.edu (Fran Zablocki) wrote in message news:<66**************************@posting.google. com>...

I am trying to write an expression that converts one set of values to
another. There are two fields, STAGE and ADMIT_TYPE, which are used as
input criteria, and the final converted values are placed in the
APPSTATUS field. The rules for the conversion are as follows:

If STAGE = 600 then APPSTATUS = 03

If STAGE > 300 then APPSTATUS = 01

If STAGE > 300 and ADMIT_TYPE = AF or AFD or AP or APD, then APPSTATUS
= 02

If STAGE < 300 then APPSTATUS = ""
This is the expression I have typed up, but I havn't gotten the right
results:

IIf([STAGE]="600","03",IIf([STAGE]>"300","01",IIf([STAGE]>"300" And
[ADMIT_TYPE]= "AF" or "AFD" or "AP" or
APD","02",IIf([STAGE]<"300",""))))

I have limited experience using most of the code, and though I have
used the IIf statement before, I am not even sure if it is the right
one for this situation. If anyone has a better way to do it or sees
the error in the way I have attempted to do this, please let me know.
Or if this isn't clear enough please let me know and I will try to
explain further. I appreciate any help that you can give!

Fran Zablocki

Please try this:
IIf([STAGE]=600,"03",IIf(([STAGE]>300 And ([ADMIT_TYPE]="AF" Or
[ADMIT_TYPE]="AFD" Or [ADMIT_TYPE]="AP" Or
[ADMIT_TYPE]="APD")),"02",IIf([STAGE]>300,"01","")))

Xiaolu

[Bob Quintal]

fe*@buffalo.edu (Fran Zablocki) wrote in
news:66**************************@posting.google.c om:

I am trying to write an expression that converts one set of
values to another. There are two fields, STAGE and ADMIT_TYPE,
which are used as input criteria, and the final converted
values are placed in the APPSTATUS field. The rules for the
conversion are as follows:

If STAGE = 600 then APPSTATUS = 03

If STAGE > 300 then APPSTATUS = 01

If STAGE > 300 and ADMIT_TYPE = AF or AFD or AP or APD, then
APPSTATUS = 02

If STAGE < 300 then APPSTATUS = ""
This is the expression I have typed up, but I havn't gotten
the right results:

IIf([STAGE]="600","03",IIf([STAGE]>"300","01",IIf([STAGE]>"300"
And [ADMIT_TYPE]= "AF" or "AFD" or "AP" or
APD","02",IIf([STAGE]<"300",""))))

I have limited experience using most of the code, and though I
have used the IIf statement before, I am not even sure if it
is the right one for this situation. If anyone has a better
way to do it or sees the error in the way I have attempted to
do this, please let me know. Or if this isn't clear enough
please let me know and I will try to explain further. I
appreciate any help that you can give!

Fran Zablocki

You might want to create a code module and write a user-defined
function. I find them a lot easier to maintain. Name the module
anything except cvtStage2Appstatus. I usually have a module
Conversions with several of these functions. They will even appear
in the expression builder. Very handy.

'-------------------------
Public function cvtStage2Appstatus( _
byval mystage as variant, _
myadmit_type as variant) as variant

select case mystage
case >=600
cvtStage2Appstatus = 03 ' or "03"
case > 300
select case myAdmit_type
case "AF"
cvtStage2Appstatus = 02 ' or "02"
case "AFD"
cvtStage2Appstatus = 02
case "AP"
cvtStage2Appstatus = 02
case "APD"
cvtStage2Appstatus = 02
case else
cvtStage2Appstatus = 01
end select
case 300
'Your choices don't include this.
' Fix by adding an option here, or delete this section and
'modify < 300 to <=300, or >300 to >=300
case else ' <300
cvtStage2Appstatus = 00 ' or " "
end select
end function
'-------------------------

Use the quoted version if you want a string result, the version as
I have it for a number.

Now in your query grid, call the function as
Appstatus: cvtStage2Appstatus([Stage],[Admit_type])

Or in a textbox.controlsource on a form or report
=cvtStage2Appstatus([Stage],[Admit_type])
Bob Q.