If, Then Statements

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I am trying to create an expression for a new field, that based upon what is
entered in a previous field, will automatically assign it a numeric value.
For example.
MSCO Field contains a drop down list with the following options:
1. 5 or More
2. 3 or 4
3. 1 or 2

I want to create a new field MSCO Score that will yield the following based
upon what is answered in MSCO:
If 5 or More, then 4
If 3 or 4, then 2
If 1 0r 2, then O

Does this make any sense? Can anyone please shed some light on how to
proceed. Figuring this out is the crux of my entire database.

Thank you,
Sharon
 
Sharon said:
I want to create a new field MSCO Score that will yield the following based
upon what is answered in MSCO:
If 5 or More, then 4
If 3 or 4, then 2
If 1 0r 2, then O

Does this make any sense? Can anyone please shed some light on how to
proceed. Figuring this out is the crux of my entire database.
Click to expand...

.... and figuring out how not to do it will be the crux of all your
later, more successful ones.

If the MSCOScore is always dependent on the MSCO value, then you
calculate it in the query:

SELECT This, That, TheOther,
IIF(MSCO>4,4, IIF(MSCO>2,2, IIF(MSCO>0,0,NULL))) AS MSCOScore,
AnyOtherFields
FROM MyTable
etc etc

This way you won't forget to update the MSCOScore field when the MSCO
value itself changes. You might like to read up on second-form
normalisation.

Hope that helps


Tim Ferguson
 
Tim's solution would work. As an alternative, I would suggest adding an
additiona column to your combo for MSCO, and assigning MSCO Score its value
in the After Update event of the MSCO combo:
So you columns would be:

5 or More 4
3 or 4 2
1 0r 2 O

Then in the After Update event:

Me.txtMSCOscore = Me.cboMSCO.Column(1)
 
Tim,
I thank you very, very much...

Tim Ferguson said:
.... and figuring out how not to do it will be the crux of all your
later, more successful ones.

If the MSCOScore is always dependent on the MSCO value, then you
calculate it in the query:

SELECT This, That, TheOther,
IIF(MSCO>4,4, IIF(MSCO>2,2, IIF(MSCO>0,0,NULL))) AS MSCOScore,
AnyOtherFields
FROM MyTable
etc etc

This way you won't forget to update the MSCOScore field when the MSCO
value itself changes. You might like to read up on second-form
normalisation.

Hope that helps


Tim Ferguson
Click to expand...
 
Thank you very, very much!

Klatuu said:
Tim's solution would work. As an alternative, I would suggest adding an
additiona column to your combo for MSCO, and assigning MSCO Score its value
in the After Update event of the MSCO combo:
So you columns would be:

5 or More 4
3 or 4 2
1 0r 2 O

Then in the After Update event:

Me.txtMSCOscore = Me.cboMSCO.Column(1)
Click to expand...
 
Tim,
I thank you very, very much...

Tim Ferguson said:
.... and figuring out how not to do it will be the crux of all your
later, more successful ones.

If the MSCOScore is always dependent on the MSCO value, then you
calculate it in the query:

SELECT This, That, TheOther,
IIF(MSCO>4,4, IIF(MSCO>2,2, IIF(MSCO>0,0,NULL))) AS MSCOScore,
AnyOtherFields
FROM MyTable
etc etc

This way you won't forget to update the MSCOScore field when the MSCO
value itself changes. You might like to read up on second-form
normalisation.

Hope that helps


Tim Ferguson
Click to expand...
 
Tim,
I followed your expression and this is what I am encountering:
1) Only half of the expression is working. The last part of the expression:
IIf([charges]=1/2,0))). If I enter a 1 or a 2 in the charges field it should
give me a 0 but it is giving me a two.

2) I created the expression using a query but it does not update if I change
the number of charges. For example, I put 5 in the charges field and it gives
me 4 in the other field but if I change it to 4 it should give me a 2 and it
doesn't. However, if I do this in a form it does update.

Any suggestions?



Expr1: IIf([charges]>=5,4,IIf([charges]>=3/4,2,IIf([charges]=1/2,0)))
 
Try

IIf([Charge] >= 5 , 4 , IIf([Charge] In (3,4) , 2, 0))

--
Good Luck
BS"D


Sharon Walls said:
Tim,
I followed your expression and this is what I am encountering:
1) Only half of the expression is working. The last part of the expression:
IIf([charges]=1/2,0))). If I enter a 1 or a 2 in the charges field it should
give me a 0 but it is giving me a two.

2) I created the expression using a query but it does not update if I change
the number of charges. For example, I put 5 in the charges field and it gives
me 4 in the other field but if I change it to 4 it should give me a 2 and it
doesn't. However, if I do this in a form it does update.

Any suggestions?



Expr1: IIf([charges]>=5,4,IIf([charges]>=3/4,2,IIf([charges]=1/2,0)))

Tim Ferguson said:
.... and figuring out how not to do it will be the crux of all your
later, more successful ones.

If the MSCOScore is always dependent on the MSCO value, then you
calculate it in the query:

SELECT This, That, TheOther,
IIF(MSCO>4,4, IIF(MSCO>2,2, IIF(MSCO>0,0,NULL))) AS MSCOScore,
AnyOtherFields
FROM MyTable
etc etc

This way you won't forget to update the MSCOScore field when the MSCO
value itself changes. You might like to read up on second-form
normalisation.

Hope that helps


Tim Ferguson
Click to expand...
Click to expand...
 
=?Utf-8?B?U2hhcm9uIFdhbGxz?= <[email protected]>
wrote in
Sorry for the delay: been away.
I followed your expression and this is what I am encountering:
1) Only half of the expression is working. The last part of the
expression: IIf([charges]=1/2,0))). If I enter a 1 or a 2 in the
charges field it should give me a 0 but it is giving me a two.
Click to expand...
Expr1: IIf([charges]>=5,4,IIf([charges]>=3/4,2,IIf([charges]=1/2,0)))
Click to expand...

The expression

charges >= 3/4

will be true for any value above 0.75 -- is this what you want? I guess
not. Check out the maths.
2) I created the expression using a query but it does not update if I
change the number of charges.
Click to expand...

No: that's the whole point. If the new field is fully dependent on the
first field, then it has no place in the table; just calculate it
whenever you need to see it. It belongs in the query, not in the table.
Check out R theory and normalisation.

Hope that helps


Tim F
 
Back
Top