still having equation problem

[stryder09]

So Im still having problems with this one and not sure how to write it? I know excel and Access handle things differently but I'm still stumped on what I should change. Also does Access allow >= or do I have to break those equations up?

Okay I am trying to use the following equation but keep getting back the error
The expression you entered contains invalid syntax.
You may have entered a comma without preceding value or identifier



the equation

=IIF(AND([DeadPinRecoveryTopMeasured]>=[DeadPinThicknessMIN],[DeadPinRecoveryTopMeasured]<=[DeadPinThicknessMAX]),0,IIF(AND([DeadPinRecoveryTopMeasured]+2>=[DeadPinThicknessMIN],[DeadPinRecoveryTopMeasured]-2<=[DeadPinThicknessMAX]),2,IIF(AND([DeadPinRecoveryTopMeasured]+4>=[DeadPinThicknessMIN],[DeadPinRecoveryTopMeasured]-4<=[DeadPinThicknessMAX]),5,20)))

Again any help would be apprciated. I feel a little bit out of my element with the equation aspect of all of this.

thanks,

Brad

[RuralGuy]

How about we start with IIF(AND[... will not work.

[stryder09]

Now I get a #name?

[RuralGuy]

You'll need to post what you have currently after each correction so we can see it.

[stryder09]

Lol sorry about that, here is the equation I have in place now.

IIF(AND([DeadPinRecoveryTopMeasured]>=[DeadPinThicknessMIN],[DeadPinRecoveryTopMeasured]<=[DeadPinThicknessMAX]),0,IIF(AND([DeadPinRecoveryTopMeasured]+2>=[DeadPinThicknessMIN],[DeadPinRecoveryTopMeasured]-2<=[DeadPinThicknessMAX]),2,IIF(AND([DeadPinRecoveryTopMeasured]+4>=[DeadPinThicknessMIN],[DeadPinRecoveryTopMeasured]-4<=[DeadPinThicknessMAX]),5,20)))

[RuralGuy]

It still starts with IIF(AND(.. You can't do that. This is InFix notation equations.

[stryder09]

Okay so how should I start it?

[RuralGuy]

Say it in English and construct it the same way.
If <condition1> AND <condition2> AND <condition3>...

[stryder09]

lol, you make it sound so easy!

[stryder09]

So in the following equation then would I put the AND before the second IIF or after? And would it have to be like ,0,AND,IIF([... or like ,0,IFF(AND([...

=IIF([DeadPinRecoveryTopMeasured]>[DeadPinThicknessMIN],[DeadPinRecoveryTopMeasured]=[DeadPinThicknessMIN]),0,IIF([DeadPinRecoveryTopMeasured]=[DeadPinThicknessMAX],[DeadPinRecoveryTopMeasured]<[DeadPinThicknessMAX]),0,IIF([DeadPinRecoveryTopMeasured]+2)>([DeadPinThicknessMIN],[DeadPinRecoveryTopMeasured]+2)=[DeadPinThicknessMIN]),2,IIF([DeadPinRecoveryTopMeasured]+2)=([DeadPinThicknessMAX],[DeadPinRecoveryTopMeasured]-2)<[DeadPinThicknessMAX]),2,IIF([DeadPinRecoveryTopMeasured]+4)>([DeadPinThicknessMIN],[DeadPinRecoveryTopMeasured]-4)+[DeadPinThicknessMIN]),5,IIF([DeadPinRecoveryTopMeasured]+4)=([DeadPinThicknessMAX],[DeadPinRecoveryTopMeasured]-4)<[DeadPinThicknessMAX]),5,20)))

[RuralGuy]

Have you constructed nested IIF statements before? How about putting it together as a sequence of If...ELSEIF...ELSEIF...ELSE...END IF statements so we can see that you are trying to accomplish?

[stryder09]

Nope this is all new to me.

[stryder09]

So does this make sense written out?

If [DeadPinRecoveryTopMeasured] is greater than or equal to [DeadPinThicknessMIN] And [DeadPinRecoveryTopMeasured] is less than or equal to [DeadPinThicknessMAX Then Return 0 if not then return 2
or
If ([DeadPinRecoveryTopMeasured]+2) is greater than or equal to [DeadPinThicknessMIN] And ([DeadPinRecoveryTopMeasured]-2) is less than or equal to [DeadPinThicknessMAX Then Return 2 if not return 5
or
if ([DeadPinRecoveryTopMeasured]+4) is greater than or equal to [DeadPinThicknessMIN] And ([DeadPinRecoveryTopMeasured]-4) is less than or equal to [DeadPinThicknessMAX Then Return 5 if not return 20

[stryder09]

this is a demerit system I am working on we have a thickness of a product thats specs call for it to between say 100 and 110mm. If in that range 0 demerits are assigned. If however it is either 98 to 100 or 110 to 112 it will have a demerit of 2. If it is 96 to 98 or 112 to 114 it will have a demerit of 5 and if it is <96 of >114 then the demerit will be 20.

Maybe I am making it more difficult than it seems?

[stryder09]

Okay Thanks RuralGuy I sat down and looked at what I was trying to do. I wrote it down and came up with the following equation. This works and does exactly what I needed it to do. I have built some databases before but I have just never had such thought needed equations before. I'm sure I will be posting again before this database is done.

=IIf([DeadPinRecoveryTopMeasured]>[DeadPinThicknessMin] And [DeadPinRecoveryTopMeasured]<[DeadPinThicknessMAX],0,IIf([DeadPinRecoveryTopMeasured]+2.1>[DeadPinThicknessMin] And [DeadPinRecoveryTopMeasured]-2.1<[DeadPinThicknessMAX],2,IIf([DeadPinRecoveryTopMeasured]+4.1>[DeadPinThicknessMin] And [DeadPinRecoveryTopMeasured]-4.1<[DeadPinThicknessMAX],5,20)))