L
LouD
Hello,
I'm building a query and the if statement was too long so I had to create a
function with a select case statement. The problem I am having is with the
statement pertaining to a date range. I can get the MN portion to display
correctly in my crosstab query if i remove the D8 portion but I need it
broken down by the year as well.
Public Function MND8(MN, D8)
Select Case MN
Case Is = "47U": MND8 = "WS"
Case Is = "UHU": MND8 = "WS"
Case Is = "W6B": MND8 = "WS"
Case Is = "10U": MND8 = "WS"
Case Is = "1AU": MND8 = "WS"
Case Is = "Z06": MND8 = "WS"
Case Is = "BJU": MND8 = "WS"
Case Is = "01U": MND8 = "SRV"
Case Is = "05U": MND8 = "SRV"
Case Is = "11U": MND8 = "SRV"
Case Is = "15U": MND8 = "SRV"
Case Is = "31U": MND8 = "SRV"
Case Is = "AC1": MND8 = "SRV"
Case Is = "F4U": MND8 = "SRV"
Case Is = "DBU": MND8 = "SRV"
Case Is = "F4U": MND8 = "SRV"
Case Else: MND8 = "N/A"
End Select
Select Case D8
Case Is = Year(#1/1/2005#): MND8 = MND8 & "2005"
Case Is = Year(#1/1/2006#): MND8 = MND8 & "2006"
Case Is = Year(#1/1/2007#): MND8 = MND8 & "2007"
Case Is = Year(#1/1/2008#): MND8 = MND8 & "2008"
Case Is = Year(#1/1/2009#): MND8 = MND8 & "2009"
Case Else: MND8 = "N/A"
End Select
End Function
I also tried this statement:
Public Function MND8(MN, D8)
Select Case MN
Case Is = "47U": MND8 = "WS"
Case Is = "UHU": MND8 = "WS"
Case Is = "W6B": MND8 = "WS"
Case Is = "10U": MND8 = "WS"
Case Is = "1AU": MND8 = "WS"
Case Is = "Z06": MND8 = "WS"
Case Is = "BJU": MND8 = "WS"
Case Is = "01U": MND8 = "SRV"
Case Is = "05U": MND8 = "SRV"
Case Is = "11U": MND8 = "SRV"
Case Is = "15U": MND8 = "SRV"
Case Is = "31U": MND8 = "SRV"
Case Is = "AC1": MND8 = "SRV"
Case Is = "F4U": MND8 = "SRV"
Case Is = "DBU": MND8 = "SRV"
Case Is = "F4U": MND8 = "SRV"
Case Else: MND8 = "N/A"
End Select
Select Case D8
Case Is >= 1 / 1 / 2005, Is <= 12 / 31 / 2005: MND8 = MND8 & "2005"
Case Is >= 1 / 1 / 2006, Is <= 12 / 31 / 2006: MND8 = MND8 & "2006"
Case Is >= 1 / 1 / 2007, Is <= 12 / 31 / 2007: MND8 = MND8 & "2007"
Case Is >= 1 / 1 / 2008, Is <= 12 / 31 / 2008: MND8 = MND8 & "2008"
Case Is >= 1 / 1 / 2009, Is <= 12 / 31 / 2009: MND8 = MND8 & "2009"
Case Else: MND8 = "N/A"
End Select
End Function
I'm not sure if that works like the "Between" "And" statement.
I'm building a query and the if statement was too long so I had to create a
function with a select case statement. The problem I am having is with the
statement pertaining to a date range. I can get the MN portion to display
correctly in my crosstab query if i remove the D8 portion but I need it
broken down by the year as well.
Public Function MND8(MN, D8)
Select Case MN
Case Is = "47U": MND8 = "WS"
Case Is = "UHU": MND8 = "WS"
Case Is = "W6B": MND8 = "WS"
Case Is = "10U": MND8 = "WS"
Case Is = "1AU": MND8 = "WS"
Case Is = "Z06": MND8 = "WS"
Case Is = "BJU": MND8 = "WS"
Case Is = "01U": MND8 = "SRV"
Case Is = "05U": MND8 = "SRV"
Case Is = "11U": MND8 = "SRV"
Case Is = "15U": MND8 = "SRV"
Case Is = "31U": MND8 = "SRV"
Case Is = "AC1": MND8 = "SRV"
Case Is = "F4U": MND8 = "SRV"
Case Is = "DBU": MND8 = "SRV"
Case Is = "F4U": MND8 = "SRV"
Case Else: MND8 = "N/A"
End Select
Select Case D8
Case Is = Year(#1/1/2005#): MND8 = MND8 & "2005"
Case Is = Year(#1/1/2006#): MND8 = MND8 & "2006"
Case Is = Year(#1/1/2007#): MND8 = MND8 & "2007"
Case Is = Year(#1/1/2008#): MND8 = MND8 & "2008"
Case Is = Year(#1/1/2009#): MND8 = MND8 & "2009"
Case Else: MND8 = "N/A"
End Select
End Function
I also tried this statement:
Public Function MND8(MN, D8)
Select Case MN
Case Is = "47U": MND8 = "WS"
Case Is = "UHU": MND8 = "WS"
Case Is = "W6B": MND8 = "WS"
Case Is = "10U": MND8 = "WS"
Case Is = "1AU": MND8 = "WS"
Case Is = "Z06": MND8 = "WS"
Case Is = "BJU": MND8 = "WS"
Case Is = "01U": MND8 = "SRV"
Case Is = "05U": MND8 = "SRV"
Case Is = "11U": MND8 = "SRV"
Case Is = "15U": MND8 = "SRV"
Case Is = "31U": MND8 = "SRV"
Case Is = "AC1": MND8 = "SRV"
Case Is = "F4U": MND8 = "SRV"
Case Is = "DBU": MND8 = "SRV"
Case Is = "F4U": MND8 = "SRV"
Case Else: MND8 = "N/A"
End Select
Select Case D8
Case Is >= 1 / 1 / 2005, Is <= 12 / 31 / 2005: MND8 = MND8 & "2005"
Case Is >= 1 / 1 / 2006, Is <= 12 / 31 / 2006: MND8 = MND8 & "2006"
Case Is >= 1 / 1 / 2007, Is <= 12 / 31 / 2007: MND8 = MND8 & "2007"
Case Is >= 1 / 1 / 2008, Is <= 12 / 31 / 2008: MND8 = MND8 & "2008"
Case Is >= 1 / 1 / 2009, Is <= 12 / 31 / 2009: MND8 = MND8 & "2009"
Case Else: MND8 = "N/A"
End Select
End Function
I'm not sure if that works like the "Between" "And" statement.