MS Access Query Case Locking

[james.burden]

Hi there,
I have recently been writing my own database's for my company and have
founds these posts to be very helpfull for the problems that i have
hit. Unfortunatly i have hit one problem that i can not find the
solution for.

I have written a query so that when i click a button it runs and asks
me for the part number of a field. It works fine other than the case.
The tables are from a ODBC databse from another peice of software. All
the information has been written in uppercase and my databse will only
show results if the caps lock is on when inputting the search
criteria. I can not change the records in the ODBC so it is down to my
database.

Is there a way around this?

My query has the following criteria in it

Like "*" & [Part Number] & "* "

I want it to ignore the case in the tables and look for what is
entered when the part number is entered
i.e. a search for cn000100 will return no results and and search for
CN000100 will return 1 result.

I have another problem with a query when searching for description but
i think i might be able to sort that out when this problem is sorted.

Burden

 

[Baz]

Your request is very confusing. Do you want the searches to be
case-insensitive or case-sensitive?

Query criteria in Access are always case-insensitive unless you specify
otherwise. If you are finding that your searches are case-sensitive then
this must be caused by your database engine (which is what?).

However, you state that the data is in fact all upper case, so surely you
always want to search with upper case criteria? If so, then this might do
the trick:

Like "*" & Ucase([Part Number]) & "* "

n.b. are you sure you want that space after the 2nd asterisk?

 

[Burden]

Sorry for making it confusing.

I have read your below post and you are a star. It has worked.

Thanks for you help and quick response. I know where to come for help
again.

Burden

Your request is very confusing. Do you want the searches to be
case-insensitive or case-sensitive?

Query criteria in Access are always case-insensitive unless you specify
otherwise. If you are finding that your searches are case-sensitive then
this must be caused by your database engine (which is what?).

However, you state that the data is in fact all upper case, so surely you
always want to search with upper case criteria? If so, then this might do
the trick:

Like "*" & Ucase([Part Number]) & "* "

n.b. are you sure you want that space after the 2nd asterisk?

I have recently been writing my own database's for my company and have
founds these posts to be very helpfull for the problems that i have
hit. Unfortunatly i have hit one problem that i can not find the
solution for.

I have written a query so that when i click a button it runs and asks
me for the part number of a field. It works fine other than the case.
The tables are from a ODBC databse from another peice of software. All
the information has been written in uppercase and my databse will only
show results if the caps lock is on when inputting the search
criteria. I can not change the records in the ODBC so it is down to my
database.

Is there a way around this?

My query has the following criteria in it

Like "*" & [Part Number] & "* "

I want it to ignore the case in the tables and look for what is
entered when the part number is entered
i.e. a search for cn000100 will return no results and and search for
CN000100 will return 1 result.

I have another problem with a query when searching for description but
i think i might be able to sort that out when this problem is sorted.

Burden

 

[Baz]

No problem, pleased to be of assistance.

Sorry for making it confusing.

I have read your below post and you are a star. It has worked.

Thanks for you help and quick response. I know where to come for help
again.

Burden

Your request is very confusing. Do you want the searches to be
case-insensitive or case-sensitive?

Query criteria in Access are always case-insensitive unless you specify
otherwise. If you are finding that your searches are case-sensitive then
this must be caused by your database engine (which is what?).

However, you state that the data is in fact all upper case, so surely you
always want to search with upper case criteria? If so, then this might do
the trick:

Like "*" & Ucase([Part Number]) & "* "

n.b. are you sure you want that space after the 2nd asterisk?

I have recently been writing my own database's for my company and have
founds these posts to be very helpfull for the problems that i have
hit. Unfortunatly i have hit one problem that i can not find the
solution for.

I have written a query so that when i click a button it runs and asks
me for the part number of a field. It works fine other than the case.
The tables are from a ODBC databse from another peice of software. All
the information has been written in uppercase and my databse will only
show results if the caps lock is on when inputting the search
criteria. I can not change the records in the ODBC so it is down to my
database.

Is there a way around this?

My query has the following criteria in it

Like "*" & [Part Number] & "* "

I want it to ignore the case in the tables and look for what is
entered when the part number is entered
i.e. a search for cn000100 will return no results and and search for
CN000100 will return 1 result.

I have another problem with a query when searching for description but
i think i might be able to sort that out when this problem is sorted.

Burden

 

[Burden]

Is there anyway to completely ignore what case it is written in?

The reason being is that i have parts like 3.3mH and when trying what
you have given me it does not show up as it is putting my criteria
into caps.

Burden

No problem, pleased to be of assistance.


Sorry for making it confusing.

I have read your below post and you are a star. It has worked.

Thanks for you help and quick response. I know where to come for help
again.

Burden

Your request is very confusing. Do you want the searches to be
case-insensitive or case-sensitive?
Query criteria in Access are always case-insensitive unless you specify
otherwise. If you are finding that your searches are case-sensitive then
this must be caused by your database engine (which is what?).
However, you state that the data is in fact all upper case, so surely you
always want to search with upper case criteria? If so, then this might do
the trick:
Like "*" & Ucase([Part Number]) & "* "
n.b. are you sure you want that space after the 2nd asterisk?
Hi there,
I have recently been writing my own database's for my company and have
founds these posts to be very helpfull for the problems that i have
hit. Unfortunatly i have hit one problem that i can not find the
solution for.
I have written a query so that when i click a button it runs and asks
me for the part number of a field. It works fine other than the case.
The tables are from a ODBC databse from another peice of software. All
the information has been written in uppercase and my databse will only
show results if the caps lock is on when inputting the search
criteria. I can not change the records in the ODBC so it is down to my
database.
Is there a way around this?
My query has the following criteria in it
Like "*" & [Part Number] & "* "
I want it to ignore the case in the tables and look for what is
entered when the part number is entered
i.e. a search for cn000100 will return no results and and search for
CN000100 will return 1 result.
I have another problem with a query when searching for description but
i think i might be able to sort that out when this problem is sorted.
Burden

 

[Baz]

As I mentioned before, as far as Access is concerned all query criteria are
case-insensitive. The case-sensitive behaviour you are experiencing is thus
a problem with your database engine, not with Access. I am therefore unable
to suggest any way around it. What kind of database are you linking to?

Is there anyway to completely ignore what case it is written in?

The reason being is that i have parts like 3.3mH and when trying what
you have given me it does not show up as it is putting my criteria
into caps.

Burden

No problem, pleased to be of assistance.


Sorry for making it confusing.

I have read your below post and you are a star. It has worked.

Thanks for you help and quick response. I know where to come for help
again.

Your request is very confusing. Do you want the searches to be
case-insensitive or case-sensitive?

Query criteria in Access are always case-insensitive unless you specify
otherwise. If you are finding that your searches are case-sensitive then
this must be caused by your database engine (which is what?).

However, you state that the data is in fact all upper case, so

surely
you

always want to search with upper case criteria? If so, then this

might
do

the trick:

Like "*" & Ucase([Part Number]) & "* "

n.b. are you sure you want that space after the 2nd asterisk?

<[email protected]> wrote in message

Hi there,
I have recently been writing my own database's for my company and have
founds these posts to be very helpfull for the problems that i have
hit. Unfortunatly i have hit one problem that i can not find the
solution for.

I have written a query so that when i click a button it runs and asks
me for the part number of a field. It works fine other than the case.
The tables are from a ODBC databse from another peice of software. All
the information has been written in uppercase and my databse will only
show results if the caps lock is on when inputting the search
criteria. I can not change the records in the ODBC so it is down to my
database.

Is there a way around this?

My query has the following criteria in it

Like "*" & [Part Number] & "* "

I want it to ignore the case in the tables and look for what is
entered when the part number is entered
i.e. a search for cn000100 will return no results and and search for
CN000100 will return 1 result.

I have another problem with a query when searching for description but
i think i might be able to sort that out when this problem is

sorted.

Burden

 

[Keith Wilby]

As I mentioned before, as far as Access is concerned all query criteria
are
case-insensitive. The case-sensitive behaviour you are experiencing is
thus
a problem with your database engine, not with Access. I am therefore
unable
to suggest any way around it. What kind of database are you linking to?

For info ...

I've just tried

Like "*" & [Title] & "*"

on an ODBC linked Oracle table, entered "details" and it returns records
containing "DETAILS". Only if I use SQL Plus does the case sensitivity
comes into play.

Keith.

 

[Baz]

It's a long time since I've touched Oracle, but that's what I would expect.
SQL Server behaves the same. However, there's lots of other database
engines and formats out there with ODBC drivers, which is why it would be
interesting to know what kind of database this is.

 

[John W. Vinson]

Is there anyway to completely ignore what case it is written in?

At the cost of losing any indexing on the field, you could use

WHERE UCase([fieldname]) = UCase([Enter part number:])

This would return all three of "3.3MH", "3.3mh" and "3.3mH" if any one of them
were entered as a criterion.

As Baz has asked a couple of times - what database software are you using?
Does it have a case-sensitivity setting or query option?

John W. Vinson [MVP]

 

[Keith Wilby]

It's a long time since I've touched Oracle, but that's what I would
expect.
SQL Server behaves the same. However, there's lots of other database
engines and formats out there with ODBC drivers, which is why it would be
interesting to know what kind of database this is.

Hi Baz.

The point I was making was that Access doesn't care about case sensitivity
when connected to Oracle so why should it be different with any other data
source? Surely JET is doing the processing (unless you run a pass-through
query)? Unless I'm missing the point, which is entirely possible ...

Keith.

 

[Baz]

Oh I see. Yes, I think you have missed the point slightly.

ODBC drivers take the queries generated by Access and translate them into
queries against the target database. If the ODBC drivers or the target
database engine impose case-sensitivity, then Access' case-insensitivity
goes out the window. It doesn't happen with Oracle or SQL Server because
those database engines are themselves case-insensitive, but if some other
engine or ODBC driver does impose case-sensitivity then there is nothing
Access can do about it.

The workaround suggested (with reservations) by John Vinson works because it
avoids having the target database engine apply the criteria, instead it
drags the entire dataset back to the desktop so that Jet can apply the
criteria. Not good, but possibly unaviodable for the OP.

 

[Keith Wilby]

It doesn't happen with Oracle or SQL Server because
those database engines are themselves case-insensitive,

This is the bit I'm disagreeing with because when I use SQL Plus to query an
Oracle table it is case-sensitive. This returns rows:

select DRG_TITLE from OMS_DRG_TBL where DRG_TITLE like '%DESIGN%'

but this returns no rows:

select DRG_TITLE from OMS_DRG_TBL where DRG_TITLE like '%design%'

Are you saying that SQL Plus as a querying tool is providing the
case-sensitivity? I sense a penny dropping moment on the way ;-)

Thanks.

Keith.

 

[Baz]

So maybe Oracle is case-sensitive, I don't know, it's about 15 years since I
last touched Oracle and I don't have it available to play with! Even if
Oracle is case-sensitive it is entirely possible (likely even) that
Microsoft's Oracle ODBC driver caters for this. I imagine there is some
tool in Oracle similar to SQL Server's SQL Profiler, if so you would
probably find that the SQL arriving at the server is somewhat different to
the query you created in Access, so as to force case-insensitive behaviour.
Or, the ODBC driver is smart enough to know that it cannot get
case-insensitive behaviour from the database engine and hence
(inefficiently) forces Jet to apply the criterion, just as would explicitly
occur with John Vinson's Ucase suggestion. Either way, it's the ODBC driver
that is sorting it out, it isn't Access applying some magic.

There are lots of ODBC drivers out there for all sorts of weird and wacky
"databases". If the database software is case-sensitive, and the ODBC
driver does nothing about it, then Access can't fix the problem (it will not
even be "aware" that there is a problem). This appears to be the situation
that the OP is in.

 

[Keith Wilby]

So maybe Oracle is case-sensitive,

It would appear so.

There are lots of ODBC drivers out there for all sorts of weird and wacky
"databases". If the database software is case-sensitive, and the ODBC
driver does nothing about it, then Access can't fix the problem (it will
not
even be "aware" that there is a problem). This appears to be the
situation
that the OP is in.

And we never did find out what his BE platform was ...

 

[Baz]

It would appear so.



And we never did find out what his BE platform was ...

Nope!