If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using Expression for Default Value
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. |
#2
|
|||
|
|||
Using Expression for Default Value
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" wrote: 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. |
#3
|
|||
|
|||
Using Expression for Default Value
Thanks.
This worked great! "Golfinray" wrote: 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" wrote: 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. |
#4
|
|||
|
|||
Using Expression for Default Value
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? "4charity" wrote: Thanks. This worked great! "Golfinray" wrote: 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" wrote: 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. |
#5
|
|||
|
|||
Using Expression for Default Value
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. "4charity" wrote: 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? "4charity" wrote: Thanks. This worked great! "Golfinray" wrote: 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" wrote: 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. |
#6
|
|||
|
|||
Using Expression for Default Value
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. -- Duane Hookom Microsoft Access MVP "4charity" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|