Cross field Validation

[carrot2324]

Hello I desperately need to know how to create a validation rule for a database that I am constructing.

Each member of the database has a code which is dependant on their first and second name first and second names are in independent fields.
I need to create a validation rule that makes sure the member code is formatted as follows
The first three letters of their second name and the fourth letter of their first name
for example

Chris Sewell >>>>> is SEWC


I am using access 07
Thankyou so much !!!

 

[vbaInet]

Welcome to AWF

Is this field going to be the Primary Key?

 

[MSAccessRookie]

Hello I desperately need to know how to create a validation rule for a database that I am constructing.

Each member of the database has a code which is dependant on their first and second name first and second names are in independent fields.
I need to create a validation rule that makes sure the member code is formatted as follows
The first three letters of their second name and the fourth letter of their first name
for example

Chris Sewell >>>>> is SEWC


I am using access 07
Thankyou so much !!!

Welcome to the AWF Forums!


Two Questions:

1. Where will you be obtaining the User Names for comparison to the Member Code?
2. Using the current Formula, the names Chris SEWell, Carol SEWard and Charles SEWinger will all require a Member Code of SEWC. Have you considered what to do in cases such as these?

 

[carrot2324]

Welcome to AWF

Is this field going to be the Primary Key?

Thankyou Yes this is the primary key

Welcome to the AWF Forums!


Two Questions:
Where will you be obtaining the User Names for comparison to the Member Code?
Using the current Formula, the names Chris SEWell, Carol SEWard and Charles SEWinger will all require a Member Code of SEWC. Have you considered what to do in cases such as these?

Thanks the two names will be from the same table as the code (customer_tbl) and yes I was going to add a two digit number to the end of the code so the example would read SEWC67 to prevent this

 

[vbaInet]

A primary key is naturally the first thing that is created before any other field data is entered, although this is not a necessity.

As MSAccessRookie pointed out about the problems in your approach plus the fact that you confirmed this will be your primary key, this sort of validation can only be done at form level using VBA.

 

[vbaInet]

@Carrot well the most of the user is emphasize on the primary key,but i would suggest just go through these links its really help you lot.
"office.microsoft.com/en-us/access/HA100963121033.aspx"

It's not that level of validation. This OP's query is above table validation because auto-generation of a primary key is also required.