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 6 2398 aj*******@yahoo .com (Ajay) wrote in
news:49******** *************** **@posting.goog le.com: 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 <Mi************ @Invalid.Com> wrote in
news:Xn******** ***********@130 .133.1.4: aj*******@yahoo .com (Ajay) wrote in news:49******** *************** **@posting.goog le.com:
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.Exec ute "ALTER TABLE tbl2002Transact ions ALTER
fldDescription Binary"
I think this will work for 2K as well.
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
"Ajay" <aj*******@yaho o.com> wrote in message
news:49******** *************** **@posting.goog le.com... 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(va rInput) 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$(varInp ut, lngCount, 1))
Next lngCount
End If
GetAscString = strOut
End Function
' *************** *************** *************** ********
HTH
Fletcher
"Fletcher Arnold" <fl****@home.co m> wrote in message
news:bn******** **@sparta.btint ernet.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(va rInput) 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$(varInp ut, 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
"TC" <a@b.c.d> wrote in message news:1066802481 .214681@teuthos ... "Fletcher Arnold" <fl****@home.co m> wrote in message news:bn******** **@sparta.btint ernet.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(va rInput) 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$(varInp ut, 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,11 6.
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
"Fletcher Arnold" <fl****@home.co m> wrote in message
news:bn******** **@sparta.btint ernet.com... "TC" <a@b.c.d> wrote in message news:1066802481 .214681@teuthos ... "Fletcher Arnold" <fl****@home.co m> wrote in message news:bn******** **@sparta.btint ernet.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(va rInput) 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$(varInp ut, 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,11 6. 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: C# newbie |
last post by:
Hi,
How can I make my query to return both "Bob" & "bob" (during search for a
string) ?
any idea?
thanks
|
by: vulcaned |
last post by:
Hi All,
In access97 I have a query who's record source is a linked sql table
and when I enter a value the Part nbr filed to select by (ex: A10295X )
I get back only records that have a case match. It will not select
records that would have a10295x.
How is it that an access query is case sensetive??? I've never run into
that before.
thanks
bobh.
|
by: Jeff S |
last post by:
I'm getting started with C# and was just wondering if C# veterans LIKE the
fact that the language is case sensitive. If so, how is case sensitivity
helpful? Do you create multiple variables, functions, and objects with
identical names that differ only in case?
Really wondering....
Jeff
|
by: Thomas Scheiderich |
last post by:
I thought I read that the case for the variable names is important.
For example
Dim Wheel As Integer
Wheel here is a different variable from WHEEL.
Is this correct?
|
by: Agnes |
last post by:
myGrid.mappingname = "Invoice" -> it doesn't work
myGrid.mappingname = "invoice" -> it seems work .
Case sensetive ?
| |
by: Agnes |
last post by:
I found that store procedure with vb.net (seems ignore case senstive)
I type "lax" as code, and it returns the correct answer , How come ??
I can't set the upper case in that textbox, BUT I need to check the data
'case'
Please help
|
by: A Bruce |
last post by:
hello,
I am attempting to convert a oracle database to postgresql and I am having
some problems creating a constraint across multiple columns which are a
mixture of case insensitive and case sensitive.
The original oracle database created an index with:
CREATE UNIQUE INDEX hatidx ON hats (upper(name), upper(server), login);
However postgresql can handle multiple columns in the index, or one function,
|
by: ambika_se |
last post by:
Hi all
I want to make a Xquery which is like this
for $x in doc("file:///C:/Science.xml")//tva2:title
where $x//node()
return $x
Its working perfectly but if i try for( biological science) its not which is becoz XQUery is case sensetive.I can use lower -case or upper-case function but i want more than that I need to look for words Science not science or SCIENCE.
Can anybody plzz help me with it. If you know how to make query insensitive...
|
by: vunet.us |
last post by:
So, I came across the problem every developer comes across with once:
case in SQL. My users login with their selected user name and when SQL
checks for user name value, case is ignored. For example, user1 is
equal to User1. This causes some problems in cases where I validate
user names for authentication to access some secure files. Anyway,
what can I do to make sure User1 cannot log in if database stores
user1?
Suggestions are very...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |