Default value equal to date entered by user

AndrewBR

New member
Local time
Today, 23:39
Joined
Nov 16, 2012
Messages
4
I want a default value to equal the first 2 characters of field [first name] and first 3 characters of field [last name] and the numeric datevalue of [DOB] 'date of birth'.

In excel its easy,
C D E result formula
Andrew Tester 12/12/1980 AnTes-29567

Formula LEFT(c9,2)&LEFT(d9,3)&"-"&e9

How do I get same result in Access?
tried
Field 'PRN', a text field set as default =""""& left([first name],2}+LEFT(Last name],3), date()&""""

Access accepted the above statement but when I input user details the default does't work.
Suggestions welcome
 
2 questions: 1. Can/Should the user ever be able to change this field's data? Suppose you get your system to work and it puts 'AnTes-29567' in the table, can/would the user every override that and put something else? 2. What happens if someone changes their name, should this field's value be automatically changed as well to reflect the new name?

I'm really trying to understand if this will always be related to the data in the other 3 fields its based on, or if it could differ.
 
The field 'PRN' is the persons reference number, it's not a key field. and it will be used in correspondence - letter and emails as a shorthand way to identify indiviuals via any reply.
The PRN is not related to other data.
 
A little less cloudy, but still not the information I was looking for.

1. Can/Should the user ever be able to change this field's data? Suppose you get your system to work and it puts 'AnTes-29567' in the table, can/would the user every override that and put something else? 2. What happens if someone changes their name, should this field's value be automatically changed as well to reflect the new name?
 
If the user changed their name then yes the ref would also change.

My query is about the how to get the default value equal to the data in the fields. I can then apply the same logic in other areas.
 
And my query was to find out the purpose of what you wanted to accomplish, not the way you think you should go about it. Mentioning use of excel always puts us on guard about incorrect assumptions of how you should interact with your data. Which is true in this case as well.

You should not store this data in a field. You should not store any caculated data (Age, Balance, Elapsed Time, etc.) in a field, instead you should caculate it in a query when you need it. The below code is equivalent to what you accomplish in Excel:

Code:
PRN: Mid([first name], 1,3) & Mid([last name], 1,3) & "-" & 1*[DOB]

Create a query based on your table and paste the above code into a field.
 

Users who are viewing this thread

Back
Top Bottom