A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Please help with access expression



 
 
Thread Tools Display Modes
  #1  
Old May 29th, 2010, 04:07 PM posted to microsoft.public.access.queries
joe_G
external usenet poster
 
Posts: 7
Default Please help with access expression

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
  #2  
Old May 29th, 2010, 05:34 PM posted to microsoft.public.access.queries
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default Please help with access expression

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

  #3  
Old June 2nd, 2010, 05:13 PM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Please help with access expression

On Sat, 29 May 2010 08:07:12 -0700 (PDT), 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:


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]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:17 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.