Hi,
I'm banging my head against the desk because I can't find a solution for the following simple problem. Case: There is a column in a table that has FamilyName and FirstName(s) in one field. ie "McCarthy,John Doe Willy". I have to split up Familyname and the 1st FirstName. The FamilyName works perfect, just looking up the comma with InStr and use Left().
Now can someone throw me a bone to help me get the 1st Firstname. "John" according to the example.
This is what I got but I think InStr function doesn't work properly. I thought the 3 parameters were InStr(placetostartlooking, [field],'SearchString') - SELECT
-
Naam_en_voornamen,
-
Left([naam_en_voornamen],InStr(1,[naam_en_voornamen],',')-1) AS Naam,
-
InStr([naam_en_voornamen],',')+1 AS Start,
-
InStr(9,[naam_en_voornamen],'a') AS stop,
-
Mid([naam_en_voornamen],[start],[stop]) AS Voornaam
-
FROM tbl_kankerpreventie;
-
4 3898
Try this: -
-
SELECT Naam_en_voornamen,
-
Left([naam_en_voornamen],InStr(1,[naam_en_voornamen],',')-1) AS Naam,
-
Right([naam_en_voornamen],(Len([naam_en_voornamen])-InStr(1,[naam_en_voornamen],',')+1)) AS Voornaam
-
FROM tbl_kankerpreventie;
-
-
Thanks for the response but that's the same result I get.
This is what I want to achieve: -
"Naam_en_voornamen" | "Naam" | "Voornaam"
-
----------------------------------------------------
-
McCarthy,John Doe Willy | McCarthy | John
-
So it has to drop the "Doe Willy". Locating the first "space" between John Doe is not the problem it's getting out the John ;-)
PEB 1,418
Expert 1GB
Hi,
try; -
SELECT
-
Naam_en_voornamen,
-
Left([naam_en_voornamen],InStr(1,[naam_en_voornamen],',')-1) AS Naam,
-
Mid([naam_en_voornamen],InStr([naam_en_voornamen],',')+1,InStr(1,[naam_en_voornamen]," ")-InStr([naam_en_voornamen],',')-1) AS Voornaam
-
FROM tbl_kankerpreventie;
-
-
:)
Works like a charm, I owe you :)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Don Miller |
last post by:
I can't seem to find at MS how to detect the 2002 OS vs. 2003 OS for web
applications using ASP. This is the only page I could find...
|
by: Paul |
last post by:
Hi all, at present I I've built a website which can be updated by admin and
users.
My problem, I've combined "log in" and "access levels" to restrict access to
certain pages, using the built...
|
by: Daniel |
last post by:
I use an Access database to basically take data exports, import them,
manipulate the data, and then turn them into exportable reports. I do
this using numerous macros, and queries to get the data...
|
by: Wayne Aprato |
last post by:
I have a client who is running several Access 97 databases that I have
written for them. They are about to upgrade to Access 2003. Is the
default file format of Access 2003 still Access 2000 the...
|
by: Peter Frost |
last post by:
Please help
I don't know if this is possible but what I would really like to do is
to use On Error Goto to capture the code that is being executed when
an error occurs.
Any help would be much...
|
by: dog |
last post by:
I've seen plenty of articles on this topic but none of them have been
able to solve my problem.
I am working with an Access 97 database on an NT4.0 machine, which has
many Access reports.
I...
|
by: Grasshopper |
last post by:
Hi,
I am automating Access reports to PDF using PDF Writer 6.0. I've
created a DTS package to run the reports and schedule a job to run this
DTS package. If I PC Anywhere into the server on...
|
by: Sebastian |
last post by:
Hello
I develop my applications in Access 2002. My development system is
running Windows XP SP2 and I have Microsoft Office XP Developer.
Microsoft Office XP is at SP3.
I used Inno Setup (great...
|
by: NeoPa |
last post by:
Access QueryDefs Mis-save Subquery SQL
Access stores its SQL for Subqueries in a strange manner :s It seems to replace the parentheses "()"with square brackets "" and (often) add an extraneous...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |