Force Lower Case

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am going to construct a table from a multitude of sources. Once done, all
my letters must be lower case (the dumb program receiving this file will
misinterpret upper case letters).

How do I make an Access table with all lower case letters?
 
Paperback,

Run an Update Query on the table, update all applicable fields to...
StrConv([NameOfField],2)
 
Paperback said:
I am going to construct a table from a multitude of sources. Once
done, all my letters must be lower case (the dumb program receiving
this file will misinterpret upper case letters).

How do I make an Access table with all lower case letters?
Click to expand...

You can create custom text and memo formats by using the following symbols.
SymbolDescription
@ Text character (either a character or a space) is required.
& Text character is not required.
< Force all characters to lowercase.
 
Paperback said:
How do I make an Access table with all lower case letters?
Click to expand...

As I always say, if you have a data rule such as only lowercase letters
are allowed then there should be a constraint (Validation Rule) in the
database to enforce the rule.

This is a tricky one because the engine in this regard is
case-insensitive e.g.

SELECT *
FROM MyTable
WHERE 'a' = 'A';

The expression 'a' = 'A' is true.

One way around this is to test the character code using the ASC()
function e.g.

SELECT *
FROM MyTable
WHERE ASC('a') = ASC('A');

The expression ASC('a') = ASC('A') is equivalent to 97 = 65 and is
false.

We can parse out the individual letters using a Sequence table, a
standard auxiliary table of integers e.g.

CREATE TABLE [Sequence]
(seq INTEGER NOT NULL PRIMARY KEY)
;
INSERT INTO [SEQUENCE] (seq) VALUES (1)
;
INSERT INTO [SEQUENCE] (seq) VALUES (2)
;
INSERT INTO [SEQUENCE] (seq) VALUES (3)
;

Let's keep things simple and assume the OP's column to test for
lowercase letters is fixed width three characters:

CREATE TABLE Test3 (
data_col CHAR(3) NOT NULL)
;
INSERT INTO Test3 (data_col) VALUES ('UP ')
;
INSERT INTO Test3 (data_col) VALUES ('UPP')
;
INSERT INTO Test3 (data_col) VALUES ('Mix')
;
INSERT INTO Test3 (data_col) VALUES ('lo ')
;
INSERT INTO Test3 (data_col) VALUES ('low')
;

Obviously, only the last row inserted should pass the rule 'lowercase
letters only'.

Here's the SQL to parse the letters:

SELECT T1.data_col,
S1.seq AS letter_pos,
MID$(T1.data_col, S1.seq, 1) AS letter,
ASC(MID$(T1.data_col, S1.seq, 1)) AS letter_code
FROM Test3 AS T1,
[Sequence] AS S1;

We can use the letter code in a subquery to identify the rows that pass
the rule 'lowercase letters only':

SELECT data_col
FROM Test3
WHERE NOT EXISTS (
SELECT *
FROM Test3 AS T1,
[Sequence] AS S1
WHERE Test3.data_col = T1.data_col
AND
ASC(MID$(T1.data_col, S1.seq, 1))
NOT BETWEEN ASC('a') AND ASC('z')
);

To show the rows that fail the rule, change the NOT EXISTS clause to
EXISTS. However, the construct that show the rows that pass the rule is
ultimately more useful because we can use the assertion in a CHECK
constraint:

DROP TABLE Test3
;
CREATE TABLE Test3 (
data_col NCHAR(3) NOT NULL,
CONSTRAINT Test3__data_col__lowercase_letters_only
CHECK (
NOT EXISTS (
SELECT *
FROM Test3 AS T1,
[Sequence] AS S1
WHERE Test3.data_col = T1.data_col
AND
ASC(MID$(T1.data_col, S1.seq, 1))
NOT BETWEEN ASC('a') AND ASC('z')
)
)
)
;
INSERT INTO Test3 (data_col) VALUES ('UP ')
;
INSERT INTO Test3 (data_col) VALUES ('UPP')
;
INSERT INTO Test3 (data_col) VALUES ('Mix')
;
INSERT INTO Test3 (data_col) VALUES ('lo ')
;
INSERT INTO Test3 (data_col) VALUES ('low')
;

This time, all the inserts fail except the last.

As is my usual courtesy, here's some VBA code to recreate and
demonstrate the above scenario:

Sub QueryCheck()
Dim cat As Object
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE Test2 ( data_col CHAR(3)" & _
" NOT NULL);"
.Execute _
"INSERT INTO Test2 (data_col)" & _
" VALUES ('UP ');"
.Execute _
"INSERT INTO Test2 (data_col)" & _
" VALUES ('UPP');"
.Execute _
"INSERT INTO Test2 (data_col)" & _
" VALUES ('Mix');"
.Execute _
"INSERT INTO Test2 (data_col)" & _
" VALUES ('lo ');"
.Execute _
"INSERT INTO Test2 (data_col)" & _
" VALUES ('low');"

.Execute _
"CREATE TABLE [Sequence] (seq INTEGER" & _
" NOT NULL PRIMARY KEY);"
.Execute _
"INSERT INTO [Sequence] (seq)" & _
" VALUES (1);"
.Execute _
"INSERT INTO [Sequence] (seq)" & _
" VALUES (2);"
.Execute _
"INSERT INTO [Sequence] (seq)" & _
" VALUES (3);"

Dim rs As Object
Set rs = .Execute( _
"SELECT T1.data_col, S1.seq AS letter_pos," & _
" MID$(T1.data_col, S1.seq, 1) AS letter," & _
" ASC(MID$(T1.data_col, S1.seq, 1))" & _
" AS letter_code" & _
" FROM Test2 AS T1, [Sequence] AS S1;")
MsgBox rs.GetString
rs.Close

.Execute _
"CREATE TABLE Test3 ( data_col NCHAR(3) NOT" & _
" NULL, CONSTRAINT Test3__data_col__" & _
"lowercase_letters_only" & _
" CHECK ( NOT EXISTS ( SELECT * FROM Test3" & _
" AS T1, [Sequence] AS S1 WHERE Test3.data_col" & _
" = T1.data_col AND ASC(MID$(T1.data_col," & _
" S1.seq, 1)) NOT BETWEEN ASC('a') AND ASC('z')" & _
" )));"

Dim lRows As Long
Dim data_value As String * 3

data_value = "UP "
lRows = 0
On Error Resume Next
.Execute _
"INSERT INTO Test3 (data_col)" & _
" VALUES ('" & data_value & "');", lRows
MsgBox _
"Attempt to insert " & _
"'" & data_value & "'" & vbCr & vbCr & _
"Error: " & _
IIf(Len(Err.Description) = 0, "(none)", _
Err.Description) & vbCr & vbCr & _
"Rows affected: " & CStr(lRows)
On Error GoTo 0

data_value = "UPP"
lRows = 0
On Error Resume Next
.Execute _
"INSERT INTO Test3 (data_col)" & _
" VALUES ('" & data_value & "');", lRows
MsgBox _
"Attempt to insert " & _
"'" & data_value & "'" & vbCr & vbCr & _
"Error: " & _
IIf(Len(Err.Description) = 0, "(none)", _
Err.Description) & vbCr & vbCr & _
"Rows affected: " & CStr(lRows)
On Error GoTo 0

data_value = "Mix"
lRows = 0
On Error Resume Next
.Execute _
"INSERT INTO Test3 (data_col)" & _
" VALUES ('" & data_value & "');", lRows
MsgBox _
"Attempt to insert " & _
"'" & data_value & "'" & vbCr & vbCr & _
"Error: " & _
IIf(Len(Err.Description) = 0, "(none)", _
Err.Description) & vbCr & vbCr & _
"Rows affected: " & CStr(lRows)
On Error GoTo 0

data_value = "Lo "
lRows = 0
On Error Resume Next
.Execute _
"INSERT INTO Test3 (data_col)" & _
" VALUES ('" & data_value & "');", lRows
MsgBox _
"Attempt to insert " & _
"'" & data_value & "'" & vbCr & vbCr & _
"Error: " & _
IIf(Len(Err.Description) = 0, "(none)", _
Err.Description) & vbCr & vbCr & _
"Rows affected: " & CStr(lRows)
On Error GoTo 0

data_value = "low"
lRows = 0
On Error Resume Next
.Execute _
"INSERT INTO Test3 (data_col)" & _
" VALUES ('" & data_value & "');", lRows
MsgBox _
"Attempt to insert " & _
"'" & data_value & "'" & vbCr & vbCr & _
"Error: " & _
IIf(Len(Err.Description) = 0, "(none)", _
Err.Description) & vbCr & vbCr & _
"Rows affected: " & CStr(lRows)
On Error GoTo 0

End With
Set .ActiveConnection = Nothing
End With
End Sub

A subquery in a CHECK constraint is a very powerful feature of Jet,
more powerful that its big sister SQL Server which has not implemented
the same functionality six years on. Yet, this Jet functionality is
seemingly very little used. Anyone know why this feature remains
neglected?

Jamie.

--
 
Back
Top