A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Using Expression for Default Value



 
 
Thread Tools Display Modes
  #1  
Old April 23rd, 2008, 12:53 PM posted to microsoft.public.access.tablesdbdesign
4charity
external usenet poster
 
Posts: 28
Default 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  
Old April 23rd, 2008, 01:28 PM posted to microsoft.public.access.tablesdbdesign
Golfinray
external usenet poster
 
Posts: 1,597
Default 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  
Old April 23rd, 2008, 02:28 PM posted to microsoft.public.access.tablesdbdesign
4charity
external usenet poster
 
Posts: 28
Default 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  
Old April 23rd, 2008, 03:15 PM posted to microsoft.public.access.tablesdbdesign
4charity
external usenet poster
 
Posts: 28
Default 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  
Old April 23rd, 2008, 04:16 PM posted to microsoft.public.access.tablesdbdesign
Golfinray
external usenet poster
 
Posts: 1,597
Default 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  
Old April 24th, 2008, 03:33 PM posted to microsoft.public.access.tablesdbdesign
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:12 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.