HELP - Nested IIF Statement

chelseaaa

New member
Local time
Today, 03:23
Joined
Jul 16, 2013
Messages
8
Hello I am new to Access and have been struggling for the past few days with a query to call the data in one column that is within a data range.

I have written both a nested IIF statement and a SWITCH statement. However, when I run it, it looks like it's not allowing a space. Is there a way around this?

TAT Status: IIF([PO Line Creation Date] <[ Requisition Approval Date], ,(IIF([Working Days]<0, Pre-ReqApproval, IIF(AND( [Working Days]>=0,[Working Days]<=3), 0 to 3 Days, IIF(AND([Working Days]>=4, [Working Days]<=6), 4 to 6 Days, IIF(AND([Working Days]>=7, [Working Days]<=10), 7 to 10 Days, IIF([Working Days]>=11, Over 11 Days)))))))

TAT Status: Switch ([PO Line Creation Date]<[ Requisition Approval Date], “”,[Working Days]<0, “Pre-Req. Approval”, [Working Days]>=0 AND [Working Days]<=3, “0 to 3 Days”,[Working Days]>=4 AND [Working Days]<=6,”4 to 6 Days”,[Working Days]>=7 AND [Working Days]<=10,”7 to 10 Days”,[Working Days]>=11, “Over 11 Days”)

Any help would be GREATLY APPRECIATED! :)
 
When you're query expressions get that complicated its time to create a function in a module. That way you can have as complicated method for calculating as you need and still have it be readable by a human.

That makes your field in your query this:

TAT_Status: getTATStatus([PO Line Creation Date], [Requisition Approval Date], [Working Days])

Then the function in your module would start and end like this:

Code:
Function getTatStatus(PO_date, ReqApp_date, Work_date) AS String
    ' determines TAT Status based on input dates
 
 
    ' logic now in query goes in here now
 
 
End Function
 
So for the ' logic now in query goes in here now' part?

I put in the IIF statement? Sorry I am totally new to the technical side of Access.
 
You put in the logic of the iif statement, not the abomination in its entirety. In VBA IIF becomes If (along with a few other minor things). Read up on a few VBA tutorials. The logic would be virtually the same, its just you can use as many lines and as much space as you need to write the logic. It will make debugging your code so much simpler.
 
I keep getting the error message - "Undefined function 'getTATStatus' in expression".

I think it has something to do with this line:

Function getTatStatus(PO_date, ReqApp_date, Work_date) As String


Thank you for your help thus far.
 
Show your entire code please.

Dale
 
This is what I have in VBA.

Function getTatStatus(PO_date, ReqApp_date, Work_date) As String
' determines TAT Status based on input dates

IIf ([PO Creation Date] < [Req Approval Date]), "", IIf([Working Days] < 0, "Pre-Req. Approval", IIf([Working Days] >= 0 And [Working Days] <= 3, "0 to 3 Days", IIf([Working Days] >= 4 And [Working Days] <= 6, "4 to 6 Days", IIf([Working Days] >= 7 And [Working Days] <= 10, "7 to 10 Days", [Working Days] >= 11, "Over 11 Days"))))


End Function

The query that I wrote was:

TAT Status: getTatStatus([Check])
 
Code:
IIf ([PO Creation Date] < [Req Approval Date]), "", IIf([Working  Days] < 0, "Pre-Req. Approval", IIf([Working Days] >= 0 And  [Working Days] <= 3, "0 to 3 Days", IIf([Working Days] >= 4 And  [Working Days] <= 6, "4 to 6 Days", IIf([Working Days] >= 7 And  [Working Days] <= 10, "7 to 10 Days", [Working Days] >= 11, "Over  11 Days"))))
Iif only works for queries.

try something like this.

Code:
  If ([PO Creation Date] < [Req Approval Date]), "" then
                 ‘Add action here if true
  Elseif  ([Working Days] < 0, then
         "Pre-Req. Approval"
  elseIf([Working Days] >= 0 And [Working Days] <= 3  then
  "0 to 3 Days", 
  elseIf([Working Days] >= 4 And [Working Days] <= 6 then
  "4 to 6 Days"
  elseIf([Working Days] >= 7 And [Working Days] <= 10 then
   "7 to 10 Days", [Working Days] >= 11, "Over 11 Days"
  [FONT=&quot]End if[/FONT]
 
The same message is still showing. I think it has something to do with PO_date, ReqApp_Date, and Work_Date. Because I have never called those values.

This is the code that I have:

Function getTatStatus(PO_date, ReqApp_date, Work_date) As String
' determines TAT Status based on input dates

If ([PO Creation Date] < [Req Approval Date]) Then
TAT Status = Null

ElseIf ([Working Days] < 0) Then
TAT Status = "Pre-Req. Approval"

ElseIf ([Working Days] >= 0 And [Working Days] <= 3) Then
TAT Status = "0 to 3 Days"

ElseIf ([Working Days] >= 4 And [Working Days] <= 6) Then
TAT Status = "4 to 6 Days"

ElseIf ([Working Days] >= 7 And [Working Days] <= 10) Then
TAT Status = "7 to 10 Days"

ElseIf ([Working Days] >= 11) Then
TAT Status = "Over 11 Days"

End If

End Function
 
Try this at the top of your function.
Function getTatStatus(PO_date, ReqApp_date, [Working Days])
Dim PO_Date As Date
Dim RegApp_Date As Date
Dim WorkDays As Interger
Dim TAT As String

Dale

What variables are you passing to this Function?
 
I think it has something to do with PO_date, ReqApp_Date, and Work_Date. Because I have never called those values.

In the immortal words of Homer Simpson, 'you're close but way off'. PO_date, ReqApp_Date and Work_Date are not values, they are variables and inside them are the only values your function knows. So, when you try and use [Working Days], [PO Creation Date] and [Req Approval Date] in your VBA code, it has no idea what you are referencing.

Your function exists seperately from your table and your queries. Its like a factory 4 towns over, you ship it data, it ships you back a finished product. Your loading [Working Days] into a truck that they are going to call 'Work_date', [PO Creation Date] into a truck they are going to call 'PO_date' and [Req Approval Date] into a truck called 'ReqApp_date'. So, whenever you go to the factory floor (the VBA code), you need to use their references for things.
 
Now all my queries won't work. There has to be an easier way, in Excel it is super easy to do an IF statement. But I can't seem to do this in Access.

For example, in column A [Working Days] - the data is based on two dates (Req Approval Date and Req Need Date). I wanted to count how many working days between req approval and the req approval date. However, I cannot show the negative days (when Need by date is before the Approval date).

For column B, if there is a negative number in Column A, then write Y otherwise write N.
 
You may be better off using a Select Case statement like this.

Code:
Public Function getTatStatus(ByVal PO_Date As Variant, ByVal ReqApp_date As Variant, ByVal Work_date As Long) As String

  If PO_Date < ReqApp_date Then
    getTatStatus = ""
    Exit Function
  End If

 Select Case Work_date
  Case Is < 0
    getTatStatus = "Pre-Req. Approval"
  Case 0 To 3
    getTatStatus = "0 to 3 Days"
  Case 4 To 6
    getTatStatus = "4 to 6 Days"
  Case 7 To 10
    getTatStatus = "7 to 10 Days"
  Case Else
    getTatStatus = "Over 11 Days"
  End Select
End Function

Then use it in your query thus.


TAT Status: getTatStatus([PO Line Creation Date], [Requisition Approval Date], [Working Days])
 
Last edited:

Users who are viewing this thread

Back
Top Bottom