Logical statement: equivalent of an IN function?
 

Hello everyone!

Please, would you know if there is an IN function under Excel (or access) so
that I can compare values without repeating the same equation?

Unless it exists, I am compelled to write expressions like:

If strTemp like * or strTemp like * or strTemp like * or... Then

isnt'it ?

Thanks ;)

Logical statement: equivalent of an IN function?
 

You don't provide too much help in your example but have a look at InStr in
VB help

Mike

"The Dude" wrote:

Hello everyone!

Please, would you know if there is an IN function under Excel (or access) so
that I can compare values without repeating the same equation?

Unless it exists, I am compelled to write expressions like:

If strTemp like * or strTemp like * or strTemp like * or... Then

isnt'it ?

Thanks ;)

Logical statement: equivalent of an IN function?
 

The InStr function that Mike suggested might be one way. Another might be to
put your values in a String array and use the Filter function. Still another
way might be to use the Like operator. If you can give us several concrete
examples of what you are trying to do, I'm sure we can flesh out our
suggestions to you with code samples.

--
Rick (MVP - Excel)


"The Dude" <f_com2°AT°yahoo°DOT°fr wrote in message
...
Hello everyone!

Please, would you know if there is an IN function under Excel (or access)
so
that I can compare values without repeating the same equation?

Unless it exists, I am compelled to write expressions like:

If strTemp like * or strTemp like * or strTemp like * or... Then

isnt'it ?

Thanks ;)

Logical statement: equivalent of an IN function?
 

ok sorry guys, I'll try to be more precise.
I will take an example, but I had several other cases needing the same logic.

I have, for instance, a little function that gives me the Windows login of
the current user. Let's say johndoe.
Now let's suppose that your program needs to allow a few users to use Admin
functions and you want to test if the current username, strUserName, is part
of the predefined admins.

My logic was to use something like:
if strUserName like "johndoe" or strUserName like "nedflanders" then
btnAdminFunctions.visible = true

Now what happens if I have about 12 admins? Will I have to repeat 12 times
the 'strUsername like [adminusername]' expression?

Thank you for your time and patience ;)
T_D

Logical statement: equivalent of an IN function?
 

Probably the simplest way is to use the InStr function. In whatever code
procedure you are doing your test in, add a constant statement declaring the
allowed people's names...

Const ApprovedAdministrators As String = "*johndoe*nedflanders*nextperson*"

Notice the list of names are delimited by asterisks (any symbol that will
never appear in the name itself will do, but if you change the asterisk to
something else, you must change them in the statements below too). Also
notice the leading and trailing asterisk... they are important and must
**always** be present. Also, do **not** try and "neaten things up" by adding
spaces around the asterisks as that will insure the name matches will never
be found. Okay, with the list of approved administrators defined, you would
use this to test if the name you have assigned to strUserName is in the
list...

If InStr(ApprovedAdministrators, "*" & strUser & "*") 0 Then
btnAdminFunctions.Visible = True
End If

The InStr function is similar in function to Excel's FIND and SEARCH
functions... it looks for the location of a substring inside a larger string
and returns the character position it is located at. Unlike Excel's
functions, it returns zero if no match is found instead of erroring out.
Here, we don't really care what character position actually is, only that it
is not zero. You are probably wondering what the asterisks are for... they
prevent a false match. Let's say you had "johndoe" assigned to strUserName,
then as set up the InStr function will search for "*johndoe*" inside
ApprovedAdmininstrators name list and would a match the first character
position. Now, what if "johnd" is assigned to strUserName... the search
would be for "*johnd*" and that string would not be found in the
ApprovedAdmininstrators name list... without the asterisks, "johnd" would be
found at the beginning of "johndoe" and falsely reported as being in the
list. The asterisks guarantee that a false match will never be found (which
is why the asterisks, or whatever delimiter you ultimately choose to use,
can **never** be allowed as part of an username).

--
Rick (MVP - Excel)


"The Dude" <f_com2°AT°yahoo°DOT°fr wrote in message
...
ok sorry guys, I'll try to be more precise.
I will take an example, but I had several other cases needing the same
logic.

I have, for instance, a little function that gives me the Windows login of
the current user. Let's say johndoe.
Now let's suppose that your program needs to allow a few users to use
Admin
functions and you want to test if the current username, strUserName, is
part
of the predefined admins.

My logic was to use something like:
if strUserName like "johndoe" or strUserName like "nedflanders" then
btnAdminFunctions.visible = true

Now what happens if I have about 12 admins? Will I have to repeat 12 times
the 'strUsername like [adminusername]' expression?

Thank you for your time and patience ;)
T_D

Logical statement: equivalent of an IN function?
 

Thanks Rick,
that's exactly what I needed.

I have replaced the * by + but I like the logic behind it and will use it
for other cases...

Thanks
T_D

"Rick Rothstein" wrote:

Probably the simplest way is to use the InStr function. In whatever code
procedure you are doing your test in, add a constant statement declaring the
allowed people's names...

Const ApprovedAdministrators As String = "*johndoe*nedflanders*nextperson*"

Notice the list of names are delimited by asterisks (any symbol that will
never appear in the name itself will do, but if you change the asterisk to
something else, you must change them in the statements below too). Also
notice the leading and trailing asterisk... they are important and must
**always** be present. Also, do **not** try and "neaten things up" by adding
spaces around the asterisks as that will insure the name matches will never
be found. Okay, with the list of approved administrators defined, you would
use this to test if the name you have assigned to strUserName is in the
list...

If InStr(ApprovedAdministrators, "*" & strUser & "*") 0 Then
btnAdminFunctions.Visible = True
End If

The InStr function is similar in function to Excel's FIND and SEARCH
functions... it looks for the location of a substring inside a larger string
and returns the character position it is located at. Unlike Excel's
functions, it returns zero if no match is found instead of erroring out.
Here, we don't really care what character position actually is, only that it
is not zero. You are probably wondering what the asterisks are for... they
prevent a false match. Let's say you had "johndoe" assigned to strUserName,
then as set up the InStr function will search for "*johndoe*" inside
ApprovedAdmininstrators name list and would a match the first character
position. Now, what if "johnd" is assigned to strUserName... the search
would be for "*johnd*" and that string would not be found in the
ApprovedAdmininstrators name list... without the asterisks, "johnd" would be
found at the beginning of "johndoe" and falsely reported as being in the
list. The asterisks guarantee that a false match will never be found (which
is why the asterisks, or whatever delimiter you ultimately choose to use,
can **never** be allowed as part of an username).

--
Rick (MVP - Excel)


"The Dude" <f_com2°AT°yahoo°DOT°fr wrote in message
...
ok sorry guys, I'll try to be more precise.
I will take an example, but I had several other cases needing the same
logic.

I have, for instance, a little function that gives me the Windows login of
the current user. Let's say johndoe.
Now let's suppose that your program needs to allow a few users to use
Admin
functions and you want to test if the current username, strUserName, is
part
of the predefined admins.

My logic was to use something like:
if strUserName like "johndoe" or strUserName like "nedflanders" then
btnAdminFunctions.visible = true

Now what happens if I have about 12 admins? Will I have to repeat 12 times
the 'strUsername like [adminusername]' expression?

Thank you for your time and patience ;)
T_D