Solved

Case sensitivity in Access SQL queries

Posted on 2006-10-20
49
510 Views
Last Modified: 2012-05-05
I have an SQL query that compares a field in an Access table ("uom") that resides in a separate mdb file, to a corresponding field in a linked table ("dbo_uom") that's in a Sybase database.

SELECT uom_id FROM uom IN 'd:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb' WHERE EXISTS (SELECT 1 FROM dbo_uom WHERE dbo_uom.uom_id = uom.uom_id)

The purpose of this query is to find uom_id's in the local table that have duplicates in the Sybase table.

We can have all sorts of combinations of cases in these tables; in other words, we might have:

UMOL
umol
Umol

I'm not sure how to make this query case-sensitive.  I've seen other solutions that work by making both items being compared lower-case, but that's kind of the opposite of what I'm looking for. If I've got "UMOL" in my local table, I only want the query to return "UMOL", not  "umol" or "Umol".

Thanks very much for any help you can provide!
Lynne
0
Comment
Question by:L_Malchiodi
  • 18
  • 14
  • 12
  • +1
49 Comments
 
LVL 39

Expert Comment

by:stevbe
ID: 17775434
you will have to use the StrComp function between the fields being linked.

Steve
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17775461
so ... instead of using JOIN syntax use the WHERE clause below ...

WHERE (((StrComp([tblEmp]![EmpName],[tblCustomer]![FirstName],0))=True));

0
 

Author Comment

by:L_Malchiodi
ID: 17775502
Thanks, Steve.  I tried running this:

SELECT uom_id FROM uom IN 'd:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb' WHERE (((StrComp([uom]![uom_id], [dbo_uom]![uom_id],0))=True));

...and it's prompting me for a parameter value for dbo_uom!uom_id.  

What am I doing wrong?
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17775544
sorry, I should have told you that part .... you need to include both tables in the FROM part ...

SELECT uom.uom_id
FROM uom IN 'd:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb', dbo_uom
(((StrComp([uom]![uom_id], [dbo_uom]![uom_id],0))=True));
0
 

Author Comment

by:L_Malchiodi
ID: 17775592
Now I'm getting a 'syntax error in the FROM clause':

SELECT uom.uom_id FROM uom  IN 'd:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb' , dbo_uom    WHERE  (((StrComp([uom]![uom_id], [dbo_uom]![uom_id],0))=True));
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17775648
ok ... this is tested, I apologize for not doing that first ... strComp returns 0 if they are equal.

(((StrComp([uom]![uom_id], [dbo_uom]![uom_id],0))=0));
0
 

Author Comment

by:L_Malchiodi
ID: 17775760
Still getting that syntax error.  Sorry, I feel like I'm missing something obvious:

SELECT uom.uom_id FROM uom  IN 'd:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb' , dbo_uom  WHERE  (((StrComp([uom]![uom_id], [dbo_uom]![uom_id],0))=0));
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17776057
hmmm ... I have not tried it with the IN ... database path ... hold on ... ack ... the IN reference causes a problem but just using the compltee fiel path syntax work OK ...

'this is my test ... for real ... not a direct linked table.
SELECT tblClient.ClientID
FROM [C:\Documents and Settings\steve\Desktop\LOCAL\ZDEV\Library\CodeLib.mdb].tblClient, tblCustomer
WHERE (((StrComp([tblCustomer]![FirstName],[tblClient]![ClientName],0))=0));

the only difference I can see now is that you are using a linked SQl Server table ... hopefully that will not make any difference.

SELECT uom.uom_id
FROM [d:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb].uom, dbo_uom
WHERE  (((StrComp([uom]![uom_id], [dbo_uom]![uom_id],0))=0));
0
 

Author Comment

by:L_Malchiodi
ID: 17776258
That runs, but it's not returning any matches. And there should be at least 10 matches.

I tried substituting in different vbCompareMethods.  With 1 and 2, I got matches, but the wrong ones. For instance, it matched 'SCORE' and 'Score'.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17778441
Each field in the Select clause must be related to a table, remote or local.  Perhaps:

Select a.uom, b.uom, FROM local AS a INNER JOIN 'd:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb' as b

Does that help?
0
 

Author Comment

by:L_Malchiodi
ID: 17797087
Still not working, still getting the syntax error in FROM clause....this is what I have now:

SELECT uom.uom_id FROM uom  INNER JOIN ['d:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb'].dbo_uom  WHERE dbo_uom.uom_id = uom.uom_id AND  (((StrComp([uom]![uom_id], [dbo_uom]![uom_id],0))=0));
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17797190
You've mixed and matched the guys' responses there a bit.
You need to take the single quotes out of the file path string.

Hmm - hang on... why am I subscribed to this thread..?
Weird!
0
 

Author Comment

by:L_Malchiodi
ID: 17797209
I tried taking the quotes out, same syntax error:

SELECT uom.uom_id FROM uom  INNER JOIN [d:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb].dbo_uom  WHERE dbo_uom.uom_id = uom.uom_id AND  (((StrComp([uom]![uom_id], [dbo_uom]![uom_id],0))=0));

0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17797285
Hmm actually looking at it you do have a syntax problem too.
You've implemented a Join - but without specifying upon which fields - immediate error. :-S
You don't want to specify the join anyway - as making it case sensitive is the whole issue..

SELECT uom.uom_id FROM uom , [d:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb].dbo_uom  
WHERE dbo_uom.uom_id = uom.uom_id AND  (((StrComp([uom]![uom_id], [dbo_uom]![uom_id],0))=0));

(Please don't accept this as the answer even if it works)
0
 

Author Comment

by:L_Malchiodi
ID: 17797345
This version runs, but doesn't return any matches. So, I guess something's not right with the StrComp?

SELECT dbo_uom.uom_id FROM dbo_uom , [d:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb].uom  
WHERE dbo_uom.uom_id = uom.uom_id AND  (((StrComp([uom]![uom_id], [dbo_uom]![uom_id],0))=0));
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17797417
What if you don't insist in it being a binary comparison...

... (((StrComp([uom]![uom_id], [dbo_uom]![uom_id],1))=0));

Does it return records?
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17797436
the vbcompare constant 0 tells it to make a binary comparison so score equals score but does not equal Score

what is the linked table name from SQL server database and what is the table name in your remote Access .mdb file?
0
 

Author Comment

by:L_Malchiodi
ID: 17797442
I've tried 0, 1 and 2 for the comparison, and it doesn't return anything for 0, and returns records for 1 and 2, but they're the wrong matches.

For instance, with 1 and 2, it sees "Score" = "SCORE".
0
 

Author Comment

by:L_Malchiodi
ID: 17797461
The Sybase database table name is 'dbo_uom', and the local table is 'uom'.

I had them flipped in one of my examples today, but I did correct it in the most recent example.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17797466
Then it doesn't seem to agree with what a real match is.
I wonder why.
So going back to a binary compare again.
See if this matters

   ... AND  (((StrComp(CStr([uom]![uom_id]), CStr([dbo_uom]![uom_id]),0))=0));
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17797467
another thought ...

That runs, but it's not returning any matches. And there should be at least 10 matches.
SELECT uom.uom_id
FROM [d:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb].uom, dbo_uom
WHERE  (((StrComp([uom]![uom_id], [dbo_uom]![uom_id],0))=0));


from which table do you want to see the ID from?
What version of Access do you have? ... are both fields in both places unicode?

0
 

Author Comment

by:L_Malchiodi
ID: 17797650
To LPurvis: adding the CStr didn't make any difference, still returns nothing.

To stevbe:
1. Doesn't matter what table I get the id from

2. I'm running Access 2002.

3. Unicode compression is No for both tables.  I can't modify the value for the linked table, so I set the local table's value to No to match it.  The query still returns nothing with a binary comparison.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17797831
... unicode compression will not make any difference ... it is prsented by Access as unicode so you need to convert from unicode to ANSI to get a binary match to work properly ... let's see what we can find ...
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17797963
Damn - time to cook.  This is fun!

(An alternative could be to create your own string comparison function - not too hard at all).
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 39

Expert Comment

by:stevbe
ID: 17798010
right ... strConv will do the unicode shuffle for you ... 128 converts from unicode to the system code page (I starting to get to the limit of my knowlegde and just regurgitating the help files) and 64 does th opposite. Now I am really guessing and thinking that WindowsXP prolly uses unicode so try turning Sybase into unicode first ...

WHERE  (((StrComp([uom]![uom_id], StrConv([dbo_uom]![uom_id], 64), 0))=0));

and if that does not work try the reverse ... Access unicode to system, code page ...

WHERE  (((StrComp(StrConv([uom]![uom_id], 128), [dbo_uom]![uom_id],0))=0));
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17798034
<string comparison function - not too hard at all>

lets see ... so you create byte arrays and compare byte by byte Or loop each Chr in the string ?
I think you still need to do the code page conversion ...
0
 

Author Comment

by:L_Malchiodi
ID: 17798083
Tried both forms, it still doesn't return anything....

SELECT dbo_uom.uom_id FROM dbo_uom , [d:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb].uom  
WHERE dbo_uom.uom_id = uom.uom_id AND (((StrComp(StrConv([uom]![uom_id], 128),[dbo_uom]![uom_id],0))=0));
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17798119
You tried it the other way around?
If Access and VBA use Unicode, Windows may or may not - and so vbFromUnicode may well not have a useful effect.

The next part of Steve's plan would undoubtedly be to have you try converting the linked table data.
(And if not - then convert both!)
0
 

Author Comment

by:L_Malchiodi
ID: 17798200
Yep, tried it both ways.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17798205
lets make this a bit simpler to see if we can find the real issue ... lets take a look at the acutal character code being returned in for the first letter ... I hope the example you posted of Score and score are real :-)

SELECT
    uom.uom_id,
    Asc(Left(uom.uom_id,1)) AS Expr1,
    dbo_uom.uom_id,
    Asc(Left([dbo_uom.uom_id],1)) AS Expr2
FROM [d:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb].uom.uom_id
INNER JOIN dbo_uom ON uom.uom_id = dbo_uom.uom_id;


Steve
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17798208
With *both* strings converted?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17798297
I agree - in that generating a character list - or even a binary or hex string string for comparison purposes would be fruitful.

(considered when I'd initially wondered about field padding - but the fact that the non binary matches worked made that seem redundant).
0
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 250 total points
ID: 17798324
<With *both* strings converted?> ahhh ... convert the ehoe shebang to ansi and forget the *system code page* :-)

I like that idea... oops ... we got a bit more work to make it work for real if we can proof that this is the problem / solution to begin with ... StrConv only does the first character ... Leigh, we may need your function after all :-)

SELECT
    uom.uom_id,
    StrConv(uom.uom_id, 64) AS Access_S,
    dbo_uom.uom_id,
    StrConv([dbo_uom.uom_id], 64)) AS Sybase_S
FROM [d:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb].uom.uom_id
INNER JOIN dbo_uom ON uom.uom_id = dbo_uom.uom_id
WHERE uom.uomid='Score';

this is not the final soultion ... just trying to see if we can get the S from Access matches the S from Sybase but not match the s from either ... let us know what the character return codes are for each
0
 

Author Comment

by:L_Malchiodi
ID: 18019539
So sorry to have left this for so long....I got pulled onto another project and didn't have time to come back to this.  If anyone's still out there and interested, I ran the SQL above (fixed a couple of minor syntax problems):

SELECT
    uom.uom_id,
    StrConv(uom.uom_id, 64) AS Access_S,
    dbo_uom.uom_id,
    StrConv([dbo_uom.uom_id], 64) AS Sybase_S
FROM [d:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb].uom
INNER JOIN dbo_uom ON uom.uom_id = dbo_uom.uom_id
WHERE uom.uom_id='Score';


And got the following:

uom.uom_id = SCORE
Access_S = S
dbo_uom.uom_id = Score
Sybase_S = S

Thanks very much,
Lynne
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18021083
so based on the first character only it looks to be working correctly?
0
 

Author Comment

by:L_Malchiodi
ID: 18021147
Unless I've thoroughly confused myself (always a possibility), I'm not sure it's working.  I don't want to match "SCORE" and "Score",  I need them to be recognized as separate strings.
0
 

Author Comment

by:L_Malchiodi
ID: 18021328
Yes, I was confused....sorry.  I agree that it was successful in that it correctly determined that the first letter was an uppercase "S".
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 250 total points
ID: 18022185
I don't think that it's only returning the first character.
I think that's all that the query can display willingly.

For example - do the same in code and you see the non-printable characters among the ASCII ones.
If you click in a query result enough - they'll show there too.

Are the comparisons working for you though?
It depends on Jet SQL's confort with comparing the nonASCII characters.

If we go with that function call to split it up - then who knows.
We could always only return the ASCII characters from the text.  Or we could even use the ASCII codes - to make it as unambiguous as we like.

For example - if you paste the following in a standard module.

Function fGetASCII(varText) As String
   
    Dim varTemp
    Dim intLoop As Integer
    Dim intTemp As Integer
   
    Const cDelim = "."
   
    If IsNull(varText) Then
        Exit Function
    End If
   
    varTemp = StrConv(varText, 64)
    For intLoop = 1 To Len(varTemp)
        intTemp = Asc(Mid(varTemp, intLoop, 1))
        If intTemp > 0 Then
            fGetASCII = fGetASCII & cDelim & intTemp
            intTemp = 0
        End If
    Next
   
    fGetASCII = Mid(fGetASCII, 2)
   
End Function

Then run

SELECT
    uom.uom_id,
    dbo_uom.uom_id,
FROM [d:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb].uom
INNER JOIN dbo_uom ON fGetASCII([uom].[uom_id]) = fGetASCII([dbo_uom].[uom_id])
WHERE uom.uom_id='Score';

It'll run like a dog! :-)
But see what it brings back.
(Assuming that the existing comparison isn't plenty).

(You can test that by just running what one of Steve and I suggested earlier...)
Something more like
SELECT
    uom.uom_id,
    dbo_uom.uom_id,
FROM [d:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb].uom
INNER JOIN dbo_uom ON StrConv([uom].[uom_id], 64) = StrConv([dbo_uom].[uom_id], 64)
WHERE uom.uom_id='Score';
0
 
LVL 44

Expert Comment

by:GRayL
ID: 18022490
I think it has to be something like:

SELECT
    uom.uom_id,
    dbo_uom.uom_id,
FROM uom IN  "d:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb"
INNER JOIN dbo_uom ON StrConv([uom].[uom_id], 64) = StrConv([dbo_uom].[uom_id], 64)
WHERE uom.uom_id='Score';
0
 

Author Comment

by:L_Malchiodi
ID: 18030246
Got it!  I created your GetASCII function, and ran this query:

SELECT uom_id FROM uom IN 'd:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb' WHERE EXISTS (SELECT 1 FROM dbo_uom WHERE Tstr(dbo_uom.uom_id) = Tstr(uom.uom_id) AND GetASCII(TStr(dbo_uom.uom_id)) = GetASCII(Tstr(uom.uom_id)))

(TStr is a string-trimming function that we created. Needed to use it here because uom_id is stored as a char(5) in the Sybase database, so anything shorter than 5 characters is padded with spaces in Access.)

It returned exactly what it was supposed to.  I was even able to use GetASCII in another check, one that looks for duplicate uom_id's in the local table:

SELECT uom_id FROM uom IN 'd:/access/newdvlp_266/custom/ImportUtility_Workfile_2002.mdb' GROUP BY uom_id, GetAscii(Tstr(uom_id)) HAVING count(GetASCII(TStr (uom_id))) > 1

Thank you all SO much for the help with this.   Aside from getting my issue resolved, I learned a lot on this one.   You guys are great!
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18030327
Cool - glad you're sorted.
And thanks for the thought... (and I'm not normally one to be pushy about these things) but could you please post in community support and ask that they re-open this question for you (so you can split it) - or just have them split it right away themselves.

The dialogue Steve and I had through this getting here was absolutely crutial in you being where you are.  I think it's important because :
1) future readers should be made aware of it (and if the accept is split they'll be more likely to see the other bits
2) there's good info there and thought processes
3) I was just dotting the i's and crossing the t's in that last post.  (Anyone could have come up with that function).
4) it was a fun back and forth and wouldn't be right otherwise :-)

Thanks muchly!
(And if Steve tries to talk you out of it just hold your hands over your eyes and ears and shout "La la laaaa" :-)
Cheers!
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18030522
la la la lal .... while I agree that the StrConv, unicode, system code page etc are very interesting (in a geeky kind of way)... do whatever you would like with the points ... Leigh is way ahead of me anyway :-)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 18032319
L_Malchiodi:  >Got it!<  Didn't my previous post contribute to that?
0
 

Author Comment

by:L_Malchiodi
ID: 18036965
You know, I really wanted to split the points, since I learned so much from the whole discussion, but wasn't sure what the 'etiquette' was regarding point-splitting.   Thanks for suggesting it, I will definitely take care of getting the points split.   Thanks again to all!
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18037809
Good on ya.  Cheers! ;-)
0
 

Author Comment

by:L_Malchiodi
ID: 18046489
..and the points have been split.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18048058
Cool.  Cheers.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 18049254
Did you ever feel invisible?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now