Parsing data

[crowegreg]

I can't get this to work properly.



I have a text field named "VP Name" The first name and last name are separated by a " ".
Within a query, I need the First Name Initial and Last Name of the VP Name to be displayed.
Getting the First Name Initial is easy, left([VP Name],1)
I can't get the last name to work properly. Here's what I've started with, mid([VP Name], instr([VP Name]," "))
I receive the error message, Data type mismatch in criteria expression.

If I try using right instead of mid, I receive the same error message.

I've been albe to use len([VP Name])-instr([VP Name]," ") to calculate the number of characters, but when I combine with right([VP Name], ###), I receive the error message.

[June7]

Name parts should be stored in separate fields. Concatenation is easier than parsing.

Nothing wrong with the Mid syntax, works for me.

Are you sure every name has a space? InStr() will error if the value is not found.

You say the initial is returning correctly?

Is this query an Access object or VBA code?

[crowegreg]

Yes, each name as a space. Some names have an ', is that the problem?

Yes, initial is working correctly.

An Access object. Once created, I'll place the SQL statement in VBA code

[crowegreg]

I just realized that 1 VP name has 2 spaces, ex: DR George Smith

[Dal Jeanis]

I don't see an obvious problem.

First, try coercing the result of instr([VP Name]," ") to a number by using val().

If that doesn't work, then please post the whole query that's failing so we can see what else might be choking the thing.

[June7]

The second space is irrelevant, can have as many as you want, InStr will stop at the first one encountered. However, the initial returned will be the D and George would be last name, if can get to work.

Could try InStrRev but then what about names like Jean Claude Van Damme?

The apostrophe will be an issue with VBA constructed SQL but Access query object shouldn't care. Still works with my testing. Would have to examine your data if you want to scrub it and provide.

[Dal Jeanis]

crowegreg - Then you're stuck with various choices: 1) leave well enough alone 2) Use InstrRev and hope no one has two last names separated by a space, or 3) check the VIP names for titles, or 4) modify the input forms so that VIP names are not freeform. If you don't plan on making this query a career, you'll end up at 1 eventually. It's your call when you've reached the point of diminshing returns.

[crowegreg]

It is the name with 2 spaces. I deleted that record and everything worked fine. I'll try InstrRev

[June7]

Interesting, don't know why that would have been issue. I tested the Mid() expression with several spaces and it worked.

[Dal Jeanis]

I wonder if maybe that record had anything else odd about it, like an apostrophe or unprintable character?