Using Function in in access ODBC query

[aaronlglover]

Hey Guys,

I have an access database as a store for some data. I have a custom function in my MDB that is basically a CASE statement. It works when build queries within the MDB itself...is there a way I can call on this function when querying the tables externally via an access ODBC connection?

I am using microsoft query to pull the data into excel.

Thanks

 

[Axworthy]

Aaron,

If you are using a regular Access query then yes, you can use Access functions as it treats the linked table the same as a linked Access table.

If you are using a pass thru query, then the SQL statement sent to your data source must use the language and functions of that database (i.e T-SQL if using SQL Server).

 

[aaronlglover]

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

 

[PHV]

You can't use any UDF with a Recordset.
You may consider the builtin IIf(), Switch() or Choose() in your JetSQL code.

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

 

[aaronlglover]

Thanks PHV,

I have tried using the IIF() and Switch() functions however when I have more then 15 conditions in either of them I get a "expression too complex" error. I would normally just create a table in the MDB with all the lookup values that I would join however the lookup values are dynamic based on user inputs in excel.

The above SQL is being executed by ADODB in some excel VBA. Is it possible to join in a temporary table that is derived from a range of cells in the open xls?

Sorry for my obvious noobness in all of this. I am relatively new to SQL