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
0
joe_G
5/29/2010 3:07:12 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
842 Views

Similar Articles

[PageSpeed] 22

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

0
PieterLinden
5/29/2010 4:34:54 PM
On Sat, 29 May 2010 08:07:12 -0700 (PDT), joe_G <joe2324@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]
0
John
6/2/2010 4:13:31 PM
Reply:

Similar Artilces:

CRM mobile express error / CRM3.0 / ASP.NET
I'm trying to setup CRM mobile for MS CRM 3.0, that I want to access from a PDA (HTC TyTNII). As far as I am aware I followed the configuration steps in setting up the Mobile Application website (with inetmgr - command etc.) and the Mobile administrative interface that I can access now from CRM itself (under settings -> Mobile Configuration). Also I can access the mobile website now from any client as well as from the PDA. I get the interface showing all entities I defined to show, and from there I can click on any of them, showing a list of all records. However then, when I c...

help with outlook 2003 #2
I can't view the same in box if I am logged in as Admin. versus home user. How can I do that. I have Win XP. Probably because both users have seperate OS-level user profiles. See if the info here helps in getting the two users to share their Outlook data: http://www.slipstick.com/outlook/olshare1.htm "Glo" <Glo@discussions.microsoft.com> wrote in message news:35D38467-7284-40E1-B7C3-B161FE4FAEE3@microsoft.com... >I can't view the same in box if I am logged in as Admin. versus home user. > How can I do that. I have Win XP. ...

Problems with a form in Access 2000
Hello all, We are using Windows 2000 pro with Access 2000. We are having this problem on one machine only. There is an input form that has a memo field in it. When the guy tries to hightlight some text in that memo field, it highlights the whole memo field as soon as he clicks in it instead of letting him choose what data to highlight. Sticky keys are not on, on his machine, I checked that. The database has a front end and a back end and he is working in the front end. To make the problem go away, he has to restart his machine. What could possibly be the problem? Thanks, GMC ...

HELP: Incoming Internet mails delay by the SMTP Connector at Exchange 2003 frontend
We just switched over to Exchange 2003 SMTP frontend and we notice mails always get queued up for 5 to 20 minutes. Force Connection, unfreeze didn't help. I can use telnet to port 25 of the backends to send a message instantly without any delay. So why the mails always get delay by the connector? Thanks Anything configured in the delivery options tab of your connector? James Chong andyhwconnet@hotmail.com wrote: > We just switched over to Exchange 2003 SMTP frontend and we notice > mails always get queued up for 5 to 20 minutes. Force Connection, > unfreeze didn't hel...

Access 2007.
Good Day, I'm working with an Access 2000 database that I inherited and I'm having some problems with it since we've moved to Office 2007. First I can't seem to get the creating 'Database.mdb' to stop - now I've read it has something to do with Compact and Repair and possibly permissions, but nothing seems to work. Also I've noticed when a developer makes a copy of our Test Database to make some enhancements, she is noticing a lot of the data is being duplicated thus creating bigger problems. Does anyone have any advise for me as to what I can do to ge...

REQ: Help! Dynamics COM object gives "Access Denied" from ASP.NET
I am trying to automate some data entry for our admin support team by authoring web-based pages which interact with Great Plains Dynamics. Currently, I am designing a proof of concept which will open the Item Maintenance page (IV_ITEM_MAINTENANCE) and programmatically fill the "Item Number" field, move focus to another field (so Dynamics will fill the Item Description info into the "Item Description" field), and then read the text from the "Item Description" field. I am able to get the code to properly work both in VB6 and VB.NET (as a WinForms Application). Ho...

Blocked access to attachments in mail and freezing up
Where I work, we use Outlook Express for email. Since we downloaded a patch on 5/6/04, we receive this message for almost all attachments we receive: "OE removed access to the following unsafe attachments to your email" followed by a list of the attachments. We are able to open the attachments by hitting the "Forward" button, but this is clunky and takes away some options (such as printing the attachment directly without opening it). Our updated VirusScan program finds no problem with any of these attachments. Also one of our accounts frequently gets stuck wh...

Please help
Hi, We have recently have migrated to WinXP/OfficeXP. Is there a way that we can setup Outlook to access the users Inbox automatically. At the moment the users are having to run the Outlook setup wizard and type in the exchange server name, Inbox name etc. the first time they login. We have about 300 users and are running Exchange 5.5 Thanks, Bruce. You need to look at the Office Resource Kit, there is a section on Profile Creation "Bruce" <codwars@hotmail.com> wrote in message news:6a8eb75a.0308110741.2d9fdb32@posting.google.com... > Hi, > > We have recently have ...

Outlook Express Setup
I have been trying for days to get my comcast account to work through my outlook. I can recieve mail but not send. I have tried everything I can find on the net to do. Nothing will work. Can anybody PLEASE help me? -- Silver Morris Outlook or Outlook Express? Your subject says OE, your message says Outlook. They are two different programs. If OE, you need to ask in an OE newsgroup. "Silver" <Silver@discussions.microsoft.com> wrote in message news:12D67A22-00A9-45B3-B94D-11EA645C8AD8@microsoft.com... :I have been trying for days to get my comcast account to work th...

Address parsing algorithm needed please
Hi, I'm looking for an Address parsing algorithm in VB6/VBA, preferably for UK addresses. I want to replicate what Microsoft Outlook does if you paste an complete address in the address field of a Contact record when the Check Details box pops up with an estimated parsing into Street, City, Region etc. (Have Googled to no avail.) TIA "Jon Lewis" <jon.lewis@cutthespambtinternet.com> wrote in message news:echZfgh4KHA.4156@TK2MSFTNGP06.phx.gbl... > Hi, I'm looking for an Address parsing algorithm in VB6/VBA, preferably > for UK addresses. ...

Cannot forward an email from a newsgroup in Outlook Express...
I use Outlook express as my news viewer. We use Outlook in our company as our default mail client - so Outlook is configured as my default mail app.. I want to forward an email from one of the news groups to a co-worker but when I click on forward in Outlook Express, the following error is displayed... "You don't have a simple MAPI compatible mailer installed. Micorosoft Outlook Express News cannot mail this message without a compatible mailer." Why can't it use Outlook (general) to do this? What do I need to do in order to be able to forward? I can "reply to gro...

Newbie needs help with Excel hyperlink issue!
I have hyperlinks between Excel and a Word document. I have set bookmarks so that the hyperlinks jump to the appropriate spots. Problem is this whichever Excel hyperlink I use first will open up word and start at the appropriate bookmark, however once Word is open clicking on any further hyperlinks does nothing. If I close Word again and click on any Excel hyperlink it will always go to the right spot on the document the first time Word opens it, but thereafter nothing! Help what do I do? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ V...

Reproducable bug with Access 2007 runtime: macro, rename table does not work
Dear all, I have a macro renaming the table X to A. This macro works fine when access 2K3 is installed. With run-time 2007 (and perhaps with Access 2007 also), I don't get any error message and the rename is NOT done. (Run-time allows me to delete and import objects, but not to rename?) The table has no relations. Can someone tell me if there is a patch/workaround for this? Seems to be to be a (severe) bug. Regards, Alain ...

HELP with Graphing.
For my particular data sets the x axis represents displacement, and the y axis represents load. I have multiple data sets; each has its own x and y values. The chart wizard only lets me pick one of the series x values for the entire graph. Is there a way i can graph all the data sets on the same plot so that each data set is referenced to its own x values, as opposed to just one of them. Plot the first data set Select the second one (both x and y values) and copy Click on Chart Use menu item Edit | Paste Special and indicate New Series with x-values in left column (with or without la...

Cannot create file error. Cannot open or save tif files...please h
Error I an receiving when trying to open a tif file as follows: Cannot create file:Remittance_advice.tif.right-click the folder you want to create the file in, and then click properties on the shortcut menu to check your permissions for the folder I am using windows vista and office 2007 outlook I cannot open or save a tif file. I can open all other file attachments. I can forward to another pc in my office and they can open. they can rename the tif and resend to me and I can open. original name is remittance_advice.tif and renamed is remittance_advice1.tif. this just start...

OLE DB error getting data from CRM db in onChange event
I'm trying to use the code below (in the onChange event of a picklist) to get some values from the CRM database so that, when the user selects a value from a picklist (here called "new_enduserid") I can get some associated values from some other tables and populate dependent text boxes etc. on the form. I'm getting an error "Multiple step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." var oDataValue = crmForm.all.new_enduserid.DataValue; /* This alert displays correctly */ alert(oDataValue[0].id); var connec...

Help Type conversion problem
Can any one help me in converting from CString to LPDISPATCH? I tried to do that but I failed Mon Did you cast from LPDISPATCH to Cstring yourself or did you get a CString Object containign a LPDISPATCH ? Can you send a code snippet ? Pat "monther" <monther@bis-t.com> wrote in message news:uEv3QlHSEHA.1340@TK2MSFTNGP12.phx.gbl... > Can any one help me in converting from CString to LPDISPATCH? > I tried to do that but I failed > Mon > > ...

Help, please: to remove Outlook 2000 ?
Help, please... On a pretty good Win XP PC, I need to upgrade from Outlook Express 6 to Outlook 2000. I installed Outlook and it imported all the folders/messages/identities from OE6. Outlook 2000 starts up, and all seems fine, and I'm looking at the new message that it created... "Welcome to Outlook 2000". If I try to view any other message, it locks up. I see the perpetual hourglass. It's dead. I want to Remove it, then re-install it. How do I remove it? I went to Control Panel, Add or remove programs... it's not there. Add/remove Windows components... it'...

Help! Set mailbox limit to low
I was configuring mailbox limtis and one user in particular, I must not have been paying attention and gave her a 250K mailbox instead of 250Mb. No she obviously cannot accept mail. I turned off the restriction completely, and she still has the limit. I even tried setting it to 250Mb instead, but it still will not deliver to her. Can anyone help me out? -Harry On Mon, 27 Jun 2005 10:05:27 -0400, "Harry Bates" <harry@-NOSPAM-rocksystems.-NOSPAM-net> wrote: >I was configuring mailbox limtis and one user in particular, I must not have >been paying attention and ga...

HELP Files Missing
I am running Windows XP Pro SP 2 and Office 2007 - and this is the second time I have lost my Help files. My IT department nor I could figure out a way to fix it without reinstalling office and losing all my settings. Has anyone else had this problem and found a solution? -- Catrina Whenever you get any problems like this, the first thing you should always do is to run Office diagnostics. Office Diagnostics is a very powerful tool to reset your Office setup to default level as far as all connections to various files are concerned. you don't need your Office CD to r...

Track project FTE per year for five years in Access?
I need to track individuals' FTE on projects for each year of the projects, which could be up to 5 years. The FTE could be for three different time periods within each year (e.g., summer, school year, calendar year for Year 1, Year 2, and so on). What are the best fields to create and table structure for this? Thanks! How about using a from date and to date in a table such that you can have multiple ranges per person. tblProject: ProjectId tblPerson: PersonId tblAssignment: ProjectId PersonId FromDate ToDate -- Dorian "Give someone a fish and they ea...

Error: Program trying to access e-mail addresses stored in Outlook
Have a popup window that comes up about 2-3 times a day. Says: " A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? If this is unexpected, it may be a virus and you should choose "no". I have been clicking no, but it keeps coming back. I have the lastest Anti-virus updates (Norton) How have others dealt with this? Thanks ...

help with percent #2
thanks i will try what you suggested and let you know if it works for m -- mikeee ----------------------------------------------------------------------- mikeeee's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1461 View this thread: http://www.excelforum.com/showthread.php?threadid=26245 ...

Error sending emails from Access through Outlook
I am having the typical problem when sending an email from Access. I get that error saying that another application is trying to send an email on my behalf or whatever. I heard that it can be bypassed by using sendObject, is this true? If so, how would i modify my code to use this.Function Email()'Set reference to OutlookOn Error GoTo Errhandler'Dim strBCC As StringDim db As DAO.DatabaseDim rst As DAO.RecordsetDim objOutl As Outlook.Application'Dim objEml As Outlook.MailItemDim i As IntegerSet db = CurrentDbSet rst = db.OpenRecordset("qryEmailReminder", dbOpenSnapshot)...

Help with referenced sheet
Hi all, I am attempting to feed data into a cell on sheet ABC that comes from another sheet that will be created programmatically (call it sheet XYZ). I start by creating the reference with sheet XYZ already existing, so that I can pick the cells I want off of it. I then delete sheet XYZ. My cells on ABC all then become #REF, which is okay with me. I save and close. I then run the program that takes some data, opens my excel file, and (re)creates sheet XYZ and puts the data I want onto it. The problem is that all of my cells on ABC that reference XYZ keep the #REF until I double ...