How do I set a text field in a table to be case sensitive?

[Guest]

I have import a table using ODBC and there is a field that is two alpha
characters which represents a name. These two character fields could be "AB"
or "Ab" or "aB" or "ab" and all four are unique for four different names. I
am trying to run a query which uses the two character field, yet it is
returning multiply names which may have the same characters but disregards
the case of the characters. Any thoughts on either settiing the field in the
table to be case sensitivity or should I focus on the query?

 

[John Vinson]

I have import a table using ODBC and there is a field that is two alpha
characters which represents a name. These two character fields could be "AB"
or "Ab" or "aB" or "ab" and all four are unique for four different names. I
am trying to run a query which uses the two character field, yet it is
returning multiply names which may have the same characters but disregards
the case of the characters. Any thoughts on either settiing the field in the
table to be case sensitivity or should I focus on the query?

Access (or to be more precise, Jet) is not case sensitive and cannot
be made case sensitive.

You can create a case sensitive query using the VBA function
StrComp():

SELECT <whatever>
FROM <table>
WHERE [fieldname] = "aB" AND StrComp([fieldname], "aB", 0) = 0

The StrComp function will return 0 if the two first arguments are
bit-for-bit identical. Using both the (non-case sensitive) equality
criterion and the StrComp will improve efficiency since it can use any
index on the field.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Howard]

I had this problem where the string was characters whose ascii code
represented a binary number (imported as an ID from a dbase table).
I solved it by writing a function that scanned through the string taking the
ASC of each character and building up a string that represented the ascii
values of the characters in the string. You can do the same thing in a
query. In my case the table was called STUDENTS and the field in question
was called STUDID so change the code below to suit. My ID code was 4
characters long so I used the code below


Link the table into Access. Then paste this text into the SQL view of a new
query...


SELECT DISTINCTROW Format(Asc(Mid([STUDID],1,1)),"000") &
Format(Asc(Mid([STUDID],2,1)),"000") & Format(Asc(Mid([STUDID],3,1)),"000")
& Format(Asc(Mid([STUDID],4,1)),"000") AS UniqueStudentRef, STUDENTS.* FROM
STUDENTS;

This will then give you another field called UniqueStudentRef which is
unique for each student.


In your case as you only have two characters you could use

SELECT DISTINCTROW Format(Asc(Mid([STUDID],1,1)),"000") &
Format(Asc(Mid([STUDID],2,1)),"000") AS UniqueStudentRef, STUDENTS.* FROM
STUDENTS;

This would give you the extra field and from then on you do all the work
with that field.



If you are importing from Dbase, ensure that these settings are correct in
your registry...

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\x.x\Engines\Xbase\DataCodePage=ANSI

This ensures that the DBase files are read correctly. There might be more
than one version of Jet installed, so change all versions.

Also if you are using an older version of Access (maybe v2.0) check the
msacc20.ini file in the Windows directory for the following entry...

[Dbase ISAM]

DataCodePage=ANSI

This also does the same thing as the registry settings.

Howard

I have import a table using ODBC and there is a field that is two alpha
characters which represents a name. These two character fields could be
"AB"
or "Ab" or "aB" or "ab" and all four are unique for four different names.
I
am trying to run a query which uses the two character field, yet it is
returning multiply names which may have the same characters but disregards
the case of the characters. Any thoughts on either settiing the field in
the
table to be case sensitivity or should I focus on the query?

Access (or to be more precise, Jet) is not case sensitive and cannot
be made case sensitive.

You can create a case sensitive query using the VBA function
StrComp():

SELECT <whatever>
FROM <table>
WHERE [fieldname] = "aB" AND StrComp([fieldname], "aB", 0) = 0

The StrComp function will return 0 if the two first arguments are
bit-for-bit identical. Using both the (non-case sensitive) equality
criterion and the StrComp will improve efficiency since it can use any
index on the field.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Howard]

I had this problem where the string was characters whose ascii code
represented a binary number (imported as an ID from a dbase table).
I solved it by writing a function that scanned through the string taking the
ASC of each character and building up a string that represented the ascii
values of the characters in the string. You can do the same thing in a
query. In my case the table was called STUDENTS and the field in question
was called STUDID so change the code below to suit. My ID code was 4
characters long so I used the code below


Link the table into Access. Then paste this text into the SQL view of a new
query...


SELECT DISTINCTROW Format(Asc(Mid([STUDID],1,1)),"000") &
Format(Asc(Mid([STUDID],2,1)),"000") & Format(Asc(Mid([STUDID],3,1)),"000")
& Format(Asc(Mid([STUDID],4,1)),"000") AS UniqueStudentRef, STUDENTS.* FROM
STUDENTS;

This will then give you another field called UniqueStudentRef which is
unique for each student.


In your case as you only have two characters you could use

SELECT DISTINCTROW Format(Asc(Mid([STUDID],1,1)),"000") &
Format(Asc(Mid([STUDID],2,1)),"000") AS UniqueStudentRef, STUDENTS.* FROM
STUDENTS;

This would give you the extra field and from then on you do all the work
with that field.



If you are importing from Dbase, ensure that these settings are correct in
your registry...

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\x.x\Engines\Xbase\DataCodePage=ANSI

This ensures that the DBase files are read correctly. There might be more
than one version of Jet installed, so change all versions.

Also if you are using an older version of Access (maybe v2.0) check the
msacc20.ini file in the Windows directory for the following entry...

[Dbase ISAM]

DataCodePage=ANSI

This also does the same thing as the registry settings.

Howard