Solved

Access 2007 - Instr

Posted on 2011-09-25
11
273 Views
Last Modified: 2012-05-12
My OS is windows 7 and I use Access 2007.  I have a Field which is called Name is  a Text type.
The data in this field contains the first, middle and last name separated by a space.
I would like to separate the first and middle names and put them in a field called First Name
And the last name and put them in a field caleld LastName.
I am trying to use an ADO function to loop thru all the names, and then find the spaces.
Thus if the data has only one space, then the first part would have to be the first name and the rest goes into the last name.
But if the data has more than one spaces, i.e. eg : John Phil Smith, then John Phil would go into the first name and Smith to the last name.  
Is using the InStrr method to find this space the correct way to do it.
The the code to my mind would be like :
 If 1 space, Then
     take all data to the left of that space and put it into a var called First Name
     and all data to the right of that space, put it into a var called Last Name
 Now update the record in the Table.
Go on looping until end of file.

Hope some expert could  help me with the coding, and if possible also the ADO code.  
The name of the Table is called Seniors Club
 The field name where the data is to be extracted from is called Name, and is a text type field.
The firstname field is text type and also the lastname field is also text type.
Thank u.
0
Comment
Question by:jegajothy
  • 5
  • 4
  • 2
11 Comments
 
LVL 13

Expert Comment

by:lee555J5
Comment Utility
First, "Name" is a reserved word in Access, and you should not use is for anything else. Use something else such as FirstName, LastName, or FullName. You could put it in [] as in [Name], but it's best to avoid it altogether.

Second, Instr is the command to use. This is only valid if all your names to split are constructed according to your example rules, as in at most 2-3 words separated by spaces. What about Jr. Sr. II? What about hyphens? What about 4 or more names?

Just some thoughts.

Lee
0
 
LVL 13

Expert Comment

by:lee555J5
Comment Utility
Sorry, the command to use to find the last space is InStrRev.

FirstName = Mid([Name], 1, InstrRev([Name], Chr(32)))
LastName = Mid([Name], InstrRev([Name], Chr(32)))
0
 

Author Comment

by:jegajothy
Comment Utility
In response to lee, thank u, looks like I am out of date of the new InStrRev command.
Thank u.  Hope someone could help me  with the coding.
0
 
LVL 84
Comment Utility
What do you mean "out of date" with the InStrRev command? Do you mean you cannot use it, or do you have some other issue? The comment by lee should answer your question completely, and should give you the correct values (with the caveat, as indicated by lee, that  your data will have to be in a specific format in order for this to work reliably).
0
 
LVL 13

Expert Comment

by:lee555J5
Comment Utility
Thank you, LSM.

jegajothy, InStrRev is not new. Where InStr searches from the start of a string, InStrRev starts its search from the end of the string.

Basically, you need to build your connection and run an UPDATE SQL statement. Look here for example connection strings if connecting to another db.

About the code:
It's only a basic shell, and I may have missed a line or two. This should get you started. Add your own error handling and garbage cleanup.

Bonus Tip
Try not to use spaces in object or field names as in Seniors Club. You can, but.... Any time you access it, you have to enclose it with [] as in [Seniors Club]. It's just best to avoid. If you can, try something like tblSeniorsClub. You may not be able to change it, such as with legacy or inherited dbs where it's not worth the effort.
Dim cnn As New ADODB.Connection, cmd As New ADODB.Command
Set cnn = CurrentProject.Connection
cmd.Connection = cnn
cmd.CommandText = "UPDATE [Seniors Club] SET [FirstName] = Mid([Name], 1, InstrRev([Name], Chr(32))), _
    [LastName] = Mid([Name], InstrRev([Name], Chr(32)));"
cmd.Execute

Open in new window

0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:jegajothy
Comment Utility
in response to LSM consulting, I am refering to my knowledge of vb as out of date as it was many moons (>7 years) that I dabbled in vb.  NOT the responder. thank u.
0
 

Author Comment

by:jegajothy
Comment Utility
In response to Lee's coding response.  I copied and pasted, but it gave me a User defined type not defined error at the line :
              Dim cnn As New ADODB.Connection
My OS is windows 7 and I am using Access 2007.
Thank u.
0
 
LVL 13

Expert Comment

by:lee555J5
Comment Utility
That error sounds as if the proper reference isn't set. I thought it would've been because you asked specifically for ADO code. Anyway, open the window for the code and go to Tools -> References.... If it's not checked at the top, scroll to Microsoft ActiveX Data Objects x.x Library and check the box for the one with the highest x.x rev. For Access 2007, I think it should be 2.8.
0
 

Author Comment

by:jegajothy
Comment Utility
In response to Lee, in the first time I was able to go to the design mode of the Form, then opened the code,  and I was able to click on the Tools, and References showed up.  As suggested I checked the 2.8 but I also saw a 6.0 version too.   When I ran the code again, I got the same error.  I am lost in what is wrong. Thank u.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
Did you get the same error on the same line?

If you're doing this in Access, and if you have linked tables, then you don't really need to use ADO. Just run the SQL statement directly:

Currentdb.Execute "UPDATE [Seniors Club] SET [FirstName] = Mid([Name], 1, InstrRev([Name], Chr(32))), _
    [LastName] = Mid([Name], InstrRev([Name], Chr(32)));"
0
 

Author Comment

by:jegajothy
Comment Utility
In response to LSMConsulting, the tabes are not linked but is in the file as the mdb file.  Thank u
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now