Dlookup function

[Andy74]

Hello,

today I noticed a strange behaviour on a simple DLookup function on two linked tables.

value = Nz(DLookup("field1", "table", "field2='" & activeuser & "'"), "NN")

If the "table" is a linked table to SQL server database, then comparison will be case unsensitive (which I prefer). If the "table" is a linked table to a Oracle database table then the comparison will be "case sensitive".

The forms with the instruction are always with "Option Compare Database"

As my application is linked to both type of database, how could I avoid this?
Shall I replace the Dlookup functions with SQL select statements, i.e. open a recordset and then extract the value?


Andy

 

[Ranman256]

wow, ive never seen it be case sensitive.
instead of the table, try using a query that uses UCASE and search your text in ucase.

 

[CJ_London]

you could use the ucase or lcase function to force the case for the comparison

"ucase(field2)='" & ucase(activeuser) & "'"

or the strcomp function

"strcomp(field2,'" & activeuser & "',0)=0"

 

[Andy74]

thanks CJ, I solved it with the lcase function. Anyway I wonder why the different behaviour of Oracle tables and SQL....but sometimes it's enough to find the workaround!
thanks