ms-access: specifying formatting for a column

Question

i would like all data that is entered into a specific column to always have a trailing comma and also i need the first character to always be a comma. how do i do this? the best solution is probably not in vba or sql but probably the properties of the table?

Answer 1

You will want to apply a Validation Rule or Input Mask to that field of the table (in the Table Design View).

Answer 2

If you just want to check if the value meets the requirements and display an error if it doesn't then use a validation rule like VeeArr suggested. If you want to correct the value if it doesn't meet the requirements then use a VBA event.

Answer 3

Consider whether you actually need those leading and trailing commas stored in the table. You can include them in a query whenever you need them.

SELECT
    some_text, 
    "," & some_text & "," AS with_commas, 
    IIf(Not IsNull(some_text), "," & some_text & ",", Null) AS with_commas_ignore_nulls
FROM YourTable;

If you really need the commas stored with the field, consider a form with a Before Update event to require the commas.

Private Sub txtsome_text_BeforeUpdate(Cancel As Integer)
    If Len(Me.txtsome_text & vbNullString) > 0 _
            And Not Me.txtsome_text Like ",*," Then
        MsgBox "some_text must start and end with a comma"
        Cancel = True
    End If
End Sub