Thanks,
When I try to query my MDB ODBC via microsoft query I get the error "Undefined function 'CaseSearched' in expression"
here is the SQL I am using
SELECT Schedules.AGENT_ID,
Schedules.AGENT_NAME AS 'Agent', Schedules.AGENT_DATA_VALUE AS 'Supervisor', Schedules.MU_ID AS 'MU',
Schedules.HEADER_DATE AS 'Schedule Date', Schedules.DETAIL_EXCEPTION AS 'Detail Exception', CaseSearched(Schedules.Detail_Exception = 'Sick', 'Absent') AS [Catagory],
Header_date+(DETAIL_START_MINUTE/1440) AS 'Detail Start', Header_Date+((Detail_START_MINUTE+DETAIL_LENGTH)/1440) AS 'Detail Stop',
Schedules.DETAIL_LENGTH AS 'Detail Duration', Schedules.SCHED_ID AS 'Schedule ID'
FROM `\\Wfmid-1350\database$\IEX`.Schedules Schedules WHERE (Schedules.HEADER_DATE>= #2009-05-31# And Schedules.HEADER_DATE< #2009-06-23#) AND (Schedules.MU_ID In (1000))
When I use this query in the MDB itself it works just fine. Do I need to use a different syntax for the function when querying it externally?
Here is the VBA I am using for the function:
Private Enum CaseTypes
CaseTypeSimple = 1
CaseTypeSearched = 2
End Enum
Public Function CaseSimple(ByVal Value As Variant, ParamArray WhenExpResultPairs() As Variant) As Variant
CaseSimple = CaseBase(CaseTypeSimple, Value, WhenExpResultPairs)
End Function
Public Function CaseSearched(ParamArray WhenExpResultPairs() As Variant) As Variant
CaseSearched = CaseBase(CaseTypeSearched, Empty, WhenExpResultPairs)
End Function
Private Function CaseBase(ByVal CaseType As CaseTypes, ByVal Value As Variant, ByVal WhenExpResultPairs As Variant) As Variant
Dim lNumPairs As Long
Dim lPairIndex As Long
Dim lIndex As Long
Dim bFound As Boolean
On Error GoTo Oops
lNumPairs = (UBound(WhenExpResultPairs) \ 2) + (UBound(WhenExpResultPairs) Mod 2)
For lPairIndex = 1 To lNumPairs
lIndex = (lPairIndex - 1) * 2
If CaseType = CaseTypeSimple Then
bFound = Nz(Value = WhenExpResultPairs(lIndex), False)
ElseIf CaseType = CaseTypeSearched Then
bFound = CBool(Nz(WhenExpResultPairs(lIndex), False))
Else
GoTo Oops
End If
If bFound Then
CaseBase = WhenExpResultPairs(lIndex + 1)
Exit Function
End If
Next lPairIndex
If UBound(WhenExpResultPairs) Mod 2 = 0 Then
CaseBase = WhenExpResultPairs(UBound(WhenExpResultPairs))
Else
CaseBase = Null
End If
Exit Function
Oops:
CaseBase = "#ERROR"
End Function