Case Sensitive VBA Query (yet another)

[SorenIX]

Hello everyone~

I find myself in a dire situation. I programmed a pretty nice Access application for the place I work at and so far I fixed any of my logic problems without having to post anything on forums. I somehow managed!

Now I have this one problem. I've looked around a lot, but nothing really solved my problem correctly or at least I couldn't understand it.

Here's my problem, I want to make this bit of code below case sensitive :

SQL_Entire = "WHERE [" & ReplaceStringSelectedField & "] LIKE '*" & SearchedString & "*';"
SQL_Beginning = "WHERE [" & ReplaceStringSelectedField & "] LIKE '" & SearchedString & "*';"
SQL_End = "WHERE [" & ReplaceStringSelectedField & "] LIKE '*" & SearchedString & "';"

What would you suggest? Thanks in advance.

Edit :
Whole code : http://pastebin.com/ygUVpEvj

 

[Pat Hartman]

Jet/ACE isn't case sensitive. If the BE is SQL Server, you have that option but don't ask me how and I think it applies EVERYWHERE which is actually more of a pain than you can imagine. I work with a third-party CRM app that is based on Pervasive and it IS case-sensitive. The users are constantly loosing things because not only do they have to remember what they typed, they also need to remember the case and account for typos!

If you have to do this, I would bring the query results into a local temp table and then using VBA use the Find function which is case sensitive.

 

[Cronk]

Given that asc("A")= 65 and asc("a")=97, you could write a function that compared the byte values of each character in the string.

 

[SorenIX]

I thought of doing this in two steps.

First creating a table with a SELECT query using the INSTR function to isolate the results, and then from that table doing the UPDATE.

I also looked for how to make a table from a query in VBA, but I couldn't relate to it. Made no sense to me.

Maybe you can help me with that?

 

[Cronk]

You don't need to create any temporary table.

Pass the search field and the search string to a function eg DoesItMatchCase([YourField],"abc*")

In the function check the ASC values for each character in both parsing from the start until the * is found and return a true or false depending on whether the ASC values match.

Incidentally, in the SQL you posted initially, there is a single quote missing after the *

Also, you did not say the purpose of of what you are attempting. There might be an easier way.

 

[SorenIX]

There indeed might be a better way. I'm trying to code a form that would act like the textbox that appears in many application such as Excel (AKA Ctrl+H), but for my database. Hence, I need to load different part of SQL code depending on what the user has ticked.

I learned VBA by myself so there might be a bit of weirdness in my code, but here is the code that handle the whole find/replace function : http://pastebin.com/ygUVpEvj


I think it's pretty good I figured it out this far by myself. xP
There are a few bugs still, mabe you can help me with that too, but first the Case sensitive is really not working.


Edit :
Types of Search :
SQL_Entire is the SQL that search in the entire field.
SQL_Beginning is the SQL that search at the beginning of a field.
SQL_End is teh SQL that search at the end of a field.

Types of Replacement :
SQL_Field is the SQL that overwrite the whole field by the chosen value.
SQL_String is the SQL that erase the searched string by the chosen value only.

 

[Pat Hartman]

@Cronk, I suggested a temp table (which I don't like and don't use unless there is no other way) because you shouldn't include a user defined query as criteria for a query against ODBC tables. Access cannot send your function to the database server so instead, it asks the database server to return ALL rows in the table and then the Jet/ACE query engine applies the criteria locally.

If your linked table is small (no more than a few thousand rows), this won't be terrible but if it is any bigger, you run the potential of creating enormous bandwidth bubbles that impact everyone else also.

Sometimes it is possible to influence how Jet/ACE break up a query for processing. I should have mentioned that you can try to do this by wrapping the existing query in a second query that uses the UDF. If the engine is smart enough, it will send the inner query to the server and then apply the final criteria locally. This will have the same effect as the temp table, two step solution I suggested earlier without the debris of a temp table. You will need to turn on a trace to see what Jet/ACE requests from the server to see if this works.

 

[SorenIX]

There is no server, the database is local. If anything it'll freeze your computer, but not impact other people. There are currently 400 rows in the table and could go to a few thousands, but I doubt it will.

As for the terms your using, I'm not too familiar with them. If you could come up with a sample code to fix my problem, that would help me more!

Thank you.

 

[Cronk]

You did not say what the purpose was for a case sensitive search. There might be an easier way around your requirement.

 

[stopher]

You can use INSTR to test for case sensitive inclusion. Use the compare option =0 for binary comparison.

Something like this (untested):

SQL_Entire = "WHERE INSTR(1,[" & ReplaceStringSelectedField & "], '" & SearchedString & "',0)>0"

hth
Chris

 

[SorenIX]

@Cronk

The purpose of a case sensitive search isn't usualy to search strings with case sensitivity? In other words, if I have "abc" and "Abc" and I search for "Abc" with case sensitivity, it should return "Abc" alone. That's pretty much what I wanna do.

@stopher

I tried your code, it worked if I searched for "A", it would replace the A's, but if I searched for "a", it would replace both A's and a's.


Perhaps I would really need to isolate the results of the search in another table. Unfortunatly, I searched for how to create a table from a select query and didn't find/understand anything.

Maybe you can help me with that?

 

[Pat Hartman]

Queries and tables are interchangeable for most uses. In this case, create a new query that selects your existing query. Then change the query type to Make table, give it a table name and save the query.

 

[Cronk]

SorenIX

"@Cronk
The purpose of a case sensitive search isn't usualy to search strings with case sensitivity? In other words, if I have "abc" and "Abc" and I search for "Abc" with case sensitivity, it should return "Abc" alone. That's pretty much what I wanna do."

I understood that but I was curious why you want to get only the "Abc" records. Having isolated these records, what are you going to do with them?

I thought I might learn something because I've not come across any situatio where there was such a requirement before.

 

[SorenIX]

@Pat Hartman

I'll try doing this.

@Cronk

My Access form is more like an application. It is very user friendly and I want to render the information management within the database itself very easy, but without the user being allowed to play into the raw tables himself.

Hence, since a few values in certain fields are object to change in a near future, I wouldn't want the user to waste his time going on the entry form, changing the same value from record to record. So, I gave him the option to do a massive find/replace directly in the table, without allowing him in the table.

Let's say the user wants to change all the values "V.2" into "V.3" because the version of a certain thing changed. In my main table, this "V.2" value could possibly be in many rows in the, let's say, "Version" column. It would be easier for him to just replace these value in one command, instead of having him go from a record to another checking if the "V.2" to "V.3" case applies.

Now, why the case sensitivity? Simply because you wouldn't want to replace "v.2" if its meaning is different than "V.2". (It's a bad example but you get the point)

Before you tell me it might be more of a pain in the ass for the user if he makes a mistake, I warn the user and display the results of the find/replace function and he can confirm or cancel them.

Hope it clarifies the case.


However, I talked about not allowing the user into the tables, but I can't make my form into a .ACCDE format. It's giving me crap about the file being to large. Any ideas?

 

[stopher]

Looking further it seems that the simple REPLACE function should suit your needs. For example:

UPDATE myTable SET myText = Replace([mytext],"aaAA","xxXX",1,1,0)

replaces the occurence of "aaAA" with "xxXX" starting the first character and only replacing once. The final zero parameter denotes a binary search i.e. case sensitive.

On you last post why would anyone change multiple instances of "version". Surely that indicates poor database design if your are having to maintain data like that (replicated data).

 

[Cronk]

Compiling to accde won't stop users being able to access tables.

There might be a compilation error that is giving the error. So try a Compact & Repair and Debug | Compile first.

 

[SorenIX]

UPDATE myTable SET myText = Replace([mytext],"aaAA","xxXX",1,1,0)

I don't quite get how it's case sensitive. Mind to explain? I like understanding before applying.

On you last post why would anyone change multiple instances of "version". Surely that indicates poor database design if your are having to maintain data like that (replicated data).

That was just an example. It does not apply to my database. What could apply is example :

Actual : "Marketing - John A."

John A. could get fired and John B. would take his place. So all "John A." would be replaced by "John B.", but the "Marketing - " would stay, becoming "Marketing - John B.".

Compiling to accde won't stop users being able to access tables.

There might be a compilation error that is giving the error. So try a Compact & Repair and Debug | Compile first.

How can you lock a database then? And how would you perform the repair?

 

[stopher]

I don't quite get how it's case sensitive. Mind to explain? I like understanding before applying.

Sure, the last parameter in the function is the compare parameter. By setting it to zero the replace does a binary comparision i.e. the text has to match bit for bit. See here.

That was just an example. It does not apply to my database. What could apply is example :

Actual : "Marketing - John A."

John A. could get fired and John B. would take his place. So all "John A." would be replaced by "John B.", but the "Marketing - " would stay, becoming "Marketing - John B.".

Again you have repeating data. Instead use a key such as EmployeePositionId. That way you can use the key throughout your database. Yet if you need to change the person in that position, that can be done in a single record. One of the key points of relationship design is that you avoid the cumbersome updating you describe and the risks of having inconsistent data.

 

[SorenIX]

Again you have repeating data. Instead use a key such as EmployeePositionId. That way you can use the key throughout your database. Yet if you need to change the person in that position, that can be done in a single record. One of the key points of relationship design is that you avoid the cumbersome updating you describe and the risks of having inconsistent data.

I must admit I don't have any relation in my database. I must also say that when I started this database, I had no precise goal, no guiding line. So I threw convinient stuff here and there, stuff poped in, etc.

Hmmmmmmmm... relations, I hadn't think about those. They weren't really needed till just now. And for some reason I'm dumb with them. I can do the rest, but I can't do relations, it never works.

 

[stopher]

I must admit I don't have any relation in my database. I must also say that when I started this database, I had no precise goal, no guiding line. So I threw convinient stuff here and there, stuff poped in, etc.

Hmmmmmmmm... relations, I hadn't think about those. They weren't really needed till just now. And for some reason I'm dumb with them. I can do the rest, but I can't do relations, it never works.

It's definitely worth investing some time in your table design before you get too far. Might be worth creating a new post under the "Theory and Practice of database design folder" and describe your business model in English terms. Also consider posting your d/b with sample data.

Hopefully someone can provide some good links on relational database design. You can of course google but I found that some tend to be better than others at explaining the main principles.