MS Access query with user function criteria stops working on odbc data

user_5686991 0
Viewed 61

I am working in the MS Access query builder. I'm using a user-defined function to return a bracketed group of characters to search for. For example, the criteria line is

Like Tcode()

and the function Tcode() is

Function Tcode() As String
Tcode = "[AC]"
End Function

If I create a local table of data with 3 single-character rows of data

A
B
C

and run a query on that column with this criteria, I get just the rows of A and C, as desired. If I run it with the criteria of

Like "[AC]"

I also get just the rows of A and C, as desired. The problem comes in when I run a query against an ODBC Oracle table we have. If I use the criteria of Like "[AC]" then I get results of A and C. If I use the criteria of like Tcode() then I get no results. I have tried several variations on the return value of Tcode without success:

Tcode returns [AC] No results

Tcode returns "[AC]" No results

Tcode returns [[AC]] No results

Tcode returns [A] No results

Tcode returns A Results in all rows with A

I want to dynamically build the Tcode return value to get different results based on user inputs on other forms. Why doesn't it work on the odbc table? I'd also accept a method that builds a search like

in ('A','C')

or

'A' or 'C'

but I don't know how to implement that using functions.

Edit: Oracle version

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE 12.1.0.2.0 Production"
TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Client drivers 19.5

ms-access odbc function oracle
2 Answers

Interesting. I can only help you insofar that ODBC in general isn't the problem.

It works with a Sql Server table:

CREATE TABLE foobar (
    T varchar(10) PRIMARY KEY
);

linked in Access using the ODBC Driver 17 for SQL Server, containing the values A,B,C as above.

And this query:

SELECT T
FROM foobar
WHERE T Like Tcode()

returns the values A+C (with the function defined as in your question).

So it must be a problem of the Oracle ODBC driver.

You should probably specify, which version of Oracle and the ODBC driver you use.

Note that you can't use the IN clause with a function, see https://stackoverflow.com/a/63054220/3820271

user_38202710

I wasn't able to find out why it's happening. I had two options; one was to use VBA to custom-generate sql for a pass-through query on each run. I tend to save this for complex queries that make use of special functions on the server. The solution I went with was to turn the function into a boolean that evaluated the string. Not the fastest thing for large queries, but ..tradeoffs.

Function TCeval(S As String) As Boolean
'usage: in criteria line: TCeval([FIELDNAME])
TCeval = False
If 1 = 1 Then 'replace with actual if evaluations
    If S = "A" Or S = "C" Then TCeval = True
End If
If 1 = 0 Then 'replace
    If S = "B" Then TCeval = True
End If
End Function
user_56869910
Related