Multiple IIf statements in queries

[swift]

I'm trying to restructure this function which came from an excel workbook, I need to put it in my query. No progress so far though!

=IF(AND(B6<-0.05,B7<0.8),"Substantial",IF(OR(B6<-0.05,AND(B6<0,B7<0.8)),"Moderate",IF(OR(B6<0,B7<0.8),"Slight","None")))

Where B6 is my field [DIFF] and B7 is my field [TEX]

Hope someone understands and can help!

Thanks

swifty

 

[DCrake]

May be far easier to convert it to a function

=IF(AND(B6<-0.05,B7<0.8),"Substantial",IF(OR(B6<-0.05,AND(B6<0,B7<0.8)),"Moderate",IF(OR(B6<0,B7<0. 8),"Slight","None")))

Public Function xFactor(B6 As Double,B7 As Double) As String

IF B6 < -0.05 And B7 < 0.8 Then
   xFactor = "Substantial"
ElseIf ....
   XFactor = Moderate
ElseIf ....
  Slight
Else
  None
End If

Exit Function

You get the gist

Then in your query

Factor:XFactor([B6],[B7])

Where B6 and B7 arre your fields in Access

 

[Brianwarnock]

I think

=IIf(b6<-0.05 and b7<0.8,"substantial,iif(b6<-0.05 or b6<0 and b7<0.8,"moderate",iif(b6<0 or b7 <0,"slight","none)))

But the function as suggested by dave might be simpler to follow and change.

Brian

 

[swift]

Okay, so now I'm officially confused - tried adding the function a few times this afternoon at work (where I don't have my Dummies book), can't get any joy out of it so far!

Here's what I've got!

Public Function xFactor(DIFF As Double, TEX As Double) As String

If [DIFF] < -0.05 And [TEX] < 0.8 Then
   xFactor = "SUBSTANTIAL"
ElseIf ....
    [DIFF] < -0.05 Or [DIFF] <0.0 And [TEX] <0.8 Then
   xFactor = "MODERATE"
ElseIf ....
    [DIFF] < 0.00 Or [TEX] < 0.8 Then
  xFactor = "SLIGHT"
Else
  xFactor = "NONE"
End If

Exit Function

If I manage to get this right, I'm assuming that the Factor:xFactor ([DIFF],[TEX]) goes into the query grid as an expression?

Thanks for help so far!

swifty

 

[Galaxiom]

DIFF and TEX are variables in the function having been passed as arguments.
Remove the square brackets.

The expression you propose for the query is correct.

 

[Brianwarnock]

Althougth not needed it is not the square brackets that is the problem but the syntax of the EsleIf , which presumably gets flagged.

ElseIf  [DIFF] < -0.05 Or [DIFF] <0.0 And [TEX] <0.8 Then
   xFactor = "MODERATE"

Brian

 

[Galaxiom]

Althougth not needed it is not the square brackets that is the problem .....

Well there you go. Never dreamed that one could put square brackets around a variable. Not useful in any way whatsoever though. FWIW I tried some pointless exercises out of curiosity.

The square brackets cannot be included when declaring a variable.

It does not allow a space into a variable name as some developers do in a fieldname.

It does not allow a reserved word to be used as a variable.

Brain is correct. The real problem is that comparisons must be single statements that can be joined by AND or OR. I never looked past the square brackets.