how to limit the characters in access to more than 255

How to limit the characters in access to more than 255? for example, I want it the memo or text box to limit it to max 300 characters.

开发者_运维技巧

In Access 2010

---

If you want to limit a memo field in a table to no more than 300 characters, open the table in design view and add this as the field's Validation Rule property.

Len([memo_field])<301

Substitute your field's name for memo_field. You can also add a Validation Text property to display a more user-friendly message when the rule is violated. With no Validation Text, that rule would produce this message ... which might not be very clear to a user:

*One or more values are prohibited by the validation rule 'Len([memo_field])<301' set for 'YourTableName.memo_field'. Enter a value that the expression for this field can accept.*

You also mentioned a text box. If it is a text box bound to a memo field, you can validate the character length in the text box's Before Update event. If the text box is named txtMemo_field:

Private Sub txtMemo_field_BeforeUpdate(Cancel As Integer)
    If Len(Me.txtMemo_field) > 300 Then
        MsgBox "Please limit data to maximum of 300 characters."
        Cancel = True
    End If
End Sub

After the message box, the cursor will be still located within the text box, and the user will not be allowed to move to another form field without supplying an acceptable value for txtMemo_field.

---

Just to address a point in @HansUp's answer:

Is Null Or Len([memo_field])<301 ... If you don't want to allow Nulls, drop the "Is Null Or" part.

There is no need to explicitly test for nulls in a constraint. A constraint doesn't have to evaluate TRUE for it to be satisfied.

If the Access database engine (ACE, Jet, whatever) actually had a spec it would read like this:

A table constraint is satisfied if and only if the specified search condition is not false for any row of a table.

According to three-valued logic required to handle nulls, the search condition LEN(NULL) < 301 evaluates to UNKNOWN and the table constraint would be satisfied (because UNKNOWN is not FALSE).

However, Access has no such spec, so we must test and see that the above assertions are indeed true (simply copy and paste into any VBA module, no references required, creates a mew blank mdb in the user's temp folder, then creates table, Validation Rule -- without the explicit test for null -- then attempts to add a null with success, Q.E.D.):

Sub WhyTestIsNull()

  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  On Error GoTo 0

  Dim cat
  Set cat = CreateObject("ADOX.Catalog")

  With cat

    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"

    With .ActiveConnection

      Dim Sql As String

      Sql = _
          "CREATE TABLE Test (" & _
          " ID INTEGER NOT NULL UNIQUE, " & _
          " memo_field MEMO" & _
          ");"
      .Execute Sql

    End With

    ' Create Validation Rules
    Dim jeng
    Set jeng = CreateObject("JRO.JetEngine")
    jeng.RefreshCache .ActiveConnection

    .Tables("Test").Columns("memo_field") _
       .Properties("Jet OLEDB:Column Validation Rule").Value = _
       "LEN(memo_field) BETWEEN 1 AND 300"

    jeng.RefreshCache .ActiveConnection

    Sql = "INSERT INTO Test (ID, memo_field) VALUES (1, NULL);"
    .ActiveConnection.Execute Sql

    Sql = "SELECT * FROM Test;"

    Dim rs
    Set rs = .ActiveConnection.Execute(Sql)
    MsgBox rs.GetString(2, , , , "<NULL>")

    Set .ActiveConnection = Nothing
  End With

End Sub

---

Change its Data type to Memo.

max field size of text field

Regards