473,327 Members | 2,055 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Splitting Field

I know this subject has been covered ad nauseum on these groups but I
cannot find a solution to my specific situation.

I inherited a database that has names kept in one field. I need to
break this out into 4 fields. The format of the names is pretty
consistent but the 4 possible names are not always there. The format
is "LastName, FirstName MiddleInitial Suffix" (Weird I know but true).
If all fields had all 4 possibilities then I would know what to do but
some only have Last, First some Last, First Middle, etc. That kind of
logic is beyond my VBA skills. Please help.
Nov 12 '05 #1
1 1432
What you can do:
Take up to 4 passes via SQL - each pass removing the set of char's up
to the first ',' or blank - on the first pass move the field to last
name, second pass to first, etc......if there's no ',' or blank in the
field then SQL will pass that record - in the where clause have
something like - Where Instr(field1, ',') > 0 or Instr(field1, ' ') >
0....

db**********@yahoo.com (BigD) wrote in message news:<68**************************@posting.google. com>...
I know this subject has been covered ad nauseum on these groups but I
cannot find a solution to my specific situation.

I inherited a database that has names kept in one field. I need to
break this out into 4 fields. The format of the names is pretty
consistent but the 4 possible names are not always there. The format
is "LastName, FirstName MiddleInitial Suffix" (Weird I know but true).
If all fields had all 4 possibilities then I would know what to do but
some only have Last, First some Last, First Middle, etc. That kind of
logic is beyond my VBA skills. Please help.

Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Rakesh | last post by:
Hi, I was 'googling' to look out for some ways of optimizing the code and came across this term - 'hot / cold splitting'. In short, the discussion is about splitting heavily accessed ( hot )...
1
by: Earl Anderson | last post by:
I have imported an Excel worksheet into A97/WinXPH which had the new employees names in one field ( in a Last Name,First Name configuration). I wanted to split that one field ( ) into two...
1
by: Greg Teets | last post by:
I have a field in an access 2000 database that is actually three lines of text separated by carriage returns. Is there a function in the Access version of SQL that will let me locate the...
3
by: La di da Limey | last post by:
Hi, I have a field in a database called "Timestamp" which has the date and time of an event, for example: "01/02/2002 09:07:59" The format is MM/DD/YYYY HH:MM:SS I want to chop the field...
1
by: Montana_Trader | last post by:
I have a product database that includes a memo field for product descriptions. That database must be imported into a legacy system that has four text fields for product descriptions, each with a...
13
by: Pedro Pinto | last post by:
Hi there. I'm trying to do the following. I have a string, and i want to separate it into other halves. This is how it should be: char string = "test//test2//test3"; were // is the part...
2
by: iritchie | last post by:
*(Apologies, I posted this in the SQL Server forum first) Hello all, I am trying to write a query which breaks down a single address field into individual fields, with char(10) or a...
4
by: Steven D'Aprano | last post by:
I'm trying to split a URL into components. For example: URL = 'http://steve:secret@www.domain.com.au:82/dir" + \ 'ectory/file.html;params?query#fragment' (joining the strings above with plus...
1
by: Nevgar | last post by:
I am fairly new to php / MySQL and have a problem with a query and an insert. I have a database that maybe isn't the best design - it includes a field "Former Names" which is varchar 255. It...
37
by: xyz | last post by:
I have a string 16:23:18.659343 131.188.37.230.22 131.188.37.59.1398 tcp 168 for example lets say for the above string 16:23:18.659343 -- time 131.188.37.230 -- srcaddress 22 ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.