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. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|