First and Last Name

[mjbryant]

I'm sure this is a simple expression, but how do you separate the first and last name that's together in one field or column into two separate columns? Also, and more importantly, how would I perform a match query - what's the expression to match two like fields BUT ONLY THE FIRST WORD IN THE FIELD; I'm working with company names and I don't want to try and match the entire company name. For example, "ABC Inc." I only want Access to try and match the ABC portion and not the Inc.

 

[Ian Mac]

On 2003-02-18 11:47, mjbryant wrote:
I'm sure this is a simple expression, but how do you separate the first and last name that's together in one field or column into two separate columns? Also, and more importantly, how would I perform a match query - what's the expression to match two like fields BUT ONLY THE FIRST WORD IN THE FIELD; I'm working with company names and I don't want to try and match the entire company name. For example, "ABC Inc." I only want Access to try and match the ABC portion and not the Inc.

If you mean by match, you mean a query, you can use fuzzy searches e.g.

Critera = ABC*

Which will return anything with ABC at the begining.

or *ABC* which means it has ABC (in order) anywhere in the name i.e. the ABC co

or *ABC which is that it has it at the end of a name i.e. 'the ABC'
would be picked up BUT NOT 'the ABC inc'

Any help??

 

[mjbryant]

I didn't explain my question correctly. What I want to do is match the first word of a field the first word in another field. For example, "generic incorporated" would be a match to "generic company" because Access looked to only match the first word.

Or, I want to match all characters before a space. I want the expression to say "match all characters that are before a space in this database/column to all characters that are before a space in this database/column"

I think the expression would go something like: Left([*],instr(1,[*])-1

Thanks for your help!

 

[parry]

Hi, In the field row of your query type something like this

FirstName: Left([Table1]![Name],InStr(1,[Table1]![Name]," ",1))

Where...

FirstName: = variable field name given for the query (you can put anything you like but must have colon after the name)

[Table1]![Name] = name of your field in table

hth

 

[mjbryant]

Thank you so much for your help!