Long,long IIf statement

[writersoon]

Does anyone know how to make these 3 IIf statements into one?

I am trying to put it all in one column and i keep recieving errors when i make input all 3 statments into 1 and change the query to a make table query.

Thanks in advance...


SELECT p.*, IIf([POType]="PO" And [NonStock]="N","NS", IIf([POType]="PO" And [Inventory Pos]>LinePt And [Days Available to Position]<91 And [NonStock]="","LP+U")) AS Comments
FROM p;

SELECT p.*, IIf([POType]="PO" And [NonStock]="N","NS",IIf([POType]="PO" And [Inventory Pos]<LinePt And QtyBO>0 And [NonStock]="","BO")) AS Comments
FROM p;

SELECT IIf([PO Type]="BO","Blkt Order",IIf([PO Type]="RM","PORM",IIf([PO Type]="WT","WHS Transfer",IIf([PO Type]="BL","Blkt Release",IIf([PO Type]="DO" And [NonStock]="N","NS Direct","Direct"))))) AS Comments, Review.*, * INTO [PO REVIEW ATTRIBUTES]
FROM P AS Review;

 

[AlexCuse]

Probably, but I doubt anyone will particularly want to ;-) Maybe it would be easier if you described the problem your iif's are solving, and let them start from scratch?

If I were you I would try to put this into a VBA function, to make it so you don't have to maintain that mess in separate places. I make it a rule to put anything with >2 nested iif's into a function.

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[writersoon]

Do you have a a code example to go off?

All i am trying to do is make one query from an import that inserts the above comments into a column of its own if the criteria meets.

 

[Remou]

writersoon
It is best to continue in the same thread.
thread701-1455703

 

[MajP]

Public Function getComment(theType As String, theStock As String, theInventory As Integer, theLinePt As Integer, theDays As Integer, theQty As Integer)
 Select Case theType
   Case "BO"
     getComments = "Blkt Order"
   Case "RM"
     getComments = "PORM"
   Case "WT"
     getComments = "WHS Transfer"
   Case "BL"
     getComments = "Blkt Release"
   Case "DO"
     If theStock = "N" Then
       getComments = "NS Direct"
     Else
       getComments = "Direct"
     End If
   Case "PO"
      If theStock = "N" Then
        getComment = "NS"
      ElseIf theInventory > theLinePt And theDays > 91 And theStock = "" Then
        getComments = "LP+U"
      ElseIf theInventory < theLinePt And theQty > 0 And theStock = " " Then
        getComments = "BO"
      End If
   Case Else
     'something here
   End Select
End Function

SELECT p.*, getComment([PO Type],[NonStock],[Inventory Pos],[linePt],[Days Available to Position],[QtyBO]) as comments from p

Your code is a mess so this may not be the exact logic

 

[writersoon]

Is the code suppose to say "theType".

I understand exactly what you have written, it looks good...

 

[MajP]

What part are you referring to? I think that answer is yes.

 

[writersoon]

I tried the code and it seemed to work, but the comment column is full of #Errors#, but iam not recieving a debugging error in the code.

 

[MajP]

What do you mean by it seemed to work? Did you get any correct results or all #Error#?
1) to debug a function first test it in the vba window using literals. Something like
getComment("PORM","N",20,20,90,2)
2) Change your literals to verify the logic

3) This function has no null checks so if you pass a null you may get an error in certain places. One way to avoid this is to change all parameters to variants and use NZ to return a zero or empty string

theType = nz(theType,"")
theQty = nz(theQty,0)
4) Once you get the function working in the vba window then try to use it in a query. I would first make a query that only returns one record to test the function.

 

[writersoon]

When i run the code i dont get any errors, but when the query is ran the entire comments section is #error#.

That what i thought when i wrote that it seemed to work, because i didnt recieve an error in vba.

 

[TheAceMan1]

How are ya writersoon . . .

Two of your IIf statements are incomplete in the last IIf:

[blue]SELECT p.*, IIf([POType]="PO" And [NonStock]="N","NS", IIf([POType]="PO" And [Inventory Pos]>LinePt And [Days Available to Position]<91 And [NonStock]="","LP+U"[COLOR=red yellow][b],"?"[/b][/color])) AS Comments
FROM p;

SELECT p.*, IIf([POType]="PO" And [NonStock]="N","NS",IIf([POType]="PO" And [Inventory Pos]<LinePt And QtyBO>0 And [NonStock]="","BO"[COLOR=red yellow][b],"?"[/b][/color])) AS Comments
FROM p;[/blue]

Also, in the code provided by [blue]MajP[/blue], for testing purposes enter a value in:

[blue]   Case Else
     [purple][b]'something here[/b][/purple]

Example:

   Case Else
     [purple][b]getComments = "?"[/b][/purple][/blue]

Its possible the code falls thru to [blue]Case Else![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[writersoon]

Ok, I tried the code a few diffrent ways and i am still recieving the #name# error.

Ive adjusted the code to below, can anybody see a mistake in it.

Option Compare Database

Public Function getComment()

Dim strType As String
Dim strStock As String
Dim strInventory As Integer
Dim intLinePt As Integer
Dim intDaysAvailableToPosition As Integer
Dim intQty As Integer
getComment = Comments
getComment = "Needs Attention"


Select Case strType
Case "BO"
getComment = "Blkt Order"
Case "RM"
getComment = "PORM"
Case "WT"
getComment = "WHS Transfer"
Case "BL"
getComment = "Blkt Release"
Case "DO"
If strStock = "N" Then
getComment = "NS Direct"
Else
getComment = "Direct"
End If
Case "PO"
If strStock = "N" Then
getComment = "NS"
ElseIf theInventory > theLinePt And theDays > 91 And theStock = "S" Then
getComment = "LP+U"
ElseIf theInventory < theLinePt And theQty > 0 And theStock = "S" Then
getComment = "BO"
End If
Case Else
getComment = "Needs Attention"
End Select
getComment = Comments
End Function

 

[randy700]

At the top of your code, you assign a value to getComment
getComment = Comments
Not sure what this does, because I don't know the value of Comments

You then assign a specific value to getComment via the Select Case structure.

Finally, you end the function with the same command you started with
getComment = Comments
which overwrites anything accomplished in the Select Case structure.

In other words, the value you have assigned to getComment follows this pattern.
getComment = Some value
getComment = A new value
getComment = The first value again!

Randy

 

[PHV]

Public Function getComment()
You should understand that your function needs parameters (aka arguments) ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[writersoon]

I have an argument in the maketable query that i am using to run the function.

I have deletd as you have pointed out, but maybe i am not placing calling the function correctly to the query?
I have been running the code in diffrent ways...
Thanks.

Public Function getComment()

Dim strType As String
Dim strStock As String
Dim strInventory As Integer
Dim intLinePt As Integer
Dim intDaysAvailableToPosition As Integer
Dim intQty As Integer


Select Case strType
Case "BO"
getComment = "Blkt Order"
Case "RM"
getComment = "PORM"
Case "WT"
getComment = "WHS Transfer"
Case "BL"
getComment = "Blkt Release"
Case "DO"
If strStock = "N" Then
getComment = "NS Direct"
Else
getComment = "Direct"
End If
Case "PO"
If strStock = "N" Then
getComment = "NS"
ElseIf theInventory > theLinePt And theDays > 91 And theStock = "S" Then
getComment = "LP+U"
ElseIf theInventory < theLinePt And theQty > 0 And theStock = "S" Then
getComment = "BO"
End If
Case Else
getComment = "Needs Attention"
End Select
End Function

 

[randy700]

You have Dimensioned several variables at the top of the function and then referred to them within the SELECT structure. You did NOT, however, assign any values to those variables. Because of this, your code will ALWAYS fall through to the Case Else, as suggested earlier by TheAceMan1.

Randy

 

[PHV]

writersoon, did you make sense with my advice (17 Mar 08 9:20) ?

 

[MajP]

You claim that the original code that I posted works. Then the problem is how you are calling the function not in the function.

original code:

Public Function getComment(theType As String, theStock As String, theInventory As Integer, theLinePt As Integer, theDays As Integer, theQty As Integer)
 Select Case theType
   Case "BO"
     getComments = "Blkt Order"
   Case "RM"
     getComments = "PORM"
   Case "WT"
     getComments = "WHS Transfer"
   Case "BL"
     getComments = "Blkt Release"
   Case "DO"
     If theStock = "N" Then
       getComments = "NS Direct"
     Else
       getComments = "Direct"
     End If
   Case "PO"
      If theStock = "N" Then
        getComment = "NS"
      ElseIf theInventory > theLinePt And theDays > 91 And theStock = "" Then
        getComments = "LP+U"
      ElseIf theInventory < theLinePt And theQty > 0 And theStock = " " Then
        getComments = "BO"
      End If
   Case Else
     'something here
   End Select
End Function

So if this works in the vba window you are not calling it correctly in your query. Post your query. Again this code does not have a null check. If the parameter ask for a string and you pass a null or something else you are going to get an error. That is your problem

Try this.

Public Function getComment(theType As String, theStock As String, theInventory As Integer, theLinePt As Integer, theDays As Integer, theQty As Integer)

get comments = "Test"
End Function

If you do not get "Test" then you are not calling the function correctly or your parameters variable types do not match.

 

[writersoon]

I am calling the function as the control source in a form, but i am not getting "test", i tried from a query also, but no luck.

I tried changing the Null's to variables that can be tested instead of having an empty value, i cant think of anything else...
Thanks

 

[MajP]

One more time! Post your code.