Deriving Client Code as Default Value

[Guest]

Our business uses Client Codes to identify clients and to derive Project
Codes. The Client Code consists of the first three letters of the client's
last name, and the first three digits of the client's street address. For
example, Richard Thompson, who lives at 1313 Mockingbird Lane would have a
Client Code of THO131, and a Project Code of THO131-xxxx. I'd like to enter
this formula:

Client Code: UCase(Left([Last Name],3)) & Left([Address],3)

in the Default Value field, but, Access won't allow it. It works well in a
Query, but I want the value to be calculated automatically once the Last Name
and Address fields have been filled in.

I've searched and read a few posts about the problems associated with
storing derived values, but, I don't think it's going to be an issue in this
case.

Thanks in advance.

 

[Jeff Boyce]

May I suggest that this is NOT a good idea for an identifier?

The following two clients would have the same Client Code, given your
algorithm:

Jane Smith
12345 Elm St
Houston, TX

Roger Smithsonian
123 Pennsylvania Ave.
Washington, D.C.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Don't use the default value for the field. Use the After_Update event of the
Address field to assign the value to the Client Code object. You should do
some error checking on the last name field (is it null? Is it at least 3 char
long?, etc).
Fair Winds, Capt Cook

 

[Guest]

We're a small company and probably only have 30-50 new clients per year.
Yes, it's possible to have duplicate client codes, but, this isn't going to
be used as a primary key - only as an identifier for us. If we ever did have
a duplicate (we haven't to date), we'd probably add an 'A' or 'B' to the end
of it.

Still looking for a solution.

Thanks.

May I suggest that this is NOT a good idea for an identifier?

The following two clients would have the same Client Code, given your
algorithm:

Jane Smith
12345 Elm St
Houston, TX

Roger Smithsonian
123 Pennsylvania Ave.
Washington, D.C.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Our business uses Client Codes to identify clients and to derive Project
Codes. The Client Code consists of the first three letters of the
client's
last name, and the first three digits of the client's street address. For
example, Richard Thompson, who lives at 1313 Mockingbird Lane would have a
Client Code of THO131, and a Project Code of THO131-xxxx. I'd like to
enter
this formula:

Client Code: UCase(Left([Last Name],3)) & Left([Address],3)

in the Default Value field, but, Access won't allow it. It works well in
a
Query, but I want the value to be calculated automatically once the Last
Name
and Address fields have been filled in.

I've searched and read a few posts about the problems associated with
storing derived values, but, I don't think it's going to be an issue in
this
case.

Thanks in advance.

 

[Marshall Barton]

Our business uses Client Codes to identify clients and to derive Project
Codes. The Client Code consists of the first three letters of the client's
last name, and the first three digits of the client's street address. For
example, Richard Thompson, who lives at 1313 Mockingbird Lane would have a
Client Code of THO131, and a Project Code of THO131-xxxx. I'd like to enter
this formula:

Client Code: UCase(Left([Last Name],3)) & Left([Address],3)

in the Default Value field, but, Access won't allow it. It works well in a
Query, but I want the value to be calculated automatically once the Last Name
and Address fields have been filled in.

I've searched and read a few posts about the problems associated with
storing derived values, but, I don't think it's going to be an issue in this
case.

The DefaultValue property won't do you any good because it
is only used at the time of the first keystroke on a new
record. You could try using the address text box's
AfterUpdate event:

Me.[Client Code] = UCase(Left([Last Name], 3) _
& Left(Address, 3))

 

[John W. Vinson]

Our business uses Client Codes to identify clients and to derive Project
Codes. The Client Code consists of the first three letters of the client's
last name, and the first three digits of the client's street address.

This kind of "intelligent key" was useful forty years ago when the main way to
look up records was to look at the labels on hanging file folders.

It's much less useful or necessary now.

Why have a six-digit code which your users must memorize? You can instead use
the tools Access provides, such as a combo box which could display the full
name and address while storing a meaningless numeric ID (that the user need
not even see, much less need to remember).

I realize that this code may be a tradition in your company, and that "new is
evil" - but perhaps it's time to drop this tradition and use the tools you're
offered!

John W. Vinson [MVP]

 

[Jeff Boyce]

Instead of using it as a Default (discussed elsewhere), or calculating it
and storing it, why not use a query to concatenate these pieces when you
need to use them? You could add the expression you provided as a new
"field" in a query (in design view).

Regards

Jeff Boyce
Microsoft Office/Access MVP