IN

[Guest]

There is a function in SQL that allows you to compare a single string with
several strings without having to use multiple Or's eg strExample
In("aa","bb","cc") - this doesn't seem to work in vba - is there an
equivalent function that does???

 

[John Spencer]

I'm not aware of one, although it should be fairly simple to write a VBA
function that would do so.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jason Lepack]

Public Function funcIN(s As String, sIN() As String) As Boolean
' Checks to see if the input string (s) is an element "in" the array
sIN
Dim i As Integer
funcIN = False
For i = LBound(sIN) To UBound(sIN)
If UCase(s) = UCase(sIN(i)) Then
funcIN = True
Exit Function
End If
Next i
End Function

This will match regardless of case. Remove the UCase for it to be
case sensitive.

Cheers,
Jason Lepack

 

[John Spencer]

Good start and that should work well for strings.

However, this may need to handle nulls, dates, and numbers as the first
argument and possibly in the array of possible matching values.

Also, comparisons are controlled by your Option Compare statement in
modules. So whether or not your compare is case sensitive is dependent on
whether or not you have set Option Compare and how you have set it for the
module. If Option Compare is not declared then the default is Option
Compare Binary (If I recall correctly)

Anyway, as I said this is a bit of code for strings.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jason Lepack]

Good start and that should work well for strings.

However, this may need to handle nulls, dates, and numbers as the first
argument and possibly in the array of possible matching values.

Yup, just used for strings.

Also, comparisons are controlled by your Option Compare statement in
modules. So whether or not your compare is case sensitive is dependent on
whether or not you have set Option Compare and how you have set it for the
module. If Option Compare is not declared then the default is Option
Compare Binary (If I recall correctly)

Interesting... I didn't know that.

 

[raskew via AccessMonster.com]

Hi -

The InParam() function described in the MSKB
http://support.microsoft.com/kb/100131/en-us may be what you're looking for.
It allows the user to enter comma-separated parameters.

HTH - Bob

Good start and that should work well for strings.

However, this may need to handle nulls, dates, and numbers as the first
argument and possibly in the array of possible matching values.

Yup, just used for strings.

Also, comparisons are controlled by your Option Compare statement in
modules. So whether or not your compare is case sensitive is dependent on
whether or not you have set Option Compare and how you have set it for the
module. If Option Compare is not declared then the default is Option
Compare Binary (If I recall correctly)

Interesting... I didn't know that.

Anyway, as I said this is a bit of code for strings.
--

[quoted text clipped - 46 lines]

- Show quoted text -