By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,735 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

duplicate records Query help

P: 19
I have a query that checks for duplicate records on 2 fields. the issue I am having is that the data unfortunately is case sensitive. So M != m but the query is case insensitive. Is there a way to make my query case sensitive so I can cancel out the False positives from the query?

here is the quey if that helps

Expand|Select|Wrap|Line Numbers
  1. SELECT tblPreReleaseData.From_Conn, tblPreReleaseData.From_term, tblPreReleaseData.Serial_number, tblPreReleaseData.Drawing_Number, tblPreReleaseData.Wire_number, tblPreReleaseData.Revision
  2. FROM tblPreReleaseData
  3. WHERE (((tblPreReleaseData.From_Conn) In (SELECT [From_Conn] FROM [tblPreReleaseData] As Tmp GROUP BY [From_Conn],[From_term] HAVING Count(*)>1  And [From_term] = [tblPreReleaseData].[From_term])))
  4. ORDER BY tblPreReleaseData.From_Conn, tblPreReleaseData.From_term;
  5.  
  6.  
any help is appreciated

CG
May 21 '07 #1
Share this Question
Share on Google+
11 Replies


JConsulting
Expert 100+
P: 603
I have a query that checks for duplicate records on 2 fields. the issue I am having is that the data unfortunately is case sensitive. So M != m but the query is case insensitive. Is there a way to make my query case sensitive so I can cancel out the False positives from the query?

here is the quey if that helps

Expand|Select|Wrap|Line Numbers
  1. SELECT tblPreReleaseData.From_Conn, tblPreReleaseData.From_term, tblPreReleaseData.Serial_number, tblPreReleaseData.Drawing_Number, tblPreReleaseData.Wire_number, tblPreReleaseData.Revision
  2. FROM tblPreReleaseData
  3. WHERE (((tblPreReleaseData.From_Conn) In (SELECT [From_Conn] FROM [tblPreReleaseData] As Tmp GROUP BY [From_Conn],[From_term] HAVING Count(*)>1  And [From_term] = [tblPreReleaseData].[From_term])))
  4. ORDER BY tblPreReleaseData.From_Conn, tblPreReleaseData.From_term;
  5.  
  6.  
any help is appreciated

CG

change the fields to UCase([yourfield])

makes everything upper case
May 21 '07 #2

Rabbit
Expert Mod 10K+
P: 12,324
change the fields to UCase([yourfield])

makes everything upper case
Actually, they want the opposite of this, a case sensitive compare.

Use StrComp(string1, string2, compare) where compare = 0 or vbBinaryCompare. If they match completely it'll return 0
May 21 '07 #3

P: 19
change the fields to UCase([yourfield])

makes everything upper case
thank you for your response, but what I am trying to do is to make sure not to capture the data that the only difference is the casing of the values. for example

record1
Field1= 1234
Field2=m

record2
Field1=1234
Field2=M

Record3
Field1=1234
Field2=m

what I need is
Record1 = Record3
but Record1 <> Record2 or Record2<>Record3

I hope this helps

CG
May 21 '07 #4

P: 19
Actually, they want the opposite of this, a case sensitive compare.

Use StrComp(string1, string2, compare) where compare = 0 or vbBinaryCompare. If they match completely it'll return 0

thank you is there a way I can add this to my sql query and if so where would it go or do I have to run this after my original query has run?

CG
May 21 '07 #5

Rabbit
Expert Mod 10K+
P: 12,324
Now that I think about it, StrComp won't work. I don't see a way to implement it. I also tried looking for a global setting you can use but I came up empty there as well. Google search didn't come up with anything either.

The only thing I can think of now is to write a procedure to recode the string as ASCII values.
May 21 '07 #6

NeoPa
Expert Mod 15k+
P: 31,271
Are you saying that
Expand|Select|Wrap|Line Numbers
  1. WHERE [Field] = 'M'
also finds records where [Field] = 'm'?
May 22 '07 #7

JConsulting
Expert 100+
P: 603
Now that I think about it, StrComp won't work. I don't see a way to implement it. I also tried looking for a global setting you can use but I came up empty there as well. Google search didn't come up with anything either.

The only thing I can think of now is to write a procedure to recode the string as ASCII values.
like this?

Expand|Select|Wrap|Line Numbers
  1. Function fGetASCII(varText) As String
  2.  
  3.     Dim varTemp
  4.     Dim intLoop As Integer
  5.     Dim intTemp As Integer
  6.  
  7.     Const cDelim = "."
  8.  
  9.     If IsNull(varText) Then
  10.         Exit Function
  11.     End If
  12.  
  13.     varTemp = StrConv(varText, 64)
  14.     For intLoop = 1 To Len(varTemp)
  15.         intTemp = Asc(Mid(varTemp, intLoop, 1))
  16.         If intTemp > 0 Then
  17.             fGetASCII = fGetASCII & cDelim & intTemp
  18.             intTemp = 0
  19.         End If
  20.     Next
  21.  
  22.     fGetASCII = Mid(fGetASCII, 2)
  23.  
  24. End Function
  25.  
May 22 '07 #8

Rabbit
Expert Mod 10K+
P: 12,324
like this?

Expand|Select|Wrap|Line Numbers
  1. Function fGetASCII(varText) As String
  2.  
  3.     Dim varTemp
  4.     Dim intLoop As Integer
  5.     Dim intTemp As Integer
  6.  
  7.     Const cDelim = "."
  8.  
  9.     If IsNull(varText) Then
  10.         Exit Function
  11.     End If
  12.  
  13.     varTemp = StrConv(varText, 64)
  14.     For intLoop = 1 To Len(varTemp)
  15.         intTemp = Asc(Mid(varTemp, intLoop, 1))
  16.         If intTemp > 0 Then
  17.             fGetASCII = fGetASCII & cDelim & intTemp
  18.             intTemp = 0
  19.         End If
  20.     Next
  21.  
  22.     fGetASCII = Mid(fGetASCII, 2)
  23.  
  24. End Function
  25.  
Yeah, something like that. I haven't tried it but why do you use Mid at the bottom?
May 22 '07 #9

JConsulting
Expert 100+
P: 603
Yeah, something like that. I haven't tried it but why do you use Mid at the bottom?
Rabbit,
it was used as part of a solution to join two tables...I forgot to take it out.
J
May 23 '07 #10

NeoPa
Expert Mod 15k+
P: 31,271
Nice work J.
I see we're keeping you busy :) I hope you're enjoying things here.
May 23 '07 #11

JConsulting
Expert 100+
P: 603
Nice work J.
I see we're keeping you busy :) I hope you're enjoying things here.
Thanks Neo,
I am having fun..pitching in where I can :o)
J
May 24 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.