Using Expression for Default Value

[4charity]

I would like to automatically create the data for a field in my main customer
table. It is the ID code for the customer, which is the first 4 letters of
their last name, and the first letter of their first name. I am using the
expression:
=Left([LastName],4)&Left([FirstName],1).
This works fine in queries, but I really want it as the Default Value in my
table that info is entered into. Can this be done? I can't get it to work.
If not, suggestions on how to go about doing this?

Thanks.

 

[Golfinray]

One way would be to do a query that just returns the one field with the
customer id that you want. Call it Q_Temp or something like that. Make it a
make-table query. Then go to sql view and type:
alter table yourtablename
add column customerID

Run the query and it should add the column to your table. Try it on a copy
of your table first.

 

[4charity]

Thanks.
This worked great!

One way would be to do a query that just returns the one field with the
customer id that you want. Call it Q_Temp or something like that. Make it a
make-table query. Then go to sql view and type:
alter table yourtablename
add column customerID

Run the query and it should add the column to your table. Try it on a copy
of your table first.

I would like to automatically create the data for a field in my main customer
table. It is the ID code for the customer, which is the first 4 letters of
their last name, and the first letter of their first name. I am using the
expression:
=Left([LastName],4)&Left([FirstName],1).
This works fine in queries, but I really want it as the Default Value in my
table that info is entered into. Can this be done? I can't get it to work.
If not, suggestions on how to go about doing this?

Thanks.

 

[4charity]

Wait.... I spoke too soon.
2 questions:

1) when I go to SQL view, and add the suggested text, I get a syntax error
message. This already exists in the box:
SELECT Left([Last Name],4) & Left([First Name],1) AS CODE
FROM Clients1;
Do I add the other text, before or after this? Also, it seems there is some
symbol missing in between.

2) If I make it a make-table query, do I make a new table, or use the name
of my original one? And if its a new one, is that the table name I use in the
above SQL statment?

Thanks.
This worked great!

One way would be to do a query that just returns the one field with the
customer id that you want. Call it Q_Temp or something like that. Make it a
make-table query. Then go to sql view and type:
alter table yourtablename
add column customerID

Run the query and it should add the column to your table. Try it on a copy
of your table first.

I would like to automatically create the data for a field in my main customer
table. It is the ID code for the customer, which is the first 4 letters of
their last name, and the first letter of their first name. I am using the
expression:
=Left([LastName],4)&Left([FirstName],1).
This works fine in queries, but I really want it as the Default Value in my
table that info is entered into. Can this be done? I can't get it to work.
If not, suggestions on how to go about doing this?

Thanks.

 

[Golfinray]

Since you just want to create the one column in your table, erase the select
and all the other stuff. Just add the alter command and run the query (make
sure it is a make-table query.) It will not make a new table, just add the
one column to your existing table. If you leave the select and other commands
there, it will make a new table - you don't need that and you may get syntax
errors. Just in sql view, clean page, add the alter commands and then run.

Wait.... I spoke too soon.
2 questions:

1) when I go to SQL view, and add the suggested text, I get a syntax error
message. This already exists in the box:
SELECT Left([Last Name],4) & Left([First Name],1) AS CODE
FROM Clients1;
Do I add the other text, before or after this? Also, it seems there is some
symbol missing in between.

2) If I make it a make-table query, do I make a new table, or use the name
of my original one? And if its a new one, is that the table name I use in the
above SQL statment?

Thanks.
This worked great!

One way would be to do a query that just returns the one field with the
customer id that you want. Call it Q_Temp or something like that. Make it a
make-table query. Then go to sql view and type:
alter table yourtablename
add column customerID

Run the query and it should add the column to your table. Try it on a copy
of your table first.

:

I would like to automatically create the data for a field in my main customer
table. It is the ID code for the customer, which is the first 4 letters of
their last name, and the first letter of their first name. I am using the
expression:
=Left([LastName],4)&Left([FirstName],1).
This works fine in queries, but I really want it as the Default Value in my
table that info is entered into. Can this be done? I can't get it to work.
If not, suggestions on how to go about doing this?

Thanks.

 

[Duane Hookom]

I would not do this. IMHO you shouldn't be storing a value that can't be
calculated. If you really want this, you could add code in your data entry
form to update the ID Code based on your expression.