help matching a field in a query

[erick-flores]

Hello all

I have a table that has the complete name for each user.

I would like to run a query to match the FirstName LastName with the
CurrentUser() and return a user_code.

This is the query that im using now:
SELECT users.user_code, users.name
FROM users
GROUP BY users.user, users.name
HAVING users.name Like (CurrentUser());

Everything works fine when the Logon Name is the exact as the name
from my table BUT the problem comes when the user forgot to type caps
to its Logon Name, when this happends then my query will return the
user_code. Also I want to be able to match any word from the
CurrentUser() to my users.name field. For example:
Logon Name: BJ Moore
Users.name: B J Moore Jr.

I want my query to be able to return the user_code for the example
above.

Thanks in advance

 

[J_Goddard via AccessMonster.com]

What you are trying to do is difficult, if not impossible, in a query. I
suggest you add another field to your users table for the Logon_Name.
Assuming that you only expect the query to return one record, whay do you use
Group By and Having?

SELECT users.user_code, users.name
FROM users
WHERE users.Logon_Name = CurrentUser();

Where are you using this? In code? If so, you will have to modify the
statement

 

[erick-flores]

What you are trying to do is difficult, if not impossible, in a query.  I
suggest you add another field to your users table for the Logon_Name.
Assuming that you only expect the query to return one record, whay do you use
Group By and Having?

Sorry for including the group by and having.

As for your suggestion I can not add a field to the users table. I
knew the query sound a lil complicated but I was just wondering if
there is a way to do it...thanks anyways.

SELECT users.user_code, users.name
FROM users
WHERE users.Logon_Name = CurrentUser();

Where are you using this? In code? If so, you will have to modify the
statement

The code is not very important at this moment, first I need to match
my data and then return the code.

 

[John W. Vinson]

Hello all

I have a table that has the complete name for each user.

I would like to run a query to match the FirstName LastName with the
CurrentUser() and return a user_code.

This is the query that im using now:
SELECT users.user_code, users.name
FROM users
GROUP BY users.user, users.name
HAVING users.name Like (CurrentUser());

Everything works fine when the Logon Name is the exact as the name
from my table BUT the problem comes when the user forgot to type caps
to its Logon Name, when this happends then my query will return the
user_code. Also I want to be able to match any word from the
CurrentUser() to my users.name field. For example:
Logon Name: BJ Moore
Users.name: B J Moore Jr.

I want my query to be able to return the user_code for the example
above.

Thanks in advance

SELECT users.user_code, users.[name]
FROM users
WHERE users.[name] LIKE "*" & CurrentUser & "*";

will find all cases where the currentuser string is a proper substring of the
user name. It's not in this case! You'll also have ambiguities and false
drops. You could get a bit better match by trimming out blanks and punctuation
in the name field:

WHERE Replace(Replace(Replace([name], " ", ""), "-", ""), ".", "") LIKE "*" &
CurrentUser() & "*")

A couple of suggestions: Name is a reserved word, and a very bad choice of
fieldname; and people's names should be stored in fields like FirstName,
MiddleName, LastName rather than in one combined field.

John W. Vinson [MVP]

 

[erick-flores]

Thanks for ur suggetions.

For the name field, I did not create the tables in the database
myself. I know is better to have multiple field for a name, but I have
to work with what I have now...thanks anyways