First Names a little different

  • Thread starter Thread starter JB
  • Start date Start date
J

JB

I have a "First Name" field in my table. Sometimes the first name is
accompanied by the person's middle initial and sometimes it is not. When I
query to count the number of times the person's name appears in my table, I
get a count for each. How can I design my query to ignore the middle
initial? Thanks.
 
Not sure there's really any reliable way to automate it. You can't just
ignore what's after the first space, because how would you handle two part
names like Mary Jane or Billy Bob?
 
Actually, I could ignore what's after the first space because their aren't
any two-part first names in my table (it's not a real big table). Can you
tell me how to do that? Thanks.
 
You could do something like this at the top of an empty column in query
design view:

NoInitial: Left([NameField], InStr([NameField]," ") - 1)

Use whatever name you like for NoInitial, and substitute your actual field
name for NameField. Also, I suggest looking up Left and InStr to see how
this works.

Having said that, you are building a house of cards. The middle initial
should be in a separate field, as suggested. That way if there is ever
anybody with a two-part name you won't get the wrong result when you parse
the FirstName field.
 
In my area, "Jim" is a fairly common first name.

If you get a count of "Jim"s, how do you know that they all aren't different
Jims?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top