Solved

MS Access -- Left() and InStr() Problem

Posted on 2006-07-13
6
466 Views
Last Modified: 2008-07-03
Here is my issue:

I am using the following in select statement:

Left(N.FirstName,InStr(1,N.FirstName,' '))

It works fine when N.FirstName is two words but return nothing when it is only one word.

I understand because InStr(1,N.FirstName,' ') is returning '0' then I am tryng to get everything to the left of '0'.  Is there away to put in some sort of if statement to take this into account.

0
Comment
Question by:daugh016
  • 2
  • 2
  • 2
6 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17100393
U have to make use of IIF

assuming this is done in a query

Left(N.FirstName,   IIF(InStr(1,N.FirstName,' ')>0,InStr(1,N.FirstName,' '),Len(N.FirstName))

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17100399
ok, my mistake. Out of practice I give the answer but dont always give an explanation. Sorry about this, I hadnt realise what I do was considered Post Tagging
So if u need an explanation, let me know
0
 
LVL 4

Expert Comment

by:Carl2002
ID: 17100406
It is returning nothing when N.Firstname is one word because of the " " at the end of the statement. The expression is specifically searching for the first space, if there isn't one it will fall down.

Can you explain a little more what you are trying to achieve?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:daugh016
ID: 17100439
Carl,

If someones first name is two words, I just want the first one.  

Example if someones name is Mike, I want it to show Mike.
Example if someones name is John Mark, I want it to show John.

Rockiroads,

I couldn't get it to work.  It says missing operator.

0
 
LVL 4

Accepted Solution

by:
Carl2002 earned 50 total points
ID: 17100498
Try this

Expr1: IIf(InStr([field1]," ")=0,[field1],Left([field1],InStr([field1]," ")))
0
 

Author Comment

by:daugh016
ID: 17100541
Thanks Carl.  That worked
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

713 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