Basic query questions concerning a linked FoxPro table

[Seth Schwarm]

First I will qualify - this is the first time I have ever linked to any type
of data via ODBC. So the root of my question may lie in my inexperience with
this interface.

I have successfully linked to a FoxPro table. I was tooling around running
some basic queries in an effort to familiarize myself with the nuances of a
FoxPro table, if any existed. And right off the bat I am in a quandry.

I am looking at basic address information in a patient table. I attempted a
criteria of "ok" for oklahoma. There are hundreds of OK addresses, yet no
results returned. So I thought maybe for some reason case sensitivity was in
place so I created an Asc function to know the ASCII number and sure enough,
the characters are coming back as capitalized characters, not lower case.

I always thought queries were not case sensitive by nature. So what is
going on? Am I just going to have to live with it? Can you explain why it
is this way and if there is anything I can do to remove the case sensitivity
when applying criteria?

I guess I am spoiled by having only worked in a pure Access environment to
date.

Thanks in advance,

Seth

 

[Albert D. Kallal]

I am looking at basic address information in a patient table. I attempted

a
criteria of "ok" for oklahoma. There are hundreds of OK addresses, yet no
results returned.

A criteria of ok in ms-access would also not match oklahoma.

ok # oklahoma

So, I not sure why this would work in FoxPro?

It is possible that the fox driver is case sensitive, but your above failing
of a match in the criteria would not work in ms-access either...

Perhaps your criteria is different then what you listed above?

 

[Seth Schwarm]

The actual contents of the State field is two characters - OK, TX, LA, KS, MO

That is why I entered the criteria of "ok"

Inside Access, you have to go to speacial measures to accomplish case
sensitive criteria. I have read about it in Access Cookbook by O'Reilly.

Seth

 

[Albert D. Kallal]

The actual contents of the State field is two characters - OK, TX, LA, KS,
MO

That is why I entered the criteria of "ok"

Inside Access, you have to go to speacial measures to accomplish case
sensitive criteria. I have read about it in Access Cookbook by O'Reilly.

Seth

ah..ok...I was thinking that some other issue make be at issue...

If "OK" works, and "ok" does not..then you are 100% right on the money
here....

 

[Van T. Dinh]

It does sound like Access / JET sends the criterion to FoxPro for processing
and FoxPro is case sensitive ...

Occasionally, I experience similar problems that on Access Queries based on
ODBC-linked Tables (in my case Microsoft SQL Server BE), parts of the
criteria are sent to SQL Server for processing while I expect this to be
handled by JET.

 

[Cindy Winegarden]

Hi Seth,

Yes, FoxPro is case sensitive. Ultimately the ODBC driver converts your
query to language that FoxPro understands. You can always use "...Where
Upper(StateAbbreviation) = Upper("ok") ..." although I don't know why in the
world you send a lower case state abbreviation when you know the state
abbreviations in your DBFs are in capital letters.

I wouldn't say you were "spoiled" in working in a pure Access environment;
I'd say your software experience was limited and with this task you get to
have a broader experience, which is a good thing.

--
Cindy Winegarden
(e-mail address removed)


VFP OLE DB: http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx
VFP ODBC: http://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx