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

Case sensetive query

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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)
Nov 12 '05 #2

P: n/a
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)
Nov 12 '05 #3

P: n/a
"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
Nov 12 '05 #4

P: n/a
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

Nov 12 '05 #5

P: n/a
"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
Nov 12 '05 #6

P: n/a
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

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.