If the new field should always be the first 5 characters of the surname, you
should not create this field in the table. Instead, do it in a query, by
entering this into a fresh column of the Field row in query design:
FirstFive: Left([Last Name], 5)
If you need to be able to sometimes assign a value other than the first 5
characters, use a Data Definition Language (DDL) query statement to add a
field to your table, and then an Update query to populate the field.
This code executes the DDL query:
Dim strSql As String
Dim db As DAO.Database
Set db = CurrentDb()
strSql = "ALTER TABLE MyTable ADD COLUMN MyNewTextField TEXT (5);"
db.Execute strSql, dbFailOnError
Set db = Nothing
For help with the Update query:
1. Create a new query into the modified table.
2. Change it to an Update query (Update on Query menu).
3. In the Update row under MyNewTextField, enter:
Left([Last Name], 5)
4. Run the query, or switch to SQL View (View menu) and copy what you see if
you want to execute it in code.
Unfortunately, the DDL query statement does not set AllowZeroLength to No in
recent versions, as it did in earlier versions. You will need to use DAO
code to modify this property, so you may prefer to use CreateField() instead
of the DDL statement.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"GL" <81*****@comcast.net> wrote in message
news:4b**************************@posting.google.c om...
Hi,
Is there a way to add a field to an existing table using a query of
some sort (without needing to manually add a field to the table). I
know how to do it with a make table query, but I have a specific need
to only add a new field to a table if possible. Here's a simplified
example of what I'm trying to do:
I get a file with the following two fields:
First Name
Last Name
I want to add a 3rd field onto that table to hold the 1st 5 characters
of the Last Name:
First Name
Last Name
1st 5 characters of Last Name
Can this be done without using a make table query and without manually
adding a field to the table & then using an update query?
Thanks,
G
