Query question!

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

Bob Vance

LastName(Text) and ClientID(Number)
Are say 2 fields in my query how can I get the expression in Uppercase
First 3 characters in LastName & OwnerID
to get something like SMI45
 
LastName(Text) and ClientID(Number)
Are say 2 fields in my query how can I get the expression in Uppercase
First 3 characters in LastName & OwnerID
to get something like SMI45
Click to expand...

An answer:

UCase(Left([LastName], 3) & OwnerID

The question:

WHY on earth would you want this?

It's redundant. It's no more unique than the OwnerID already is. It's not
mnemonic (is SMI34 or SMI127 Janet Smith?) It's an "intelligent key" (and no,
that's not a compliment, unfortunately).

It's easy to create this... but what use will it serve?
 
Thanks John, its a unique bank code given to my clients for a bank reference
on a statement, the first 3 letters gives you a heads up who it is but the
individual ID number is final, hmm what about say "St George Club" Would I
get STG I was going to use this in my query too =UCase .........Regards Bob

John W. Vinson said:
LastName(Text) and ClientID(Number)
Are say 2 fields in my query how can I get the expression in Uppercase
First 3 characters in LastName & OwnerID
to get something like SMI45
Click to expand...

An answer:

UCase(Left([LastName], 3) & OwnerID

The question:

WHY on earth would you want this?

It's redundant. It's no more unique than the OwnerID already is. It's not
mnemonic (is SMI34 or SMI127 Janet Smith?) It's an "intelligent key" (and
no,
that's not a compliment, unfortunately).

It's easy to create this... but what use will it serve?
Click to expand...
 
This works except for St George
BankCode: Left([OwnerLastName],3) & [OwnerID]
Thanks Bob


Bob Vance said:
Thanks John, its a unique bank code given to my clients for a bank
reference on a statement, the first 3 letters gives you a heads up who it
is but the individual ID number is final, hmm what about say "St George
Club" Would I get STG I was going to use this in my query too =UCase
.........Regards Bob

John W. Vinson said:
LastName(Text) and ClientID(Number)
Are say 2 fields in my query how can I get the expression in Uppercase
First 3 characters in LastName & OwnerID
to get something like SMI45
Click to expand...

An answer:

UCase(Left([LastName], 3) & OwnerID

The question:

WHY on earth would you want this?

It's redundant. It's no more unique than the OwnerID already is. It's not
mnemonic (is SMI34 or SMI127 Janet Smith?) It's an "intelligent key" (and
no,
that's not a compliment, unfortunately).

It's easy to create this... but what use will it serve?
Click to expand...
Click to expand...
 
UCase(Left(replace([OwnerLastName], " ",""), 3))

Bob Vance said:
This works except for St George
BankCode: Left([OwnerLastName],3) & [OwnerID]
Thanks Bob


Bob Vance said:
Thanks John, its a unique bank code given to my clients for a bank
reference on a statement, the first 3 letters gives you a heads up who it
is but the individual ID number is final, hmm what about say "St George
Club" Would I get STG I was going to use this in my query too =UCase
.........Regards Bob

John W. Vinson said:
LastName(Text) and ClientID(Number)
Are say 2 fields in my query how can I get the expression in Uppercase
First 3 characters in LastName & OwnerID
to get something like SMI45

An answer:

UCase(Left([LastName], 3) & OwnerID

The question:

WHY on earth would you want this?

It's redundant. It's no more unique than the OwnerID already is. It's not
mnemonic (is SMI34 or SMI127 Janet Smith?) It's an "intelligent key" (and
no,
that's not a compliment, unfortunately).

It's easy to create this... but what use will it serve?
Click to expand...


.
Click to expand...
Click to expand...
 
Brilliant Mark thanks for the help :)
Regards Bob

NameCode: UCase(Left(Replace([OwnerLastName]," ",""),3)) & [OwnerID]

Maarkr said:
UCase(Left(replace([OwnerLastName], " ",""), 3))

Bob Vance said:
This works except for St George
BankCode: Left([OwnerLastName],3) & [OwnerID]
Thanks Bob


Bob Vance said:
Thanks John, its a unique bank code given to my clients for a bank
reference on a statement, the first 3 letters gives you a heads up who
it
is but the individual ID number is final, hmm what about say "St George
Club" Would I get STG I was going to use this in my query too =UCase
.........Regards Bob

LastName(Text) and ClientID(Number)
Are say 2 fields in my query how can I get the expression in Uppercase
First 3 characters in LastName & OwnerID
to get something like SMI45

An answer:

UCase(Left([LastName], 3) & OwnerID

The question:

WHY on earth would you want this?

It's redundant. It's no more unique than the OwnerID already is. It's
not
mnemonic (is SMI34 or SMI127 Janet Smith?) It's an "intelligent key"
(and
no,
that's not a compliment, unfortunately).

It's easy to create this... but what use will it serve?
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top