Select DISTINCT question

[barryjohnson]

Access 97
Data: ABOY, aboy, BBOY, bboy

When I run an SQL query: Select DISTINCT Data from table
I get this as a response:
ABOY, BBOY

It doesn't give me back the aboy & bboy.

How can I make the query see the other two values? It looks like Access97 or SQL sees them the same as the uppercase values and NOT as distinct values???

 

[ukcableguy]

Your option compare is probably set at text. This allows textual comparisons rather than binary. If you want to distinguish between A and a, set this option to binary(the default) and since a<>A in binary, these will be included in your resultset.

 

[barryjohnson]

Where do you set &quot;option compare&quot;? I can't find reference to it in my Access manuals.

 

[barryjohnson]

I found the Option Compare in VB and tried both text and binary. It didn't help.

 

[allanon]

Here is what I did and it works good:

1) created a module called Utilities and placed this code in it.

Option Compare Binary
Option Explicit

Public Function CompareStrings(ByVal String1 As String, ByVal String2 As String) As Boolean
On Error GoTo CompareStringsError
If String1 = String2 Then
CompareStrings = True
Else
CompareStrings = False
End If
Exit Function
CompareStringsError:
MsgBox (Err.Description)
End Function

** Note the Option Compare Binary **

2) called the function:

Me.txtOut = CStr(CompareStrings(Me.txtIn1, Me.txtIn2))

This works great.

Thanks to theoslogos for his solution.

 

[allanon]

I should read the hope post before I reply. OPPS... anyhow, to your other problem I did find a solution.

1) in the same module as above place this code:

Public Function CountStringValue(ByVal String1 As String) As Long
Dim i As Integer
On Error GoTo CountStringValueError

CountStringValue = 0
For i = 1 To Len(Trim(String1))
CountStringValue = CountStringValue + Asc(Mid(String1, i, 1))
Next i
Exit Function
CountStringValueError:
MsgBox (Err.Description)
End Function


Lucky us that you can call a user defined function in access.

2) Next here was my query:

SELECT DISTINCT CountStringValue(comparison.field1), comparison.field1
FROM comparison;

Here is my data:
field1 field2
ABOY ABOY
BBOY bboy
aboy ABOY
bboy BBOY
ABOY temp
BBOY temp
aboy temp
bboy temp

here is my result:
Expr1000 field1
299 ABOY
300 BBOY
427 aboy
428 bboy

as you can see you get an extra field this way BUT it works.