Some language have an inlist function (inlist State = 'NY', 'CT',.

[Tupacmoche]

Some language have an inlist function (inlist State = 'NY', 'CT', 'NJ') is
there one in Access 2003? Or is there something equivalent?

 

[Larry Linson]

Some language have an inlist function (inlist State = 'NY', 'CT', 'NJ') is
there one in Access 2003? Or is there something equivalent?

Perhaps it should be intuitive what an "Inlist" function does, but it isn't
intuitive to me. If you will explain that, perhaps someone could suggest how
you accomplish the same purpose in Access.

To the best of my knowledge, there is no "Inlist" function in Access.

Larry Linson
Microsoft Access MVP

 

[John Nurick]

Hi Tupacmoche,

Access 2003 isn't a language.

In VBA, there's the Instr() function, e.g.:
If Instr("NY,CT,NJ", strState) > 0 Then
In SQL, there's the IN operator, e.g.:
WHERE State IN ('NY', 'CT', 'NJ')

 

[Dirk Goldgar]

Some language have an inlist function (inlist State = 'NY', 'CT',
'NJ') is there one in Access 2003? Or is there something equivalent?

Is that function supposed to return true if State is in 'NY' or 'CT' or
'NJ'? Or is it to return the position of the matched entry?
Regardless, Access VBA doesn't have such a function, so you can't use it
in VBA code. Access SQL does have an In operator that can be used in
queries, if all you want to know is whether the item is in the list:

SELECT * FROM Addresses
WHERE State In ('NY', 'CT', 'NJ');

To get a similar effect in VBA code, I've been known to use the InStr
function:

If InStr(State, "NY,CT,NJ") > 0 Then
' it was in the list
End If

I seem to recall that you can also use the Eval function to get the In
operator to work:

If Eval("'" & State & "' In ('NY', 'CT', 'NJ')") Then
' it was in the list
End If

On the other hand, if you want the function to return the position of
the item in the list, there is no built-in function for that. Writing
one would be trivial, though.

 

[Dirk Goldgar]

If InStr(State, "NY,CT,NJ") > 0 Then

I appear to have written this backward -- should have been

If InStr("NY,CT,NJ", State) > 0 Then

Sorry.

 

[Douglas J. Steele]

I appear to have written this backward -- should have been

If InStr("NY,CT,NJ", State) > 0 Then

Sorry.

Just to be different, I typically include commas on either side, just to be
sure that it fails for, say, single characters ("N", "C"):

If InStr(",NY,CT,NJ,", ", " & State & ",") > 0 Then

(although I'll admit I've been known to go overboard...)

 

[Dirk Goldgar]

Just to be different, I typically include commas on either side, just
to be sure that it fails for, say, single characters ("N", "C"):

If InStr(",NY,CT,NJ,", ", " & State & ",") > 0 Then

(although I'll admit I've been known to go overboard...)

No, you're quite right -- I've done it that way, too, but last night I
was obviously dopey and forgot that point along with getting the
arguments backward..