How do I separate query data in Expression Builder?

[rbnlk]

Trying to manipulate data in a query field called NAM1 using expression builder. Don't know how to separate the data and reverse it. There is other data mixed in with this field. I only want the data in this field that is separated by a comma and a space. I would be ok to seperate the data into to separate fields.
Here's an example of the data:
PERKINS, JEFFREY D

Here's how I want it:
JEFFREY D PERKINS

Can you help?

 

[SeeJane]

Hi

The quick explanation of how to do this is to concatenate everything to the left of ", " with everything to the right of ", ". One way to do it is use the InStr function to get the character position of ", " then the left/right functions to get everything to the side.

To get all to left:
Left (YourField, Instr(1, YourField, ", &quot-1)

To get all to right:
Right (YourField, Instr(1, YourField, ", &quot+ 1)

Put it all together:
Left (YourField, Instr(1, YourField, ", &quot-1) & " " & Right (YourField, Instr(1, YourField, ", &quot+ 1)

This should be right, but if you're a char or 2 off fool around with the +/- 1's in the InStr function.

As an aside, your data would be easier to manipulate/search if it were divided up over several fields FirstName, LastName, MiddleName, MaidenName, etc. That would allow you to use multiple styles for different formats.

Good luck,
CJ

 

[rbnlk]

Seems simple enough but I am getting an error of : Invalid procedure call.

Here is the left expression I'm using:
Left([NAM1],InStr(1,[NAM1],", &quot-1)

What am I doing wrong?

 

[SeeJane]

Not sure, it looks OK to me...I'm stumped. There are a few functions (in theory, at least) acting in concert here, so you might be able to pin down the souce if you try slowly building the statement. In other wirds, try InStr(1,[NAM1],", &quot and if that works, try InStr(1,[NAM1],", &quot-1, then Left([NAM1],InStr(1,[NAM1],", &quot-1).

The only thing I can think of is that it may be choking on a record missing the &quot;, &quot; (maybe there's one with just a &quot;,&quot; missing the space or something?). You could add an iif statement to the whole thing like iif instr(1,[NAM1],&quot;, &quot<>0,InStr(1,[NAM1],&quot;, &quot-1, Left([NAM1],InStr(1,[NAM1],&quot;, &quot-1),&quot;Invalid entry&quot

Anyone else have an idea?

Good luck,
CJ

 

[rbnlk]

I am still majorly confused as to why this will not work. I have tried to break it down as you suggest but it still doesn't work. It gives me no data when I run the query and I know it's there. If I use the &quot;like&quot; command it will display the data if I use wildcards i.e. [like &quot;*, *&quot;]
I have also tried it in a similar but different data file.

Still stumped. Don't what to sound ignorant but could it be a properties problem?

 

[SeeJane]

Now I am truly stumped...sorry! Fooled around a bit with the InStr function and couldn't produce an error. You could try submitting another post with a desperate-sounding title.

Good luck,
CJ