Query to check For middle Initial on First Name Field 1

[egstatus]

Hi all
I am trying to write a query to check for Middle initials in the first_name field. [MS Access 2003]

For Example If I have "David A", "John D", "Eddie" as the records
I want to get "David A", and "John D" because they have midle initial.


Here is what I have:

SELECT [loeh-new].FIRST, [loeh-new].LAST
FROM [loeh-new]
WHERE (((Mid(Trim([First]),Len(Trim([First]))-1,1))=""))
ORDER BY [loeh-new].FIRST;

When I run this query I get "Invalid Procedure Call"
What Am I doing wrong here?

Thanks

Ed

 

[Crowley16]

eh, your where statement doesn't make sense for what you are trying to do...

how I'd do this is to test if there's a space character in the first name field...

where Instr(Trim(first), " ") < 1

--------------------
Procrastinate Now!

 

[danvlas]

SELECT [loeh-new].[FIRST], [loeh-new].[LAST]
FROM [loeh-new]
WHERE InStr([loeh-new].[FIRST]," ")> 0
ORDER BY [loeh-new].[FIRST];

Additional suggestion: First and Last are Access built-in functions, so it's highly recommended that you avoid using them as field names.

Instead, use a naming convention (such as loeFirst, loeLast) to eliminate the possibility of conflicts.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[egstatus]

I only want this query to return record that have a
[FirstName space MiddleInit] Ex "David J"

I don't want record like "JI YOUNG" or "KATHERINE ANN" because is a full middle name. I am only interested in Middle Initials.

The following[ WHERE InStr([loeh-new].[FIRST]," ")> 0 ]returns "JI YOUNG" or "KATHERINE ANN"

Thanks again

 

[Remou]

Just to confuse, I tried your query and it worked for me. I wonder if you have lost a reference?

 

[egstatus]

How can I tell if I lost a reference?

It would have to be either the Len or Mid functions that are causing it.

Thanks again.

 

[Golom]

Just to expand on danvlas' example

SELECT [FIRST], [LAST]

FROM [loeh-new] 

WHERE InStr([FIRST]," ") > 0 
  AND Len([FIRST]) = InStr([FIRST]," ") + 1

ORDER BY [FIRST];

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[egstatus]

Thanks Golom and the rest that helped me on this.

The last post did the work.

Ed

 

[PHV]

Another way:
WHERE [FIRST] Like '* ?'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886