Prev: Select Top with aggregate function
Next: Query on a subform
From: joe_G on 29 May 2010 11:07
Hello,

I was hoping someone could assist with the following expression in
access that combines the Left function and Len function:

I have column called ID, which has about 700,000 records and each ID
can be anywhere from 7 to 10 characters; however, I all IDs to have
only 7 characters and this means removing characters that are greater
than 7. For example, smith09 is fine, but smith09990 would need to
reduced to smith09. Basically, i need an expression that will check
every ID and, if ID > 7 characters, use the left function to cut it to
7 characters.
Please let me know if you need more information:

I provided a table below of what I need. There are over 700,000
records in this table:


ID: expression

Smith09 Ok
Smith0978 Smith09
From: PieterLinden via AccessMonster.com on 29 May 2010 12:34
joe_G wrote:
>Hello,
>
>I was hoping someone could assist with the following expression in
>access that combines the Left function and Len function:
>
>I have column called ID, which has about 700,000 records and each ID
>can be anywhere from 7 to 10 characters; however, I all IDs to have
>only 7 characters and this means removing characters that are greater
>than 7. For example, smith09 is fine, but smith09990 would need to
>reduced to smith09. Basically, i need an expression that will check
>every ID and, if ID > 7 characters, use the left function to cut it to
>7 characters.
>Please let me know if you need more information:
>
>I provided a table below of what I need. There are over 700,000
>records in this table:
>
>ID: expression
>
>Smith09 Ok
>Smith0978 Smith09

SELECT oldName, Left$(oldName,7) As First7Chars
FROM MyTable;

then if you're sure... back up your table (because I don't want to be accused
of messing up your data irreparably!). THEN, when you're sure your original
data is okay...

UPDATE MyTable
SET oldName = Left$(oldName,7);

--
Message posted via http://www.accessmonster.com

From: John W. Vinson on 2 Jun 2010 12:13
On Sat, 29 May 2010 08:07:12 -0700 (PDT), joe_G <joe2324(a)gmail.com> wrote:

>Hello,
>
>I was hoping someone could assist with the following expression in
>access that combines the Left function and Len function:
>
>I have column called ID, which has about 700,000 records and each ID
>can be anywhere from 7 to 10 characters; however, I all IDs to have
>only 7 characters and this means removing characters that are greater
>than 7. For example, smith09 is fine, but smith09990 would need to
>reduced to smith09. Basically, i need an expression that will check
>every ID and, if ID > 7 characters, use the left function to cut it to
>7 characters.
>Please let me know if you need more information:
>
>I provided a table below of what I need. There are over 700,000
>records in this table:

You've got a possible major problem if this ID is intended to be a unique ID:
what if you have records with

SMITH09001
SMITH09123
SMITH09X
SMITH0935

These will all trunctate to SMITH09, losing any distinction between them.

How do you anticipate dealing with this issue - or is it irrelevant?
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Select Top with aggregate function
Next: Query on a subform