Case sensetive query

[Ajay]

Hi
I am trying to do the following.
I have a set of records
Example
1 Test
2 test
3 TEST
4 ACCESS
5 EXCEL

If I'm running a query with a count I am getting the following result
record number of records
test 3
access 1
excel 1
My question is how can I make the query casesensetive so that my
query output
will be
record number of records
Test 1
test 1
TEST 1
Access 1
Excel 1

This means that the query shouldn't count test as one record but as 3
seperate records because there is a difference in the upper and lower
cases.

Thanks in advance

Ajay

[Lyle Fairfield]

aj*******@yahoo.com (Ajay) wrote in
news:49*************************@posting.google.co m:

Hi
I am trying to do the following.
I have a set of records
Example
1 Test
2 test
3 TEST
4 ACCESS
5 EXCEL

If I'm running a query with a count I am getting the following result
record number of records
test 3
access 1
excel 1
My question is how can I make the query casesensetive so that my
query output
will be
record number of records
Test 1
test 1
TEST 1
Access 1
Excel 1

This means that the query shouldn't count test as one record but as 3
seperate records because there is a difference in the upper and lower
cases.

You might TRY (after backing up your table) changing the field type to
Binary.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Lyle Fairfield]

Lyle Fairfield <Mi************@Invalid.Com> wrote in
news:Xn*******************@130.133.1.4:

aj*******@yahoo.com (Ajay) wrote in
news:49*************************@posting.google.co m:

Hi
I am trying to do the following.
I have a set of records
Example
1 Test
2 test
3 TEST
4 ACCESS
5 EXCEL

If I'm running a query with a count I am getting the following result
record number of records
test 3
access 1
excel 1
My question is how can I make the query casesensetive so that my
query output
will be
record number of records
Test 1
test 1
TEST 1
Access 1
Excel 1

This means that the query shouldn't count test as one record but as 3
seperate records because there is a difference in the upper and lower
cases.

You might TRY (after backing up your table) changing the field type to
Binary.

If you are using XP you could use:

CurrentProject.Connection.Execute "ALTER TABLE tbl2002Transactions ALTER
fldDescription Binary"

I think this will work for 2K as well.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Fletcher Arnold]

"Ajay" <aj*******@yahoo.com> wrote in message
news:49*************************@posting.google.co m...

Hi
I am trying to do the following.
I have a set of records
Example
1 Test
2 test
3 TEST
4 ACCESS
5 EXCEL

If I'm running a query with a count I am getting the following result
record number of records
test 3
access 1
excel 1
My question is how can I make the query casesensetive so that my
query output
will be
record number of records
Test 1
test 1
TEST 1
Access 1
Excel 1

This means that the query shouldn't count test as one record but as 3
seperate records because there is a difference in the upper and lower
cases.

Thanks in advance

Ajay

As an alternative to Lyle's which does not involve changing data types you
could simply write a query. Asuming the data 'Test', 'test', 'TEST' etc is
held in a field called MyField in the table MyTable, then the query would
be:

SELECT MyField, Count(*) AS MyTotal
FROM MyTable
GROUP BY MyField, GetAscString([MyField])

You would simply have to paste the following function into a code module:

' ************************************************** ***
Public Function GetAscString(varInput) As String

Dim lngCount As Long
Dim strOut As String

If Len(varInput) > 0 Then
For lngCount = 1 To Len(varInput)
strOut = strOut & Asc(Mid$(varInput, lngCount, 1))
Next lngCount
End If

GetAscString = strOut

End Function

' ************************************************** ***
HTH

Fletcher

[TC]

"Fletcher Arnold" <fl****@home.com> wrote in message
news:bn**********@sparta.btinternet.com...

(snip)

As an alternative to Lyle's which does not involve changing data types you
could simply write a query. Asuming the data 'Test', 'test', 'TEST' etc is held in a field called MyField in the table MyTable, then the query would
be:

SELECT MyField, Count(*) AS MyTotal
FROM MyTable
GROUP BY MyField, GetAscString([MyField])

You would simply have to paste the following function into a code module:

' ************************************************** ***
Public Function GetAscString(varInput) As String

Dim lngCount As Long
Dim strOut As String

If Len(varInput) > 0 Then
For lngCount = 1 To Len(varInput)
strOut = strOut & Asc(Mid$(varInput, lngCount, 1))
Next lngCount
End If

GetAscString = strOut

End Function

' ************************************************** ***

You would want to pad the Asc values to a fixed length, eg:
format(asc(...), "000").

Otherwise, you don't know whether "12345" was 1, 23, 45 or: 123, 45 or: 1,
234, 5 etc. - so in principle, you could erroneously match those three
strings, as just one string.

I realize this might not happen with the actual numbers in question. But as
a matter of principle, it would be safer to do the fixed-length thing.

TC

[Fletcher Arnold]

"TC" <a@b.c.d> wrote in message news:1066802481.214681@teuthos...

"Fletcher Arnold" <fl****@home.com> wrote in message
news:bn**********@sparta.btinternet.com...

(snip)

As an alternative to Lyle's which does not involve changing data types you could simply write a query. Asuming the data 'Test', 'test', 'TEST' etc is

held in a field called MyField in the table MyTable, then the query would be:

SELECT MyField, Count(*) AS MyTotal
FROM MyTable
GROUP BY MyField, GetAscString([MyField])

You would simply have to paste the following function into a code module:
' ************************************************** ***
Public Function GetAscString(varInput) As String

Dim lngCount As Long
Dim strOut As String

If Len(varInput) > 0 Then
For lngCount = 1 To Len(varInput)
strOut = strOut & Asc(Mid$(varInput, lngCount, 1))
Next lngCount
End If

GetAscString = strOut

End Function

' ************************************************** ***

You would want to pad the Asc values to a fixed length, eg:
format(asc(...), "000").

Otherwise, you don't know whether "12345" was 1, 23, 45 or: 123, 45 or: 1,
234, 5 etc. - so in principle, you could erroneously match those three
strings, as just one string.

I realize this might not happen with the actual numbers in question. But

as a matter of principle, it would be safer to do the fixed-length thing.

TC

Hi TC
This did cross my mind, however if you look at the select query, you will
see that I group first by MyField then by GetAscString([MyField]). This
means that the first 'group by' looks at the string - it doesn't need to
'understand' the code to correctly group 'test', 'Test' and 'TEST' together,
and 'one', two', 'three' apart. In other words, with the word 'test' for
example, 116,101,115,116 is already correctly grouped as the word 'test' -
it couldn't be grouped as 11,61,01,115,116.
It is only when (ignoring upper/lower case) the word is the same, that the
second 'group by' is needed and even then, the code generated is not in the
select clause so the user doesn't see it.
Therefore, I can't see a case (even theoretical) where this query would not
correctly group a list of words. However, experience has taught me to
anticipate being corrected.
Regards

Fletcher

[TC]

"Fletcher Arnold" <fl****@home.com> wrote in message
news:bn**********@sparta.btinternet.com...

"TC" <a@b.c.d> wrote in message news:1066802481.214681@teuthos...

"Fletcher Arnold" <fl****@home.com> wrote in message
news:bn**********@sparta.btinternet.com...

(snip)

As an alternative to Lyle's which does not involve changing data types you could simply write a query. Asuming the data 'Test', 'test', 'TEST'

etc

is

held in a field called MyField in the table MyTable, then the query would be:

SELECT MyField, Count(*) AS MyTotal
FROM MyTable
GROUP BY MyField, GetAscString([MyField])

You would simply have to paste the following function into a code module:
' ************************************************** ***
Public Function GetAscString(varInput) As String

Dim lngCount As Long
Dim strOut As String

If Len(varInput) > 0 Then
For lngCount = 1 To Len(varInput)
strOut = strOut & Asc(Mid$(varInput, lngCount, 1))
Next lngCount
End If

GetAscString = strOut

End Function

' ************************************************** ***

You would want to pad the Asc values to a fixed length, eg:
format(asc(...), "000").

Otherwise, you don't know whether "12345" was 1, 23, 45 or: 123, 45 or:

1, 234, 5 etc. - so in principle, you could erroneously match those three
strings, as just one string.

I realize this might not happen with the actual numbers in question. But

as

a matter of principle, it would be safer to do the fixed-length thing.

TC

Hi TC
This did cross my mind, however if you look at the select query, you will
see that I group first by MyField then by GetAscString([MyField]). This
means that the first 'group by' looks at the string - it doesn't need to
'understand' the code to correctly group 'test', 'Test' and 'TEST'

together, and 'one', two', 'three' apart. In other words, with the word 'test' for
example, 116,101,115,116 is already correctly grouped as the word 'test' -
it couldn't be grouped as 11,61,01,115,116.
It is only when (ignoring upper/lower case) the word is the same, that the
second 'group by' is needed and even then, the code generated is not in the select clause so the user doesn't see it.
Therefore, I can't see a case (even theoretical) where this query would not correctly group a list of words. However, experience has taught me to
anticipate being corrected.
Regards

Fletcher

Ah, you are exactly right!

Cheers,
TC