Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Force Lower Case

Discussion in 'Information Technology' started by Paperback Writer, Jul 28, 2006.

Tags:
  1. Paperback Writer

    Paperback Writer
    Expand Collapse
    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?
     
  2. Loading...

    Similar Threads Forum Date
    Ashdoc's Movie Review---Force 2 Theatre, Movies & Cinema Nov 20, 2016
    Interfaith Religious Freedom For Sikhs In The US Armed Forces Interfaith Dialogues Apr 20, 2016
    Hard Talk Toronto Youtube Star Jus Reign Was Forced To Remove His Turban At A California Airport Hard Talk Feb 24, 2016
    The force Blogs Oct 17, 2015
    How message of love from Sikh gurus are helping Pakistani Christians to fight forced conversions Sikh Sikhi Sikhism Oct 12, 2015

  3. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Paperback,

    Run an Update Query on the table, update all applicable fields to...
    StrConv([NameOfField],2)

    --
    Steve Schapel, Microsoft Access MVP

    Paperback Writer wrote:
    > 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?
     
  4. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Paperback Writer wrote:
    > 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?


    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.
    > Force all characters to uppercase.



    --
    Joseph Meehan

    Dia duit
     
  5. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Paperback Writer wrote:

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


    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.

    --
     

Share This Page