Solved Multi-field Search doesn't work on Linked Table, but works on Local Table [Upper/Lower Case] problem! (1 Viewer)

[Element007]

Hello , i have made a form with a text box that searches many columns with string "SearchFor" [based on Dynamically search multiple fields]

if i search for a string in LOCAL TABLE (case insensitive) the results are OK,

but in LINKED TABLE search is CASE-SENSITIVE?
linked table is from Sybase database linked thru ODBC, with works perfect also for updating the database [read/write]



code is exactly as in stated link above nothing changed !!
I tried playing with:
removing [Me.***] from the Vbacode
vbTextCompare, binary, database
Option Compare Database or Text
Option Explicit
vba code is in the FORM, its not in a MODULE,


What i want is whether i type Lower-Case or Upper-Case it should Search for Either [Lower/upper case in linked table]

 

[CJ_London]

Sybase text comparisons are case sensitive. I'm not familiar enough with it to know if that can be turned off (as you can turn it on in vba with vbBinaryCompare)

have you tried using the upper or lower functions?

upper(myfield)=upper(searchtext)

In access, all modules should have Option Compare Database and Option Explicit

 

[strive4peace]

hi Element007,

Welcome to Access World forums!

as CJ mentioned, you can use the UPPER function to wrap around fields to convert to uppercase for comparing. You might need to do this if they might not have a value. Nz is the null-to-zero conversion function. the optional second argument specifies what to use if there's no value.
Upper( Nz( [myfield], "") )

I'm curious if changing the Sort Order to General (or to something else if that is what it already is) would have any effect. Once it is change you have to compact/repair the database for it to take effect.

If that doesn't affect it, perhaps check sort order choices in Sybase?

 

[jdraw]

I'm not familiar with Sybase, but I found this 11 yr old article via google.

 

[Element007]

Hi CJ will i be able to type in lower case and show lower and upper case results?
in my database some columns contain text that are lower-case and other columns that cointain upper-case text, i need to search both!

Sybase text comparisons are case sensitive. I'm not familiar enough with it to know if that can be turned off (as you can turn it on in vba with vbBinaryCompare)

have you tried using the upper or lower functions?

upper(myfield)=upper(searchtext)

In access, all modules should have Option Compare Database and Option Explicit

 

[Element007]

Hi,
I changed the OPTIONS>GENERAL>NewSortOrder to GENERAL, and
OPTIONS>GENERAL>FILTER lookup options (myDatabase) ODBC Fields [checked]
and
OPTIONS>ObjectDesigners>Query design> SQL server Compatibile Syntax (ANSI 92) >this Database [checked] // Had to replace LIKE "*" with ALIKE "%"

Doesnt work either???

anyway another problems ocured (ps. btw im working Remotely in my office computer!!)

Also now when i try to update linkedTable it says CANNOT DEFINE FIELDS MORE THAN ONCE

as CJ mentioned, you can use the UPPER function to wrap around fields to convert to uppercase for comparing. You might need to do this if they might not have a value. Nz is the null-to-zero conversion function. the optional second argument specifies what to use if there's no value.
Upper( Nz( [myfield], "") )

I'm curious if changing the Sort Order to General (or to something else if that is what it already is) would have any effect. Once it is change you have to compact/repair the database for it to take effect.


If that doesn't affect it, perhaps check sort order choices in Sybase?
[/QUOTE]

 

[strive4peace]

hi Element007,

well it was a long shot ... also, changes won't take effect till you Compact/Repair -- and you should always backup your database before you do that

> "CANNOT DEFINE FIELDS MORE THAN ONCE"

are you using a query? calculated field names must be different than any field in the record source, including the field you're using in an expression. Where do you see this message?

 

[Element007]

@strive4peace

Whenever i try to update the linked Table i get [Cannot define fields more than once],
Workaround Fix: i created a view in Sybase-mydatabase and i linked ms access on the VIEW,
when i tried to delete then update the linked table from sybaseDb , all the tablenames were showing duplicate in msAccess LinkedTable manager, only the view i created shows one time!! but anyway its working!!!

hi Element007,

well it was a long shot ... also, changes won't take effect till you Compact/Repair -- and you should always backup your database before you do that

> "CANNOT DEFINE FIELDS MORE THAN ONCE"

are you using a query? calculated field names must be different than any field in the record source, including the field you're using in an expression. Where do you see this message?

 

[Element007]

@CJ_London

SrchText.Value = UCase(vSearchString) this makes the script search only for UPPER CASE? i cant get the results i want
i have values for example [day;Day;DAY] in diferernt columns! UCase gives me only [=DAY] result!!

Sybase text comparisons are case sensitive. I'm not familiar enough with it to know if that can be turned off (as you can turn it on in vba with vbBinaryCompare)

have you tried using the upper or lower functions?

upper(myfield)=upper(searchtext)

In access, all modules should have Option Compare Database and Option Explicit

 

[CJ_London]

SrchText.Value = UCase(vSearchString)

is not what I suggested - read my post again

 

[Element007]

@CJ_London , sorry i read it again but didnt understand were are you telling me to change,
In Query somwhere??


im not an expert, im a CopyPaster or Adjuster a better word ,

is not what I suggested - read my post again

 

[strive4peace]

@Element007, @CJ_London suggested using Upper to wrap text on both sides of the equal sign (=)

 

[CJ_London]

upper(myfield)=upper(searchtext)

 

[Element007]

i Adjusted the QUERY to:
SELECT * FROM mytbl WHERE (((UCase([mytbl].myfield1)) Like "*" & forms![myForm]!SrchText & "*"))

this works by MAKING all cellValues upperCase, although i cant tell whether the original values are lower or upper, but the results are OK!!


Thanks @CJ_London &Q @strive4peace

 

[strive4peace]

you're welcome, Element007 ~ happy to help

 

[NauticalGent]

im not an expert, im a CopyPaster or Adjuster a better word ,

I'll send you a membership application...