Access auto-entered data, possible?

[Tiger__]

Hi,
I have 2 fields, SUB and MAIN

SUB MAIN
0221 02215678
0320 03206654
0560 05603049

SUB is always the first 4 digits from MAIN. If I add data to this table in table view, I want the SUB to be auto entered from MAIN (left([MAIN];4)

Now the problem is, it isn't posible to use default value and set left([MAIN];4) for the field SUB so it would be auto entered with the 4 first digits from MAIN.

How can I do this IN TABLE VIEW... I tried almoest everything..is this even possible?!!

Many thanks,
Claudio

 

[jfgambit]

What you want to do needs to be done in a Form...

or create an update query and run it after you input all your data:

UPDATE NameofTable SET NameofTable.Sub = Left([Main],4);

 

[theprez]

You are violating an important rule of normalization. If you can always create sub from main, you have no need for the sub field, it can always be created using left(main,4). There is no need for this field. Eliminate the sub field or make sub and main separate tables with a relationship.

 

[Pat Hartman]

A different take on the normalization issue - First normal form says that fields should be atomic. That means that they cannot be further divided. If you can divide Main into 2 or more parts, your design violates first normal form and I guarentee you will have trouble with it in the future. There is currently someone posting regarding a slow query and his problem is caused by exactly this situation. Some bright light defined a single field to contain 2 attributes and our poster now needs to search a very large file without the database server being able to optimize the query to use an index because the criteria is only the first 4 characters of the key field.