duplicate records Query help

[cgrider]

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

SELECT tblPreReleaseData.From_Conn, tblPreReleaseData.From_term, tblPreReleaseData.Serial_number, tblPreReleaseData.Drawing_Number, tblPreReleaseData.Wire_number, tblPreReleaseData.Revision
FROM tblPreReleaseData
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])))
ORDER BY tblPreReleaseData.From_Conn, tblPreReleaseData.From_term;

any help is appreciated

CG

[JConsulting]

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

SELECT tblPreReleaseData.From_Conn, tblPreReleaseData.From_term, tblPreReleaseData.Serial_number, tblPreReleaseData.Drawing_Number, tblPreReleaseData.Wire_number, tblPreReleaseData.Revision
FROM tblPreReleaseData
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])))
ORDER BY tblPreReleaseData.From_Conn, tblPreReleaseData.From_term;

any help is appreciated

CG

change the fields to UCase([yourfield])

makes everything upper case

[Rabbit]

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

[cgrider]

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<>Record 3

I hope this helps

CG

[cgrider]

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

[Rabbit]

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.

[NeoPa]

Are you saying that

WHERE [Field] = 'M'

also finds records where [Field] = 'm'?

[JConsulting]

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?

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

[Rabbit]

like this?

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

Yeah, something like that. I haven't tried it but why do you use Mid at the bottom?

[JConsulting]

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

[NeoPa]

Nice work J.
I see we're keeping you busy :) I hope you're enjoying things here.

[JConsulting]

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